Re: Per-table resync for logical replication subscriptions

From: shveta malik <shveta(dot)malik(at)gmail(dot)com>
To: Cagri Biroglu <cagri(dot)biroglu(at)adyen(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-19 04:45:21
Message-ID: CAJpy0uCQn_8adW5vm2S_cLG1z_q2g4n+qOa1-Zodka1A4LaeRQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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://www.postgresql.org/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-19 05:01:29 Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Previous Message Bharath Rupireddy 2026-11-19 04:36:44 Re: Report oldest xmin source when autovacuum cannot remove tuples