Re: Initial table sync copies the whole table when it is no longer published

From: shveta malik <shveta(dot)malik(at)gmail(dot)com>
To: Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, 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-17 10:09:32
Message-ID: CAJpy0uCiBWrE+B4gkrE0yDVPy-S3P4sNMbMsaNdwCfz0R1t4Kw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Nov 16, 2026 at 4:08 PM Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> wrote:
>
> 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.

Thanks Shlok for testing it on different versions.

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

Thanks for reporting. Yes, it has the same root cause: the
subscription still lists tab1 from before, and since the new unlogged
table cannot be published, the column-list query returns no rows,
which tablesync interprets as “no column list” and copies evrything.

I can add this case to the patch's test as well if others think so;
otherwise, the test I have added should suffice.

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Hayato Kuroda (Fujitsu) 2026-11-17 10:17:08 RE: Test publication.sql racing with subscription.sql might fail due to cache lookup error
Previous Message Ashutosh Sharma 2026-11-17 10:06:05 Proposal: cross-database unlogged relation size reporting