Re: Per-table resync for logical replication subscriptions

Dear Hayato,

Thank you. v12 is attached.

- 0001 is plain tables, and rejects a partitioned table or a partition.
0002 adds partition support.
- The truncate now carries the subscription's own replication origin. That
is what keeps the two-way case from emptying both nodes.
- The failure after the slot drop is confirmed and documented.

> I still prefer the separated patch to make review easier.

Done.

> Isn't it problematic? Is there a good method to fix? My primitive idea is
to
> reject REFRESH command if the target tables are included in a publication.

The cause is that the discard and the refill do not travel under the same
origin filter. The truncate is issued by a user backend, so it carries no
origin and nothing filters it, while the re-copy is done by a tablesync
worker, so it carries one. A subscriber using origin = none therefore takes
the first and drops the second.

v12 sets replorigin_xact_state.origin to the subscription's own origin
around
ExecuteTruncateGuts(), which is the origin the apply worker would have used
had the same truncate arrived from the publisher. So the two halves travel
together: a subscriber takes both or neither.

v12 rows on B right after the command : 100
after re-enable : A 100, B 100

Indeed the primitive idea does prevent the loss, but it refuses a great
deal more
than it has to. "Included in a publication" holds for every table on a
node that has a
FOR ALL TABLES publication, and for every table a forwarder forwards, which
is
one of the topologies this is meant for. In the cascade below it would
refuse
all four refreshes on B, where only one of the four was losing anything:
two of
them publish no TRUNCATE at all, so nothing can cross, and the first
converges
as it is.

Narrowing it to "the node I copy from is subscribed to me" would cut that
down, but it is both unreliable and incomplete: publication names are not
unique across nodes, so the match has to be made on a name the publisher
reports, and a cycle of three or more never matches at all -- with
A -> B -> C -> A the node A copies from is C, and nothing in A's catalogs
mentions C.

Tagging the truncate refuses nothing, needs no detection, and holds for any
cycle length.

> Rejecting the REFRESH TABLE on the intermediate node can solve the issue
but
> may be too aggressive.

Agreed, and I think the matrix argues against it. A -> B -> C, refresh on
B,
with drift introduced first so the repair restores different rows. Only two
settings matter, neither of them the command's:

B's publication C's subscription v11 C rows v12 C rows
publishes TRUNCATE origin = any 100, converged 100, converged
publishes TRUNCATE origin = none 0, LOST 100, keeps its own
withholds TRUNCATE origin = any 100, stale 100, stale
withholds TRUNCATE origin = none 100, stale 100, stale

The second row is the case you did not name, and it is the same bug as the
two-way one: the discard propagates, the refill does not. The third row is
yours; C logs conflict=insert_exists and its apply worker stops.

> I think we need to clarify that the REFRESH must be done again if it
fails.
> Or should we reject the case if the table-sync is ongoing?

Confirmed, and I could fail the command there rather than reconstruct the
state. Two relations with tablesync slots, t1 parked at FINISHEDCOPY and t2
still copying. A pg_recvlogical session holds t2's slot, so the command
drops
t1's slot and then waits on t2's, DROP_REPLICATION_SLOT ... WAIT waiting
for an
active slot rather than failing: it is now inside its drop loop with one
slot
already gone. Cutting its connection to the publisher there gives

ERROR: could not drop replication slot "pg_16400_sync_16392_..." on
publisher: FATAL: terminating connection due to administrator command

and the rollback returns t1 to FINISHEDCOPY with its slot gone, so enabling
the
subscription leaves its worker at

ERROR: could not start WAL streaming: ERROR: replication slot
"pg_16400_sync_16384_..." does not exist

repeating, because tablesync.c resumes a FINISHEDCOPY relation from that
slot,
reading its origin with replorigin_by_name(originname, false). Re-running
the
command repairs it: state i, then r, 400001 rows on both sides. I can send
the
script if you want to run it.

I have documented it rather than rejecting the mid-sync case, for two
reasons.
A relation stuck mid-sync is the main thing this command is for -- a
tablesync
that cannot finish is exactly what the user wants to reset -- so rejecting
it
would remove the primary use. And the exposure is core's own: both
AlterSubscription_refresh() and DropSubscription() drop tablesync slots at
the
very end with missing_ok = true, for the stated reason

/*
* Drop the tablesync slots associated with removed tables. This has
* to be at the end because otherwise if there is an error while doing
* the database operations we won't be able to rollback dropped slots.
*/

and this command does the same, with the slot drops last.

Best regards,
Cagri

On Fri, Oct 23, 2026 at 8:36 AM Hayato Kuroda (Fujitsu) <
kuroda(dot)hayato(at)fujitsu(dot)com> wrote:

> Hi Cagri,
>
> Sorry for the late reply. I have been busy for PG19 open items.
>
> > Rejecting partitioned roots removes only about fifty lines. It does not
> > remove the ancestor locking or the ancestor half of the
> cross-subscription
> > check, which are needed when a leaf is the subscribed table, as it is
> whenever
> > publish_via_partition_root is off. Both problems you found here were in
> that
> > code, so it would stay in 0001 and 0002 would add the easy part. I would
> > rather keep one patch, but I will split it if you prefer.
>
> I still prefer the separated patch to make review easier.
>
> > thank you. With C -> B -> A and A on origin = none, the same
> > copy made CREATE SUBSCRIPTION warn and REFRESH TABLE stay silent.
> > check_publications_origin_tables() skips the tables a subscription
> already
> > has, because those come throuqgh WAL where origins are known, and this
> command
> > is the one thing that puts such a table back through a copy. v10 runs
> the
> > check and passes the subscription's other tables as the skip list, so it
> warns
> > for a table another subscription writes and stays quiet otherwise.
>
> Assuming that there is a bidirectional setup A <-> B, and REFRESH TABLE is
> done
> on node A. In this case the TRUNCATE can be replicated to the node B and
> table
> on both nodes are empty. Tuples are lost after the subscription is enabled
> because the copy source does not have ones anymore. Isn't it problematic?
> Is there a good method to fix?
> My primitive idea is to reject REFRESH command if the target tables are
> included
> in a publication.
>
> Below are new comments:
> 01.
> Assuming that there is a cascading replication: A->B->C and origin=any,
> and the
> publication on B does not publish TRUNCATE. If the REFRESH TABLE is done
> on B,
> the TRUNCATE cannot be replicated but the re-synced data can be replicated
> again,
> can cause the data conflict. Should we solve the issue? Rejecting the
> REFRESH TABLE
> on the intermediate node can solve the issue but may be too aggressive.
>
> 02.
> Assuming the normal logical replication system A->B, and the REFRESH TABLE
> is
> done while the srsubstate is still FINISHCOPY. If the command fails after
> dropping
> the replication origin and replication slot, the tablesync worker will
> assume
> the slot exists and tries to stream via the slot, it can cause the ERROR.
>
> I think we need to clarify that the REFRESH must be done again if it fails.
> Or should we reject the case if the table-sync is ongoing?
>
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>
>