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
v1-0001-DOCS-clarify-that-REFRESH-SEQUENCES-might-be-usin.patch
Description: Binary data
