| From: | vignesh C <vignesh21(at)gmail(dot)com> |
|---|---|
| To: | shveta malik <shveta(dot)malik(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 04:22:38 |
| Message-ID: | CALDaNm2XYERb=sGDUEZVQ_uGDXaRQ8bnY_9n7b5gTayT9wu3Mg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
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.
Regards,
Vignesh
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Hayato Kuroda (Fujitsu) | 2026-11-19 04:21:33 | RE: [PATCH] pg_createsubscriber: add hint to wal_level error |