| From: | Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> |
|---|---|
| To: | shveta malik <shveta(dot)malik(at)gmail(dot)com> |
| Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, shveta malik <shvetamalik(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
| Subject: | Re: Initial table sync copies the whole table when it is no longer published |
| Date: | 2026-11-16 10:38:43 |
| Message-ID: | CANhcyEWcx=ndzUW4GXxu-dFsWXnO-+xWhJLLnW5B4R_wPx8pcQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, 16 Nov 2026 at 15:15, 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.
>
Hi Shveta,
I am able to reproduce the issue. I also agree with and confirm the
above analysis.
In fetch_remote_table_info both the queries return no row and hence it
is treated the same as "no column list" and "no row filter".
The COPY command formed is: COPY public.tab1 (a, b, c) TO STDOUT.
I am able to reproduce the issue from PG15 till PG19 and on master.
While testing I also encountered one more issue, which has the same root cause.
And the tablesync worker can copy data of UNLOGGED tables.
Pub:
create table tab1(a int, b int, c int);
create publication pub1 for all tables;
Sub:
create table tab1(a int, b int, c int);
create subscription sub1 connection '...' publication pub1
WITH(enabled='false');
Pub:
drop table tab1;
create unlogged table tab1(a int, b int, c int);
insert into tab1 select i, i, i from generate_series(1, 5) i;
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
In this case as well, in fetch_remote_table_info both the queries
return no row and hence it is treated the same as "no column list" and
"no row filter".
The COPY command formed is: COPY public.tab1 (a, b, c) TO STDOUT.
> 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 have not checked the fix. Will review and test it.
Thanks,
Shlok Kyal
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bertrand Drouvot | 2026-11-16 10:38:56 | Re: pgstat: Flush some statistics within running transactions, take 2 |
| Previous Message | Amit Kapila | 2026-11-16 10:16:25 | Re: Detect mismatched subscription and replication slot properties |