Postgres source defaults to incremental sync even when full refresh is requested in PyAirbyte

[chougule-anushka]

Issue Description

Task:
I was working on setting up a PyAirbyte pipeline to sync the table from Postgres → MSSQL. The steps included:

• Creating a source (Postgres)
• Testing the source connection
• Creating a destination (MSSQL)
• Running a sync with full_refresh + overwrite

What I Expected:
The connector should respect the full refresh mode I defined (via force_full_refresh=True and overwrite at the destination) and successfully sync the full table to MSSQL without requiring a cursor column.

What Happened Instead:
Even with force_full_refresh=True, the Postgres connector defaulted to incremental sync. Since no cursor field exists in my table, the sync failed with:

No cursor field specified for stream attempting to do incremental

Attempts Made:

• Tried force_full_refresh=True in .read()
• Used overwrite=True in .write()
• Tried defining a user-defined cursor
• Tried _discover() and dynamic catalog edits

Despite these, the connector still reverted to incremental mode and failed.

How can we ensure that PyAirbyte respects full_refresh without needing to define a cursor?
Is this a limitation in PyAirbyte or the connector itself?

Traceback (most recent call last):
File "/home/azureuser/Orchestration-interns-al/pyairbyte.py", line 75, in
pg_source.read(
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/sources/base.py", line 869, in read
result = self._read_to_cache(
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/sources/base.py", line 953, in _read_to_cache
cache._write_airbyte_message_stream( # noqa: SLF001 # Non-public API
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/caches/base.py", line 411, in _write_airbyte_message_stream
cache_processor.process_airbyte_messages(
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/shared/sql_processor.py", line 273, in process_airbyte_messages
for message in messages:
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/sources/base.py", line 775, in _read_with_catalog
for message in progress_tracker.tally_records_read(message_generator):
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/progress.py", line 263, in tally_records_read
for count, message in enumerate(messages, start=1):
File "/home/azureuser/Orchestration-interns-al/pyairbyte-310/lib/python3.10/site-packages/airbyte/_connector_base.py", line 487, in _execute
raise exc.AirbyteConnectorFailedError(
airbyte.exceptions.AirbyteConnectorFailedError: Connector failed. (AirbyteConnectorFailedError)

AirbyteConnectorFailedError: Connector failed.
Please review the log file for more information.
Connector Name: 'source-postgres'
Exit Code: 1