flaky copy_data retry_test

[levkk]

PgDog version
Current main.

Description
Test fails randomly. We should either ignore it until we fix it...or fix it. :)

[meskill]

Disabled test for now. It's interesting how this test works for some time and then starts to fail. I had hopes the 2pc...

There are multiple issues that should be fixed:

1. Add more logs to track the flow more precisely - missing logs for success 2pc cleanup, new attempts etc.
2. The 2pc for copy_data should actually validate if we commited the transaction after the retries or not. Rn it always goes with retry
3. destination_has_rows check mostly hits the closed pool that is pointless most of the time. That should use either reloaded pool or run the standalone request
4. Try to update test to fail it more often, instead of waiting it to fail on CI. The test commit_sync should've used for this, but it doesn't fail on ci, while the usual retry_test does fail like in the issue

The relevant logs for reference:

CI logs

2026-07-14T06:40:00.352167Z  INFO replication slot "__pgdog_xdqzxgr1lm5w8eslrd8qdcqd" at lsn 0/1014E408 created [pgdog@127.0.0.1:15432/pgdog]
[retry_test] Killing shard_1 during active copy...
 Container copy_data-shard_1-1  Killing
2026-07-14T06:40:00.413908Z  INFO closing server connection: state=error, reason=other [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.423472Z  INFO closing server connection: state=idle in transaction, reason=other [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:00.423514Z  WARN data sync for "copy_data"."users_0" failed (attempt 1/8): 2pc commit failed for __pgdog_2pc_14604649331232827475: parallel connection error, retrying after 500ms...
2026-07-14T06:40:00.423519Z  INFO closing server connection: state=idle, reason=other [pgdog@127.0.0.1:15433/pgdog1]
2026-07-14T06:40:00.423711Z ERROR [2pc] error cleaning up "__pgdog_2pc_14604649331232827475" transaction: net: connection closed by peer
2026-07-14T06:40:00.423723Z ERROR server cleanup failed: net: connection closed by peer [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.423724Z  INFO closing server connection: state=error, reason=error [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.423727Z ERROR pool received broken server connection, closing [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.423792Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=1 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.424022Z ERROR error connecting to server: unexpected end of file [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.425092Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=1 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.425234Z ERROR error connecting to server: unexpected end of file [pgdog@127.0.0.1:15434/pgdog2]
 Container copy_data-shard_1-1  Killed
[retry_test] Issuing RELOAD while shard_1 is down...
2026-07-14T06:40:00.626473Z  INFO client "admin" connected to database "admin" [127.0.0.1:50900, auth: scram] 
2026-07-14T06:40:00.626586Z  INFO reloading configuration
2026-07-14T06:40:00.626658Z  INFO loaded "/home/runner/_work/pgdog/pgdog/integration/copy_data/retry_test/pgdog.toml"
2026-07-14T06:40:00.626669Z  INFO loaded "/home/runner/_work/pgdog/pgdog/integration/copy_data/retry_test/users.toml"
2026-07-14T06:40:00.626779Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=1, waiting=1 [pgdog@127.0.0.1:15433/pgdog1]
RELOAD
2026-07-14T06:40:00.626791Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=1 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.626836Z  INFO client "admin" disconnected from database "admin" [127.0.0.1:50900]
2026-07-14T06:40:00.626837Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.627813Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=1 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.627847Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.628808Z  INFO new server connection: auth=scram, source=config, reason=client [pgdog@127.0.0.1:15433/pgdog1] 
2026-07-14T06:40:00.628887Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.628923Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.633511Z  INFO loaded schema for 14 tables on shard 0 [pgdog@127.0.0.1:15433/pgdog1]
2026-07-14T06:40:00.960987Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:00.961097Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.293795Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.293920Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.627432Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.627536Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.960030Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:01.960157Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.292722Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.292830Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.626373Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.626488Z ERROR error connecting to server: Connection refused (os error 111) [pgdog@127.0.0.1:15434/pgdog2]
[retry_test] Starting shard_1...
 Container copy_data-shard_1-1  Starting
 Container copy_data-shard_1-1  Started
2026-07-14T06:40:02.959001Z  INFO new connection requested: reason=client, min=1, max=10, idle=0, taken=0, waiting=2 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.964457Z  INFO new server connection: auth=scram, source=config, reason=client [pgdog@127.0.0.1:15434/pgdog2] 
2026-07-14T06:40:02.977436Z  INFO loaded schema for 14 tables on shard 1 [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:02.977867Z  WARN could not verify destination rows on shard 0: pool: pool is shut down; proceeding as if empty
2026-07-14T06:40:02.977873Z  WARN could not verify destination rows on shard 1: pool: pool is shut down; proceeding as if empty
2026-07-14T06:40:02.977875Z  INFO data sync for "copy_data"."users_0" started [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:02.979910Z  INFO new server connection: auth=scram, source=config, reason=replication [pgdog@127.0.0.1:15433/pgdog1] 
2026-07-14T06:40:02.981726Z  INFO new server connection: auth=scram, source=config, reason=replication [pgdog@127.0.0.1:15434/pgdog2] 
2026-07-14T06:40:02.983618Z  INFO new server connection: auth=scram, source=config, reason=replication [pgdog@127.0.0.1:15432/pgdog] 
2026-07-14T06:40:02.983628Z  INFO creating replication slot "__pgdog_bepjd311bbehwtgdlezfuwgg" [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:02.985860Z  INFO replication slot "__pgdog_bepjd311bbehwtgdlezfuwgg" at lsn 0/1014E440 created [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:03.020153Z  INFO closing server connection: state=active, reason=other [pgdog@127.0.0.1:15433/pgdog1]
2026-07-14T06:40:03.020154Z  INFO closing server connection: state=idle in transaction, reason=other [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:03.020173Z  WARN could not verify destination rows on shard 0: pool: pool is shut down; proceeding as if empty
2026-07-14T06:40:03.020176Z  WARN could not verify destination rows on shard 1: pool: pool is shut down; proceeding as if empty
2026-07-14T06:40:03.020184Z  INFO closing server connection: state=active, reason=other [pgdog@127.0.0.1:15434/pgdog2]
2026-07-14T06:40:03.020249Z ERROR task failed: pg_error: ERROR: 23505 duplicate key value violates unique constraint "users_0_pkey"
Key (id, tenant_id)=(1, 1) already exists.
2026-07-14T06:40:03.020252Z  INFO task{id=0}:task{id=1}: state transition to: failed: pg_error: ERROR: 23505 duplicate key value violates unique constraint "users_0_pkey"
Key (id, tenant_id)=(1, 1) already exists.
2026-07-14T06:40:03.022025Z  WARN task{id=0}: replication slot "__pgdog_repl_ecih9ehzmts4sq1j57g_0" dropped [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:03.022032Z  INFO task{id=0}: closing server connection: state=idle, reason=other [pgdog@127.0.0.1:15432/pgdog]
2026-07-14T06:40:03.022051Z ERROR task failed: task failed: pg_error: ERROR: 23505 duplicate key value violates unique constraint "users_0_pkey"
Key (id, tenant_id)=(1, 1) already exists.
2026-07-14T06:40:03.022054Z  INFO task{id=0}: state transition to: failed: task failed: pg_error: ERROR: 23505 duplicate key value violates unique constraint "users_0_pkey"
Key (id, tenant_id)=(1, 1) already exists.
[retry_test] shard_1 is ready.
[retry_test] Waiting for SHOW TASKS to report a Replication task...

[ygxio]

Is the same test, that was enabled after putting 2pc into copy?

[meskill]

Is the same test, that was enabled after putting 2pc into copy?

yes

[levkk]

No, that's not the test that's failing. The original title is correct, I was talking about the test in pgdog unit tests:

pgdog backend::replication::logical::orchestrator::tests::wait_for_replication_finishes_with_unrelated_writes

The one you disabled in #1192 actually has been running very well over the past few days.

[meskill]

oh, sorry, yeah.
But I just opened the main CI in the morning and saw only the copy_data test failing
https://github.com/pgdogdev/pgdog/actions/runs/29311786800/job/87017508079

so, still the issue, but yeah the different one

[levkk]

Definitely reduced its frequency by a lot, I don't even remember seeing that one fail...so that's good :)

[meskill]

Let's leave this issue as is now in low priority.

The original flakiness fixed in the #1201