MIME-Version: 1.0
From: Peter Smith <smithpb2250@gmail.com>
Date: Thu, 2 Jul 2026 14:58:48 +1200
Message-ID: 
 <CAHut+PtFkGvZNihGRDoghWNKMfJufEpR9+thbG_8qPQ7RyVN4w@mail.gmail.com>
Subject: DOCS - Clarify that REFRESH SEQUENCES might be using stale
 publication data
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000040b294065597fe0d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPtFkGvZNihGRDoghWNKMfJufEpR9%2BthbG_8qPQ7RyVN4w%40mail.gmail.com>
Precedence: bulk

--00000000000040b294065597fe0d
Content-Type: text/plain; charset="UTF-8"

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

--00000000000040b294065597fe0d
Content-Type: application/octet-stream;
	name="v1-0001-DOCS-clarify-that-REFRESH-SEQUENCES-might-be-usin.patch"
Content-Disposition: attachment;
	filename="v1-0001-DOCS-clarify-that-REFRESH-SEQUENCES-might-be-usin.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mr2wwa2k0>
X-Attachment-Id: f_mr2wwa2k0

RnJvbSBhZmE3ZWJhNDAwYjU5M2UyYTczYTVmZDYxOGU1MTBjZWY1NzFhZWU3IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBTbWl0aCA8cGV0ZXIuYi5zbWl0aEBmdWppdHN1LmNv
bT4KRGF0ZTogVGh1LCAyIEp1bCAyMDI2IDE0OjQ4OjQzICsxMjAwClN1YmplY3Q6IFtQQVRDSCB2
MV0gRE9DUyAtIGNsYXJpZnkgdGhhdCBSRUZSRVNIIFNFUVVFTkNFUyBtaWdodCBiZSB1c2luZyBz
dGFsZQogcHVibGljYXRpb24gZGF0YQoKLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL2FsdGVyX3N1YnNj
cmlwdGlvbi5zZ21sIHwgMTAgKysrKysrKystLQogMSBmaWxlIGNoYW5nZWQsIDggaW5zZXJ0aW9u
cygrKSwgMiBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvcmVmL2FsdGVy
X3N1YnNjcmlwdGlvbi5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9hbHRlcl9zdWJzY3JpcHRpb24u
c2dtbAppbmRleCBlNGYwYjZiMTZjNy4uYzllZDc3NDU1NTQgMTAwNjQ0Ci0tLSBhL2RvYy9zcmMv
c2dtbC9yZWYvYWx0ZXJfc3Vic2NyaXB0aW9uLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3JlZi9h
bHRlcl9zdWJzY3JpcHRpb24uc2dtbApAQCAtMjM2LDEzICsyMzYsMTkgQEAgQUxURVIgU1VCU0NS
SVBUSU9OIDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVyIj5uYW1lPC9yZXBsYWNlYWJsZT4g
UkVOQU1FIFRPIDwKICAgICA8bGlzdGl0ZW0+CiAgICAgIDxwYXJhPgogICAgICAgUmUtc3luY2hy
b25pemUgc2VxdWVuY2UgZGF0YSB3aXRoIHRoZSBwdWJsaXNoZXIuIFVubGlrZQotICAgICAgPGxp
bmsgbGlua2VuZD0ic3FsLWFsdGVyc3Vic2NyaXB0aW9uLXBhcmFtcy1yZWZyZXNoLXB1YmxpY2F0
aW9uIj4KLSAgICAgIDxjb21tYW5kPkFMVEVSIFNVQlNDUklQVElPTiAuLi4gUkVGUkVTSCBQVUJM
SUNBVElPTjwvY29tbWFuZD48L2xpbms+IHdoaWNoCisgICAgICA8bGl0ZXJhbD5SRUZSRVNIIFBV
QkxJQ0FUSU9OPC9saXRlcmFsPiB3aGljaAogICAgICAgb25seSBoYXMgdGhlIGFiaWxpdHkgdG8g
c3luY2hyb25pemUgbmV3bHkgYWRkZWQgc2VxdWVuY2VzLAogICAgICAgPGxpdGVyYWw+UkVGUkVT
SCBTRVFVRU5DRVM8L2xpdGVyYWw+IHdpbGwgcmUtc3luY2hyb25pemUgdGhlIHNlcXVlbmNlCiAg
ICAgICBkYXRhIGZvciBhbGwgY3VycmVudGx5IHN1YnNjcmliZWQgc2VxdWVuY2VzLiBJdCBkb2Vz
IG5vdCBhZGQgb3IgcmVtb3ZlCiAgICAgICBzZXF1ZW5jZXMgZnJvbSB0aGUgc3Vic2NyaXB0aW9u
IHRvIG1hdGNoIHRoZSBwdWJsaWNhdGlvbi4KICAgICAgPC9wYXJhPgorICAgICA8cGFyYT4KKyAg
ICAgIElmIHRoZSBwdWJsaWNhdGlvbidzIHNlcXVlbmNlIG1lbWJlcnNoaXAgaGFzIGNoYW5nZWQs
CisgICAgICA8bGluayBsaW5rZW5kPSJzcWwtYWx0ZXJzdWJzY3JpcHRpb24tcGFyYW1zLXJlZnJl
c2gtcHVibGljYXRpb24iPgorICAgICAgPGNvbW1hbmQ+QUxURVIgU1VCU0NSSVBUSU9OIC4uLiBS
RUZSRVNIIFBVQkxJQ0FUSU9OPC9jb21tYW5kPjwvbGluaz4KKyAgICAgIHNob3VsZCBiZSBydW4g
YmVmb3JlIDxsaXRlcmFsPlJFRlJFU0ggU0VRVUVOQ0VTPC9saXRlcmFsPiB0byBlbnN1cmUgdGhl
CisgICAgICBzdWJzY3JpcHRpb24ncyBrbm93biBzZXF1ZW5jZSBsaXN0IGlzIGN1cnJlbnQuCisg
ICAgIDwvcGFyYT4KICAgICAgPHBhcmE+CiAgICAgICBTZWUgPHhyZWYgbGlua2VuZD0ic2VxdWVu
Y2UtZGVmaW5pdGlvbi1taXNtYXRjaGVzIi8+IGZvcgogICAgICAgcmVjb21tZW5kYXRpb25zIG9u
IGhvdyB0byBoYW5kbGUgYW55IHdhcm5pbmdzIGFib3V0IHNlcXVlbmNlIGRlZmluaXRpb24KLS0g
CjIuNDcuMwoK
--00000000000040b294065597fe0d--