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: vignesh C <vignesh21(at)gmail(dot)com>
Cc: Ajin Cherian <itsajin(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, 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-19 09:21:52
Message-ID: CAJpy0uDbuKTBnOPfMh05EzbzuSy-MNw_dnm7uAhBk8voY=8ZpQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Nov 19, 2026 at 9:52 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, 18 Nov 2026 at 10:40, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Nov 18, 2026 at 9:48 AM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
> > >
> > > On Wed, Nov 18, 2026 at 2:39 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > >
> > > > Good point Ajin. I did not think of it earlier, but on analysing it
> > > > now, I think there is a way out without the race: since
> > > > disable_on_error can be changed on a disabled sub, the user can
> > > > temporarily set 'disable_on_error=false' on disabled sub, then ENABLE
> > > > the sub, then REFRESH PUBLICATION, and then set disable_on_error back
> > > > to true. I tested this with the patch, and it works.
> > > >
> > >
> > > Yes, that works as a workaround.
> > >
> > > >
> > > > As you said, this applies to all tablesync errors caused by
> > > > publisher-side changes, so I think documenting it would be better done
> > > > separately,e.g. as a note in the disable_on_error description?
> > > > Thoughts? But if others feel that we can combine it in this patch, I
> > > > am okay to do that.
> > >
> > > Yes, a note in the disable_on_error description sounds good.
> >
> > Done. Attached v3-0002. No change in 0001.
> >
> > > On Wed, Nov 18, 2026 at 2:40 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > >
> > > > I missed mentioning that alternatively, the table can be published
> > > > again on the publisher before re-enabling (if necessary).
> > > >
> > >
> > > I don't think users would want to do that if they removed the table
> > > from the publication deliberately. It also isn't an option for an
> > > UNLOGGED table like in Shlok's example, which can't be published at
> > > all, or for a table that has been dropped on the publisher.
> >
> > Okay, I have still added a generic line about this in the doc without
> > mentioning any specific resolution in this context.
>
> Thanks for the patch, couple of comments:
> 1. The inserts after the error can be removed:
> +# Changes made on the publisher after that are not replicated either.
> +$node_publisher->safe_psql(
> + 'postgres', qq(
> + INSERT INTO tab_4 VALUES (6, 6, 6);
> + INSERT INTO tab_5 VALUES (6, 6, 6);
> +));
> +$node_publisher->wait_for_catchup('tap_sub_2');
> +
> +$result = $node_subscriber->safe_psql('postgres',
> + "SELECT (SELECT count(*) FROM tab_4), (SELECT count(*) FROM tab_5)");
> +is($result, qq(0|0),
> + 'check that no data is copied or replicated for tables that
> are no longer published'
> +);
> +
>
> The tablesync worker errors out, so tab_4 and tab_5 never reach the
> ready state. Their changes are never applied, and this part doesn't
> test the fix. The
> two wait_for_log checks already show that the sync failed.

Agreed that the inserts don’t test the fix, since the tables never
become ready; I have removed them along with the wait_for_catchup.
I’ve kept the count check though: the wait_for_log checks show that
the sync failed, while the count confirms the actual outcome, that
nothing was copied, which is the behaviour being fixed.

> 2. The REFRESH PUBLICATION block can be removed:
> +# REFRESH PUBLICATION removes the tables from the subscription.
> +$node_subscriber->safe_psql('postgres',
> + "ALTER SUBSCRIPTION tap_sub_2 REFRESH PUBLICATION");
> +
> +$result = $node_subscriber->safe_psql('postgres',
> + "SELECT count(*) FROM pg_subscription_rel WHERE srrelid IN
> ('tab_4'::regclass, 'tab_5'::regclass)"
> +);
> +is($result, qq(0),
> + 'check that REFRESH PUBLICATION removes the tables from the
> subscription');
>
> 025_rep_changes_for_schema.pl already checks that tables are removed
> from pg_subscription_rel after REFRESH
> PUBLICATION.

I gave it some thought; I think we should keep it. Both the errhint
and the doc patch suggest REFRESH PUBLICATION as the way to recover,
so it seems good to test that it works in exactly the state this fix
leaves behind, i.e. tables whose initial sync failed and are still in
DATASYNC. 025 only removes tables that are already READY. What do you
say?

Attached v4 with the change.

thanks
Shveta

Attachment Content-Type Size
v4-0001-Do-not-copy-the-whole-table-in-tablesync-when-no-.patch application/octet-stream 7.5 KB
v4-0002-Doc-Explain-how-to-recover-a-subscription-disable.patch application/octet-stream 3.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2026-11-19 09:26:41 Re: Initial table sync copies the whole table when it is no longer published
Previous Message Maxim Orlov 2026-11-19 08:56:21 Re: Fix race with LLVM and bison.