hello wave I m getting occasional issues with the Snowflake Meltano #troubleshooting

hello :wave: I'm getting occasional issues with th...

jacob_mulligan

12/01/2023, 3:24 PM

hello 👋 I'm getting occasional issues with the Snowflake loader failing with these error messages

Copy code

psycopg2.OperationalError: terminating connection due to conflict with recovery 
DETAIL:  User query might have needed to see row versions that must be removed. 
HINT:  In a moment you should be able to reconnect to the database and repeat your command. 
SSL connection has been closed unexpectedly
psycopg2.InterfaceError: connection already closed

The Snowflake warehouse is an XS, set to auto resume, auto suspect after 5 minuets, and multi-cluster up to 4 clusters. My first thought when reading the error message was that the warehouse was suspending too quickly, but 5min seems like it should be enough time and it's set to auto-resume. Any idea why I'm getting this error message?

jacob_mulligan

12/01/2023, 3:25 PM

Here's the full error message:

Copy code

2023-12-01T14:43:56.430513Z [info     ] Traceback (most recent call last):
2023-12-01T14:43:56.431703Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/sync_strategies/full_table.py", line 144, in sync_table
2023-12-01T14:43:56.433372Z [info     ]     for rec in cur:
2023-12-01T14:43:56.434417Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/psycopg2/extras.py", line 120, in __iter__
2023-12-01T14:43:56.435747Z [info     ]     yield next(res)
2023-12-01T14:43:56.436828Z [info     ] psycopg2.OperationalError: terminating connection due to conflict with recovery
2023-12-01T14:43:56.437134Z [info     ] DETAIL:  User query might have needed to see row versions that must be removed.
2023-12-01T14:43:56.437324Z [info     ] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2023-12-01T14:43:56.437512Z [info     ] SSL connection has been closed unexpectedly
2023-12-01T14:43:56.440336Z [info     ]
2023-12-01T14:43:56.440721Z [info     ]
2023-12-01T14:43:56.440953Z [info     ] During handling of the above exception, another exception occurred:
2023-12-01T14:43:56.441142Z [info     ]
2023-12-01T14:43:56.443521Z [info     ] Traceback (most recent call last):
2023-12-01T14:43:56.443747Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/bin/tap-postgres", line 8, in <module>
2023-12-01T14:43:56.443951Z [info     ]     sys.exit(main())
2023-12-01T14:43:56.444117Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 448, in main
2023-12-01T14:43:56.444280Z [info     ]     raise exc
2023-12-01T14:43:56.444436Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 445, in main
2023-12-01T14:43:56.444581Z [info     ]     main_impl()
2023-12-01T14:43:56.444701Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 434, in main_impl
2023-12-01T14:43:56.444821Z [info     ]     do_sync(conn_config, args.catalog.to_dict() if args.catalog else args.properties,
2023-12-01T14:43:56.444942Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 312, in do_sync
2023-12-01T14:43:56.445099Z [info     ]     state = sync_traditional_stream(conn_config,
2023-12-01T14:43:56.445264Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 172, in sync_traditional_stream
2023-12-01T14:43:56.445442Z [info     ]     state = do_sync_full_table(conn_config, stream, state, desired_columns, md_map)
2023-12-01T14:43:56.445612Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/__init__.py", line 63, in do_sync_full_table
2023-12-01T14:43:56.445791Z [info     ]     state = full_table.sync_table(conn_config, stream, state, desired_columns, md_map)
2023-12-01T14:43:56.445961Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/tap_postgres/sync_strategies/full_table.py", line 107, in sync_table
2023-12-01T14:43:56.446131Z [info     ]     with post_db.open_connection(conn_info) as conn:
2023-12-01T14:43:56.446309Z [info     ] psycopg2.InterfaceError: connection already closed

mark_estey

12/01/2023, 4:20 PM

It looks like to me the error is coming from the tap-postgres side. You might want to check the Postgres DB and see if it has errors or if there's some sort of transactional deadlock?

mark_estey

12/01/2023, 4:26 PM

https://dba.stackexchange.com/questions/280716/error-canceling-statement-due-to-conflict-with-recovery might apply if you're using replication between Postgres DBs

jacob_mulligan

12/01/2023, 4:27 PM

i am replicating from a postgres DB 👀

jacob_mulligan

12/01/2023, 4:27 PM

i assumed this was an issue on the snowflake loader side. Taking a look at the link you shared. Thanks @mark_estey

jacob_mulligan

12/01/2023, 4:29 PM

great, I think that's the issue. Will wait until Monday to make changes on the postgres side to avoid pre-weekend snafus though 🙂 appreciate the help Mark!

Open in Slack

Previous Next