Re: logical decoding and replication of sequences

On 11/23/21 02:01, Andres Freund wrote:
> Hi,
>
> On 2021-09-25 22:05:43 +0200, Hannu Krosing wrote:
>> If our aim is just to make sure that all user-visible data in
>> *transactional* tables is consistent with sequence state then one
>> very much simplified approach to this could be to track the results of
>> nextval() calls in a transaction at COMMIT put the latest sequence
>> value in WAL (or just track the sequences affected and put the latest
>> sequence state in WAL at commit which needs extra read of sequence but
>> protects against race conditions with parallel transactions which get
>> rolled back later)
>
> I think this is a bad idea. It's architecturally more complicated and prevents
> use cases because sequence values aren't guaranteed to be as new as on the
> original system. You'd need to track all sequence use somehow *even if there
> is no relevant WAL generated* in a transaction. There's simply no evidence of
> sequence use in a transaction if that transaction uses a previously logged
> sequence value.
>

Not quite. We already have a cache of all sequences used by a session
(see seqhashtab in sequence.c), and it's not that hard to extend it to
per-transaction tracking. That's what the last two versions do, mostly.

But there are various issues with that approach, described in my last
message(s).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company