Re: Per-table resync for logical replication subscriptions

From: Cagri Biroglu <cagri(dot)biroglu(at)adyen(dot)com>
To: shveta malik <shveta(dot)malik(at)gmail(dot)com>
Cc: Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com>, "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, Ajin Cherian <itsajin(at)gmail(dot)com>, "smithpb2250(at)gmail(dot)com" <smithpb2250(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, shveta malik <shvetamalik(at)gmail(dot)com>
Subject: Re: Per-table resync for logical replication subscriptions
Date: 2026-11-21 18:10:58
Message-ID: CAA36mspG4VVHcJfuagE9U0s2YP_fN-SshcUZw+Md9pemN6EW3g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Shveta,

Thanks, and thanks Peter for pointing you here. Having followed [1],
I agree it is a use case: with your reproducer on master today the
table is copied whole, and once the publication is back, REFRESH
TABLE re-copies just that table with its column list and row filter.
It would also be how to copy a skipped table, if [1] later moves to a
WARNING as Peter suggested.

For the case you describe, I would keep REFRESH TABLE as it is and
rely on [1]:

- With [1], the re-seeded table ends where [1] leaves any table whose
initial sync finds it unpublished: it stays in 'd', nothing is
copied, and "with the ERROR the next retry simply syncs it" once it
is published again, as you said in [1]. If it stays unpublished,
the way out, with disable_on_error too, is the one your v4-0002
documents. I tested both on v15 with your v4.

- Removing it has the problems you gave in [1] against the worker
doing it: a table published again stays out until someone runs
REFRESH PUBLICATION, and a per-table removal is only a partial
refresh, e.g. after a publish_via_partition_root change it would
drop the partitions without adding the root.

- A check with the publisher first cannot replace [1], since the
table can be unpublished right after it, and every REFRESH TABLE
would have to connect; for ready tables under origin = any it does
not today. Done with fetch_relation_list(), it also has the
publisher open every published table, the cost fd7a25af11e took out
of tablesync: about 160 ms for a FOR ALL TABLES publication of 50k
tables here, and it waits behind an ACCESS EXCLUSIVE lock on any of
them.

REFRESH TABLE is meant for a table that is still published and
replicating but has drifted, and when to reconcile membership stays
with the user, as with REFRESH SEQUENCES. So in the next version I
would add the note REFRESH SEQUENCES has, to run REFRESH PUBLICATION
first if the publication's set of tables has changed; a table it
removes is then refused by REFRESH TABLE without being truncated.

Best regards,
Cagri Biroglu

On Thu, Nov 19, 2026 at 5:45 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:

> Hi,
>
> I agree with the idea of the patch and the need for this feature.
>
> While looking at this from the angle of [1], I noticed that REFRESH
> TABLE only looks at the subscriber’s pg_subscription_rel; it does not
> check whether the named table is still published by any of the
> subscription’s publications.
>
> I tried v15 (with the fix from [1] applied): sync tab1 (using test
> given in [1]), then on the publisher ALTER PUBLICATION pub1 DROP TABLE
> tab1, then on the subscriber DISABLE + REFRESH TABLE tab1. The command
> succeeds, truncates tab1 (5 rows -> 0) and resets it to 'i'.
> After ENABLE, tablesync fails because the table is no longer
> published, so the local data is gone and won’t be copied back unless
> the table is published again and error raised by [1] is also not
> resolved. With disable_on_error = true, the subscription is disabled
> again, and repeating REFRESH TABLE just repeats this.
>
> Since REFRESH TABLE is a per-table refresh, I would expect it to also
> reconcile the table with the publications, similar to REFRESH
> PUBLICATION: if the named table is no longer published by any of the
> subscription’s publications, it should be removed from the
> subscription instead of being truncated and re-copied. At a minimum,
> the command should verify with the publisher that each named table is
> still published before truncating it, and error out otherwise.
> Thoughts?
>
> [1]:
> https://stream.boxwatch.ru/message-id/CAJpy0uAWSLH-SMeH0JoqvGACv8truSfXiwFp4PCkTJE93CGigA%40mail.gmail.com
>
> (Thanks Peter for pointing me to this thread and for the off-list
> discussion on this.)
>
> thanks
> Shveta
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message shihao zhong 2026-11-21 18:39:46 Re: Hash index: stop registering pin-only bucket pages in WAL (remove REGBUF_NO_CHANGE)
Previous Message surya poondla 2026-11-21 17:49:28 Re: Checkpoints stuck after a failed bulk write inside a savepoint