| From: | shveta malik <shveta(dot)malik(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | vignesh C <vignesh21(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, shveta malik <shvetamalik(at)gmail(dot)com> |
| Subject: | Re: Initial table sync copies the whole table when it is no longer published |
| Date: | 2026-11-16 10:03:01 |
| Message-ID: | CAJpy0uDw0crvrUFU=FsqcuvH=dJbqPASkUhnDB=bdHV1SAc+0A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, Nov 16, 2026 at 3:14 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> Hi All,
> (CCing the people involved in commit 7c99dc5, which handled a related case.)
>
> While working on BUG #19864, I found an issue in tablesync that I
> think is worth fixing. It is somewhat similar to the case handled by
> commit 7c99dc5 (a missing publication in the apply worker), but
> happens during the initial sync.
>
>
> Steps to reproduce:
>
> Pub:
> create table tab1(a int, b int, c int);
> insert into tab1 select i, i, i from generate_series(1, 5) i;
> create publication pub1 for table tab1(c) where (c > 3);
>
> Sub:
> create table tab1(a int, b int, c int);
> create subscription sub1 connection '...' publication pub1
> WITH(enabled='false');
>
> Pub:
> drop publication pub1;
>
> Sub:
> alter subscription sub1 ENABLE;
>
> -- wait a few seconds for the sync
> select * from tab1 order by c;
> select srrelid::regclass, srsubstate FROM pg_subscription_rel;
>
> Result:
> a | b | c
> ---+---+---
> 1 | 1 | 1
> 2 | 2 | 2
> 3 | 3 | 3
> 4 | 4 | 4
> 5 | 5 | 5
>
> srrelid | srsubstate
> ---------+------------
> tab1 | r
>
> Here tablesync ends up copying all the columns and all the rows of a
> table, ignoring the publication's column list and row filter. It
> happens when the publication is dropped (or the table is removed from
> the publication) before the table's initial sync starts. The table is
> marked ready and no error is reported on either side. Without the DROP
> PUBLICATION step, we get only (NULL, NULL, 4) and (NULL, NULL, 5), as
> expected.
>
> The cause is in fetch_remote_table_info(). It fetches the column list
> and the row filter from the publisher, one row per publication. When
> the publication is gone, both queries return no rows, and that is
> treated the same as "no column list" and "no row filter", so the COPY
> takes the whole table. Note that a table published without a column
> list or row filter still returns one row from these queries, with NULL
> values. Only a table that no publication publishes returns no rows.
>
> ALTER SUBSCRIPTION ... REFRESH PUBLICATION does not really help here.
> It can't be run on a disabled subscription, and once we enable the
> subscription, the sync may copy the wrong data before the refresh
> runs.
>
> It is also not just about the ENABLE command; any table still waiting
> for its initial sync can hit this when its turn comes, e.g. when many
> tables are queued for a limited number of sync workers, even though
> the sub was enabled long before the publication was dropped. REFRESH
> PUBLICATION can only prevent this if it runs after the drop and before
> the table’s sync starts, which is a race the user can’t control.
>
> And if the refresh is run after the sync, it removes the table from
> the subscription, but the wrongly copied data stays on the subscriber.
>
> Attached patch fixes this by raising an error when the column-list and
> row-filter queries on the publisher return no rows, since that means
> none of the subscription's publications publishes the table anymore,
> instead of treating it as "no column list" and "no row filter" and
> copying everything. Unlike commit 7c99dc5, which skips a missing
> publication with a WARNING in the apply worker, I think raising an
> error is the right choice here. A tablesync error only affects that
> one table’s sync, and the other tables and the apply worker continue.
> Skipping, on the other hand, would mean marking the table ready
> without copying it, so if the table is published again, its initial
> data would be silently missing on the subscriber. Thoughts?
>
I missed to mention one behaviour change post-patch for partitioned
tables: if publish_via_partition_root is changed after the
subscription is created but before its tables are synced, the
subscription still lists the relations published under the old setting
(the partitions, or the root table), which the publisher no longer
publishes. HEAD copies them anyway, and the data is correct in this
case. With the patch, their initial tablesync errors until REFRESH
PUBLICATION is run. I think it shouldbe acceptable, since a REFRESH is
needed after such a change anyway. Thoughts?
thanks
Shveta
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dean Rasheed | 2026-11-16 10:07:50 | Re: [PATCH] Normalize numeric/bigint scientific notation after rounding |
| Previous Message | Dean Rasheed | 2026-11-16 10:00:40 | Re: [PATCH] Normalize numeric/bigint scientific notation after rounding |