| 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
>
| 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 |