public inbox for [email protected]help / color / mirror / Atom feed
DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data 4+ messages / 2 participants [nested] [flat]
* DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data @ 2026-07-02 02:58 Peter Smith <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Peter Smith @ 2026-07-02 02:58 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]> Today I was experimenting with logical replication of sequences and encountered some "unexpected" behaviour. It makes sense in hindsight, but at the time it seemed surprising. It turns out that it is possible to synchronise sequence values (using "ALTER SUBSCRIPTION... REFRESH SEQUENCES") even when those sequences are not published. That's because a subscription only knows which sequences are subscribed to from the `pg_subscription_rel` table, but that information can be stale if the publication has since stopped publishing those sequences. In other words, if a sequence was removed from the publication but REFRESH PUBLICATION hasn't been run, REFRESH SEQUENCES would continue to synchronise it since it is still in `pg_subscription_rel`. The docs do refer to "currently subscribed sequences" but do not explicitly warn about this gotcha. PSA: a patch to add a clarifying sentence. (I also removed an earlier link to "ALTER SUBSCRIPTION ... REFRESH PUBLICATION", because it did not seem good to have multiple links to the same place). ====== Kind Regards, Peter Smith. Fujitsu Australia Attachments: [application/octet-stream] v1-0001-DOCS-clarify-that-REFRESH-SEQUENCES-might-be-usin.patch (1.8K, ../../CAHut+PtFkGvZNihGRDoghWNKMfJufEpR9+thbG_8qPQ7RyVN4w@mail.gmail.com/2-v1-0001-DOCS-clarify-that-REFRESH-SEQUENCES-might-be-usin.patch) download | inline diff: From afa7eba400b593e2a73a5fd618e510cef571aee7 Mon Sep 17 00:00:00 2001 From: Peter Smith <[email protected]> Date: Thu, 2 Jul 2026 14:48:43 +1200 Subject: [PATCH v1] DOCS - clarify that REFRESH SEQUENCES might be using stale publication data --- doc/src/sgml/ref/alter_subscription.sgml | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index e4f0b6b16c7..c9ed7745554 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -236,13 +236,19 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <listitem> <para> Re-synchronize sequence data with the publisher. Unlike - <link linkend="sql-altersubscription-params-refresh-publication"> - <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> which + <literal>REFRESH PUBLICATION</literal> which only has the ability to synchronize newly added sequences, <literal>REFRESH SEQUENCES</literal> will re-synchronize the sequence data for all currently subscribed sequences. It does not add or remove sequences from the subscription to match the publication. </para> + <para> + If the publication's sequence membership has changed, + <link linkend="sql-altersubscription-params-refresh-publication"> + <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> + should be run before <literal>REFRESH SEQUENCES</literal> to ensure the + subscription's known sequence list is current. + </para> <para> See <xref linkend="sequence-definition-mismatches"/> for recommendations on how to handle any warnings about sequence definition -- 2.47.3 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data @ 2026-07-02 06:48 Amit Kapila <[email protected]> parent: Peter Smith <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Amit Kapila @ 2026-07-02 06:48 UTC (permalink / raw) To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Thu, Jul 2, 2026 at 8:29 AM Peter Smith <[email protected]> wrote: > > The docs do refer to "currently subscribed sequences" but do not > explicitly warn about this gotcha. > > PSA: a patch to add a clarifying sentence. > It does not add or remove sequences from the subscription to match the publication. </para> + <para> + If the publication's sequence membership has changed, + <link linkend="sql-altersubscription-params-refresh-publication"> + <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> + should be run before <literal>REFRESH SEQUENCES</literal> to ensure the + subscription's known sequence list is current. + </para> It is already mentioned that this command "does not add or remove sequences from the subscription to match the publication.". Adding more information, especially a separate para for the same, appears like bloating the text in docs. OTOH, I see your point of running REFRESH PUBLICATION before REFRESH SEQUENCES which I tried to cover in the existing paragraph. Apart from that, I have merged your doc-changes from another thread [1] which is on the same topic. Let's commit them together. [1] - https://www.postgresql.org/message-id/CAHut%2BPsrVntfrJ8gV45%2B6%3D1bs5dTWD5mU7Q6aKrBnf1vmyZQkQ%40ma... -- With Regards, Amit Kapila. Attachments: [application/octet-stream] v2-0001-doc-Clarify-sequence-synchronization-commands.patch (3.1K, ../../CAA4eK1Jr-f1fhELi2XxF=u8NNBBNOPv7dQiNPFsd3twC1w98BA@mail.gmail.com/2-v2-0001-doc-Clarify-sequence-synchronization-commands.patch) download | inline diff: From 38c6b38fb36cca7ce9f2de0f787e3a3a8fec2b3b Mon Sep 17 00:00:00 2001 From: Amit Kapila <[email protected]> Date: Thu, 2 Jul 2026 11:35:16 +0530 Subject: [PATCH v2] doc: Clarify sequence synchronization commands. Improve the REFRESH SEQUENCES description in ALTER SUBSCRIPTION to more accurately explain how it differs from REFRESH PUBLICATION: REFRESH PUBLICATION synchronizes the subscription's set of sequences with the publication (adding new and removing dropped sequences), whereas REFRESH SEQUENCES only re-synchronizes data for the currently subscribed sequences without changing which sequences are subscribed. Also advise running REFRESH PUBLICATION first when the publication's set of sequences has changed. In the "Replicating Sequences" section, note that CREATE SUBSCRIPTION uses copy_data = true (the default) to copy the initial sequence values, and link to the copy_data parameter. --- doc/src/sgml/logical-replication.sgml | 4 +++- doc/src/sgml/ref/alter_subscription.sgml | 10 ++++++---- 2 files changed, 9 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index f21239af6b8..63935fa2eb3 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1784,7 +1784,9 @@ Included in publications: <listitem> <para> use <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> - to initially synchronize the published sequences. + with <link linkend="sql-createsubscription-params-with-copy-data"> + <literal>copy_data = true</literal></link> (the default) to copy the + initial sequence values from the publisher. </para> </listitem> <listitem> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index fad1f90956a..8d64744375a 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -237,11 +237,13 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <para> Re-synchronize sequence data with the publisher. Unlike <link linkend="sql-altersubscription-params-refresh-publication"> - <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> which - only has the ability to synchronize newly added sequences, + <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, + which synchronizes the subscription's set of sequences with the + publication (adding new and removing dropped sequences), <literal>REFRESH SEQUENCES</literal> will re-synchronize the sequence - data for all currently subscribed sequences. It does not add or remove - sequences from the subscription to match the publication. + data for all currently subscribed sequences without changing which + sequences are subscribed. Run <literal>REFRESH PUBLICATION</literal> + first if the publication's set of sequences has changed. </para> <para> See <xref linkend="sequence-definition-mismatches"/> for -- 2.54.0 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data @ 2026-07-03 03:18 Peter Smith <[email protected]> parent: Amit Kapila <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Peter Smith @ 2026-07-03 03:18 UTC (permalink / raw) To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Thu, Jul 2, 2026 at 6:48 PM Amit Kapila <[email protected]> wrote: > > On Thu, Jul 2, 2026 at 8:29 AM Peter Smith <[email protected]> wrote: > > > > The docs do refer to "currently subscribed sequences" but do not > > explicitly warn about this gotcha. > > > > PSA: a patch to add a clarifying sentence. > > > > It does not add or remove > sequences from the subscription to match the publication. > </para> > + <para> > + If the publication's sequence membership has changed, > + <link linkend="sql-altersubscription-params-refresh-publication"> > + <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> > + should be run before <literal>REFRESH SEQUENCES</literal> to ensure the > + subscription's known sequence list is current. > + </para> > > It is already mentioned that this command "does not add or remove > sequences from the subscription to match the publication.". Adding > more information, especially a separate para for the same, appears > like bloating the text in docs. OTOH, I see your point of running > REFRESH PUBLICATION before REFRESH SEQUENCES which I tried to cover in > the existing paragraph. Apart from that, I have merged your > doc-changes from another thread [1] which is on the same topic. Let's > commit them together. > +1 to combine both patches. Upon re-reading, I think there is still ambiguity due to that word "synchronizing". IMO, when we are talking about "synchronizing" sequences, we mean that the sequence *values* on the subscriber will be synchronized with those from the publisher. So, REFRESH SEQUENCES synchronizes all currently known sequences. OTOH, the REFRESH PUBLICATION refreshes all sequences known to the subscriber, but it will only synchronize values for newly added ones (not all of them). So "synchnonizing" is a misleading term to use when describing the `pg_subscription_rel` members getting updated. ~~~ CHANGES: Alter Subscription REFRESH SEQUENCES: The REFRESH PUBLICATION option already correctly describes all that above ("Previously subscribed sequences are not re-synchronized."), so I think the fix is to remove the unnecessary explanation: "Unlike ...". After doing that, the remaining text is shorter and more correct. Logical Replication: (2nd bullet) Should also mention that REFRESH PUBLICATION refreshes the sequences known to the subscription. ~~~ PSA v3. ====== Kind Regards, Peter Smith. Fujitsu Australia Attachments: [application/octet-stream] v3-0001-doc-Clarify-sequence-synchronization-commands.patch (3.4K, ../../CAHut+PurBp3fHRMBO18Jeg_1TeeWjY9XYk6rD6fXAKgDRQxaMg@mail.gmail.com/2-v3-0001-doc-Clarify-sequence-synchronization-commands.patch) download | inline diff: From 4be8ea86504ec8c870ae0f615a5efef38b65571d Mon Sep 17 00:00:00 2001 From: Peter Smith <[email protected]> Date: Fri, 3 Jul 2026 15:03:08 +1200 Subject: [PATCH v3] doc: Clarify sequence synchronization commands. Improve the REFRESH SEQUENCES description in ALTER SUBSCRIPTION to more accurately explain how it differs from REFRESH PUBLICATION: REFRESH PUBLICATION synchronizes the subscription's set of sequences with the publication (adding new and removing dropped sequences), whereas REFRESH SEQUENCES only re-synchronizes data for the currently subscribed sequences without changing which sequences are subscribed. Also advise running REFRESH PUBLICATION first when the publication's set of sequences has changed. In the "Replicating Sequences" section, note that CREATE SUBSCRIPTION uses copy_data = true (the default) to copy the initial sequence values, and link to the copy_data parameter. --- doc/src/sgml/logical-replication.sgml | 7 +++++-- doc/src/sgml/ref/alter_subscription.sgml | 10 ++++------ 2 files changed, 9 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 5befefd9c5a..1df57eb1998 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1769,14 +1769,17 @@ Included in publications: <listitem> <para> use <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> - to initially synchronize the published sequences. + with <link linkend="sql-createsubscription-params-with-copy-data"> + <literal>copy_data = true</literal></link> (the default) to copy the + initial sequence values from the publisher. </para> </listitem> <listitem> <para> use <link linkend="sql-altersubscription-params-refresh-publication"> <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> - to synchronize only newly added sequences. + to refresh what sequences are known to the subscription, and + synchronize only newly added sequences. </para> </listitem> <listitem> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index e4f0b6b16c7..3d66a3b3a8f 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -235,13 +235,11 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <term><literal>REFRESH SEQUENCES</literal></term> <listitem> <para> - Re-synchronize sequence data with the publisher. Unlike - <link linkend="sql-altersubscription-params-refresh-publication"> - <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> which - only has the ability to synchronize newly added sequences, + Re-synchronize sequence data with the publisher. <literal>REFRESH SEQUENCES</literal> will re-synchronize the sequence - data for all currently subscribed sequences. It does not add or remove - sequences from the subscription to match the publication. + data for all currently subscribed sequences without changing which + sequences are subscribed. Run <literal>REFRESH PUBLICATION</literal> + first if the publication's set of sequences has changed. </para> <para> See <xref linkend="sequence-definition-mismatches"/> for -- 2.47.3 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data @ 2026-07-06 03:48 Amit Kapila <[email protected]> parent: Peter Smith <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Amit Kapila @ 2026-07-06 03:48 UTC (permalink / raw) To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Fri, Jul 3, 2026 at 8:48 AM Peter Smith <[email protected]> wrote: > > On Thu, Jul 2, 2026 at 6:48 PM Amit Kapila <[email protected]> wrote: > > > > On Thu, Jul 2, 2026 at 8:29 AM Peter Smith <[email protected]> wrote: > > > > > > The docs do refer to "currently subscribed sequences" but do not > > > explicitly warn about this gotcha. > > > > > > PSA: a patch to add a clarifying sentence. > > > > > > > It does not add or remove > > sequences from the subscription to match the publication. > > </para> > > + <para> > > + If the publication's sequence membership has changed, > > + <link linkend="sql-altersubscription-params-refresh-publication"> > > + <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> > > + should be run before <literal>REFRESH SEQUENCES</literal> to ensure the > > + subscription's known sequence list is current. > > + </para> > > > > It is already mentioned that this command "does not add or remove > > sequences from the subscription to match the publication.". Adding > > more information, especially a separate para for the same, appears > > like bloating the text in docs. OTOH, I see your point of running > > REFRESH PUBLICATION before REFRESH SEQUENCES which I tried to cover in > > the existing paragraph. Apart from that, I have merged your > > doc-changes from another thread [1] which is on the same topic. Let's > > commit them together. > > > > +1 to combine both patches. > > Upon re-reading, I think there is still ambiguity due to that word > "synchronizing". > I don't read it that way. I feel the current wording is succinct and conveys what the command is supposed to do. I read your new version of the patch but I still like the one I shared previously, so I'll go-ahead with that unless someone else has preference for one or the other version. -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-07-06 03:48 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-07-02 02:58 DOCS - Clarify that REFRESH SEQUENCES might be using stale publication data Peter Smith <[email protected]> 2026-07-02 06:48 ` Amit Kapila <[email protected]> 2026-07-03 03:18 ` Peter Smith <[email protected]> 2026-07-06 03:48 ` Amit Kapila <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox