Horton

Re: logical decoding and replication of sequences

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Hannu Krosing <hannuk@google.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, 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-23T01:01:14Z
Lists: pgsql-hackers 
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.

Greetings,

Andres Freund

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.