> I would like to ask if you have some suggestions or ideas that can make subscriber receives the current value without the need to
>
> disabling the 32 increment behavior?
It simply shouldn't expect that to be the case. What do you need it
for?
As far as I can tell replicating sequence values is useful to allow
failover, by ensuring all sequences will return sensible values going
forward. That does not require to now precise values.
Totally agree. Code that relies on getting specific sequence values is broken code. Alas, very common, but still broken.
Cary, by way of background a large part of why this wasn't supported by logical decoding back in 9.4 is that until the pg_catalog.pg_sequence relation was introduced in PostgreSQL 10, the sequence relfilenode intermixed a bunch of transactional and non-transactional state in a very messy way. This made it very hard to achieve sensible behaviour for logical decoding.
As it is, make sure your regression tests carefully cover the following cases, as TAP tests in src/test/recovery, probably a new module for logical decoding of sequences:
1.
* Begin txn
* Create sequence
* Call nextval() on sequence over generate_series() and discard results
* Rollback
* Issue a dummy insert+commit to some other table to force logical decoding to send something
* Ensure subscription catches up successfully
This checks that we cope with advances for a sequence that doesn't get created.
2.
* Begin 1st txn
* Create a sequence
* Use the sequence to populate a temp table with enough rows to ensure sequence updates are written
* Begin a 2nd txn
* Issue a dummy insert+commit to some other table to force logical decoding to send something
* Commit the 2nd txn
* Commit the 1st txn
* Wait for subscription catchup
* Check that the sequence value on the subscriber reflects the value after sequence advance, not the value at creation time
This makes sure that sequence advances are handled sensibly when they arrive for a sequence that does not yet exist in the catalogs.
You'll need to run psql in an IPC::Run background session for that. We should really have a helper for this. I'll see if I'm allowed to post the one I use for my own TAP tests to the list.
