Occasionally getting a "psycopg2.OperationalError: SSL connection has been closed unexpectedly" error, can the tap-postgres code be adjusted to handle this gracefully?

[jcbmllgn]

tl;dr, does it make sense to add pool_pre_ping as a configurable option to this tap to handle connections unexpectedly closing?

I have the following tap configured:

  - name: tap-postgres-arc-full-refresh
    inherit_from: tap-postgres
    variant: meltanolabs
    pip_url: git+https://github.com/MeltanoLabs/tap-postgres.git
    config:
      host: ${ARC_DB_HOST}
      user: ${ARC_DB_USER}
      port: ${ARC_DB_PORT}
      password: ${ARC_DB_PASSWORD}
      database: ${ARC_DB_NAME}
      default_replication_method: FULL_TABLE
      filter_schemas:
      - public
      ssl_enable: true
      ssl_mode: require
      ssl_certificate_authority: .secrets/root.crl
      dates_as_string: true

    select:
    - public-versions.*
    - public-message_channels_respond_to_chat_data.*
    - public-charts_phone_communications.*
    - public-clinics_groups_users.*
    - public-users.*

    - public-channel_participants.*
    - public-ecm_profiles.*
    - public-message_channels.*

And am occasionally getting this error:

2024-02-01T14:02:34.054285Z [info     ] 2024-02-01 14:02:34,053 | INFO     | singer_sdk.metrics   | METRIC: {"type": "counter", "metric": "record_count", "value": 214, "tags": {"stream": "public-ecm_profiles", "context": {}}} cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh
2024-02-01T14:02:34.058629Z [info     ] 2024-02-01 14:02:34,053 | ERROR    | tap-postgres.public-ecm_profiles | An unhandled error occurred while syncing 'public-ecm_profiles' cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh
2024-02-01T14:02:34.058891Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh
2024-02-01T14:02:34.059164Z [info     ]   File "/home/runner/work/analytics-etl/analytics-etl/.***/extractors/tap-postgres/venv/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line [1066](https://github.com/pairteam/analytics-etl/actions/runs/7738064996/job/21110836898#step:8:1067), in _buffer_rows cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh
2024-02-01T14:02:34.059443Z [info     ]     new_rows = dbapi_cursor.fetchmany(size) cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh
2024-02-01T14:02:34.059651Z [info     ] psycopg2.OperationalError: SSL connection has been closed unexpectedly cmd_type=elb consumer=False name=tap-postgres-arc-full-refresh producer=True stdio=stderr string_id=tap-postgres-arc-full-refresh

I'm typically able to rerun the sync and it will succeed. However, ideally the tap would handle this in some way, if possible. From googling the error I've found this stackoverflow conversation which advises setting pool_pre_ping=True. This pool_pre_ping configuration is not configurable though. Does it make sense to add this as a configuration options to the tap?

Thank you!

[jcbmllgn]

I'm going to close this issue since this is already captured in this prior issue: meltano/sdk#2257 cc @edgarrmondragon

[visch]

Thanks @jcbmllgn , I'm guessing the SDK issue does fix your problem?

[jcbmllgn]

Yeah, the SDK issue should fix the problem. Thanks @visch 🙏