bugfix to ensure resync cannot fail due to table size too large

[jgao54]

Scenario this change fixes:

1. User resynced table - a typical resync writes to a _resync table and at the end exchange it with the original table, and drop the old table.
2. Before the resync fully complete, user triggered resync again, this can leave the previous *_resync table with 1TB of data
3. during the second resync, the CREATE OR REPLACE table during setup fails. This under the hood does the following:
   a. create a new table with name tmp_replace*
   b. exchange new table with old table
   c. drop old table
4. if the old table exceeds max_table_size_to_drop, 3c fails and the tmp_replace* table is orphaned.
5. on retry, the swapped *_resync table is empty and CREATE OR REPLACE succeeds

The fix ensures drop would not fail because table is too large. Arguably cleaner if we make this a client-level setting, but safer to assess it on the per-query level since we are authorizing dropping 1TB+ table.

Fixes: DBI-698

[codecov]

❌ 2 Tests Failed:

Tests completed	Failed	Passed	Skipped
2219	2	2217	202

View the top 2 failed test(s) by shortest run time

github.com/PeerDB-io/peerdb/flow/e2e::TestApiPg

Stack Traces | 0s run time

=== RUN   TestApiPg
=== PAUSE TestApiPg
=== CONT  TestApiPg
--- FAIL: TestApiPg (0.00s)

github.com/PeerDB-io/peerdb/flow/e2e::TestApiPg/TestCancelTableAddition_NoRemovalAssumed

Stack Traces | 23.3s run time

=== RUN   TestApiPg/TestCancelTableAddition_NoRemovalAssumed
=== PAUSE TestApiPg/TestCancelTableAddition_NoRemovalAssumed
=== CONT  TestApiPg/TestCancelTableAddition_NoRemovalAssumed
    cancel_table_addition_test.go:361: WaitFor wait for initial load to finish 2026-04-20 19:11:14.778914265 +0000 UTC m=+82.765088602
    cancel_table_addition_test.go:365: WaitFor t1 initial load 2026-04-20 19:11:14.779447998 +0000 UTC m=+82.765622335
    cancel_table_addition_test.go:366: WaitFor t2 initial load 2026-04-20 19:11:14.793723608 +0000 UTC m=+82.779897945
2026/04/20 19:11:14 INFO Executing and processing query x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id"
2026/04/20 19:11:14 INFO Executing and processing query stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id"
2026/04/20 19:11:14 INFO [pg_query_executor] declared cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursorQuery="DECLARE peerdb_cursor_15779739722389611366 CURSOR FOR SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id" args=[]
2026/04/20 19:11:14 INFO [pg_query_executor] fetching rows start x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id" channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] declared cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursorQuery="DECLARE peerdb_cursor_15328690676555848357 CURSOR FOR SELECT id,val FROM e2e_test_api_aql7vkum.\"t2\" ORDER BY id" args=[]
2026/04/20 19:11:14 INFO [pg_query_executor] fetching rows start x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_aql7vkum.\"t2\" ORDER BY id" channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetching from cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15779739722389611366
2026/04/20 19:11:14 INFO [pg_query_executor] fetching from cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15328690676555848357
2026/04/20 19:11:14 INFO processed row stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15779739722389611366 records=1 bytes=9 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetched rows x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id" rows=1 bytes=9 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetching from cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15779739722389611366
2026/04/20 19:11:14 INFO processed row stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15328690676555848357 records=1 bytes=9 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetched rows x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_aql7vkum.\"t2\" ORDER BY id" rows=1 bytes=9 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetching from cursor x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15328690676555848357
2026/04/20 19:11:14 INFO processed row stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15779739722389611366 records=0 bytes=0 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetched rows x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id" rows=0 bytes=0 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] committing transaction x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart
2026/04/20 19:11:14 INFO processed row stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart cursor=peerdb_cursor_15328690676555848357 records=0 bytes=0 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] fetched rows x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_aql7vkum.\"t2\" ORDER BY id" rows=0 bytes=0 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] committing transaction x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart
2026/04/20 19:11:14 INFO [pg_query_executor] committed transaction for query x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t2\" ORDER BY id" rows=1 bytes=9 channelLen=0
2026/04/20 19:11:14 INFO [pg_query_executor] committed transaction for query x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_aql7vkum.\"t2\" ORDER BY id" rows=1 bytes=9 channelLen=0
    cancel_table_addition_test.go:367: WaitFor t3 initial load 2026-04-20 19:11:14.798817604 +0000 UTC m=+82.784991941
