Horton

Re: logical decoding and replication of sequences

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Andres Freund <andres@anarazel.de>, Hannu Krosing <hannuk@google.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>, vignesh C <vignesh21@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Petr Jelinek <petr.jelinek@enterprisedb.com>
Date: 2021-11-23T13:36:17Z
Lists: pgsql-hackers 

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

Commits

  1. Fix cache invalidation bug in recovery_prefetch.

  2. Revert "Logical decoding of sequences"

  3. Minor improvements in sequence decoding code and docs

  4. Handle sequences in preprocess_pubobj_list

  5. Update tab-completion for CREATE PUBLICATION with sequences

  6. Add decoding of sequences to built-in replication

  7. Stabilize test_decoding touching with sequences

  8. Call ReorderBufferProcessXid from sequence_decode

  9. Add decoding of sequences to test_decoding

  10. Replace Test::More plans with done_testing

  11. Logical decoding of sequences

  12. Respect permissions within logical replication.