On Mon, 16 Nov 2026 at 15:15, shveta malik <[email protected]> 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