2026/04/20 19:11:14 INFO Executing and processing query x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t3\" ORDER BY id"
2026/04/20 19:11:14 INFO Executing and processing query stream x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN} partitionId=testpart query="SELECT id,val FROM e2e_test_api_llwrkc17.\"t3\" ORDER BY id"
    cancel_table_addition_test.go:375: WaitFor wait for pause for add table 2026-04-20 19:11:14.80737758 +0000 UTC m=+82.793551917
    cancel_table_addition_test.go:376: UNEXPECTED ERROR unable to establish connection with catalog: FATAL: terminating connection due to administrator command (SQLSTATE 57P01)
    api_test.go:48: begin tearing down postgres schema api_llwrkc17
--- FAIL: TestApiPg/TestCancelTableAddition_NoRemovalAssumed (23.27s)

To view more test analytics, go to the Test Analytics Dashboard
_{📋 Got 3 mins? Take this short survey to help us improve Test Analytics.}

[github-actions]

🔄 Flaky Test Detected

Analysis: The e2e test TestApiPg/TestTableAdditionWithoutInitialLoad failed due to a transient PostgreSQL admin_shutdown error (SQLSTATE 57P01), where the catalog DB connection was externally terminated mid-test — a clear infrastructure/environment flake unrelated to code changes.
Confidence: 0.95

✅ Automatically retrying the workflow

View workflow run

[github-actions]

🔄 Flaky Test Detected

Analysis: The test TestApiMongo/TestCancelTableAdditionDuringSetupFlow failed due to a PostgreSQL SQLSTATE 57P01 (admin_shutdown) error where a concurrent test terminated the catalog connection while 32 tests ran in parallel, a classic cross-test interference race condition.
Confidence: 0.92

✅ Automatically retrying the workflow

View workflow run

[pfcoperez]

LGTM. Would it make sense to put together a small test case where the ClickHouse target is set to use a ridiculously small max_table_size_to_drop value by default and where we exercise this change?

[pfcoperez]

I was researching on this option and found that overriding max_table_size_to_drop setting at query time is available only after ClickHouse v23.12 --> https://clickhouse.com/docs/whats-new/changelog/2023#2312

I understand that someone using such an ancient version is a low-probability corner case. Anyway, even if that happens, the setting will be just ignored and they would not get any worse than the current situation, right?

[pfcoperez]

Actually, I was wrong in my assumption, I did run a little experiment:

SELECT COUNT(*) FROM table SETTINGS i_dont_exist=0;

Setting i_dont_exist is neither a builtin setting nor started with the prefix 'custom_' registered for user-defined settings.

So I think this would indeed fail on older versions. Maybe we can add a version check to the condition?

[ilidemi]

We have a whole framework for version-aware CH settings under https://github.com/PeerDB-io/peerdb/blob/main/flow/internal/clickhouse/settings.go. I think this logic should move to generateCreateTableSQLForNormalizedTable and be appended to SQL like the other cases we use settings

[jgao54]

@pfcoperez good call on checking ch version before adding it to the context.

@ilidemi i was worried that SETTINGS max_table_size_to_drop = 0 get applied to the CREATE TABLE as a table-level storage setting, but looks like clickhouse handles this automagically here

[github-actions]

🔄 Flaky Test Detected

Analysis: The failures are caused by a PostgreSQL catalog connection being externally terminated (SQLSTATE 57P01 admin_shutdown) during the test, which is a transient CI infrastructure issue, not a code regression.
Confidence: 0.93

✅ Automatically retrying the workflow

View workflow run

[jgao54]

@pfcoperez re: testing

I ran this in clickhouse on a non-empty table with max_table_size_to_drop = 1 which repro-ed the error, and with max_table_size_to_drop = 0 it succeeded.

For testing, this feels more like a clickhouse functionality test than peerdb test, but i realized we we don't have a unit test coverage for generateCreateTableSQLForNormalizedTable so creating a basic one to avoid regressions.

[github-actions]

🔄 Flaky Test Detected

Analysis: TestCancelTableAddition_NoRemovalAssumed failed due to a PostgreSQL catalog connection being forcibly terminated (SQLSTATE 57P01 / admin_shutdown), an infrastructure/network error rather than a code assertion failure, likely caused by a race condition in the parallel e2e test environment using Toxiproxy.
Confidence: 0.85

✅ Automatically retrying the workflow

View workflow run