Re: logical decoding and replication of sequences

On 9/25/21 22:05, Hannu Krosing wrote:
> Just a note for some design decisions
> 
>> 1) By default, sequences are treated non-transactionally, i.e. sent to the output plugin right away.
> 
> 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)
> 

Not sure. TBH I feel rather uneasy about adding more stuff in COMMIT.

> This avoids sending redundant changes for multiple nextval() calls
> (like loading a million-row table with sequence-generated id column)
> 

Yeah, it'd be nice to have to optimize this a bit, somehow. But I'd bet 
it's a negligible amount of data / changes, compared to the table.

> And one can argue that we can safely ignore anything in ROLLBACKED
> sequences. This is assuming that even if we did advance the sequence
> paste the last value sent by the latest COMMITTED transaction it does
> not matter for database consistency.
> 

I don't think we can ignore aborted (ROLLBACK) transactions, in the 
sense that you can't just discard the increments. Imagine you have this 
sequence of transactions:

BEGIN;
SELECT nextval('s');  -- allocates new chunk of values
ROLLBACK;

BEGIN;
SELECT nextval('s'); -- returns one of the cached values
COMMIT;

If you ignore the aborted transaction, then the sequence increment won't 
be replicated -- but that's wrong, because user now has a visible 
sequence value from that chunk.

So I guess we'd have to maintain a cache of sequences incremented in the 
current session, do nothing in aborted transactions (i.e. keep the 
contents but don't log anything) and log/reset at commit.

I wonder if multiple sessions make this even more problematic (e.g. due 
to session just disconnecting mid transansaction, without writing the 
abort record at all). But AFAICS that's not an issue, because the other 
session has a separate cache for the sequence.

> It can matter if customers just call nextval() in rolled-back
> transactions and somehow expect these values to be replicated based on
> reasoning along "sequences are not transactional - so rollbacks should
> not matter" .
> 

I don't think we guarantee anything for data in transactions that did 
not commit, so this seems like a non-issue. I.e. we don't need to go out 
of our way to guarantee something we never promised.

> Or we may get away with most in-detail sequence tracking on the source
> if we just keep track of the xmin of the sequence and send the
> sequence info over at commit if it == current_transaction_id ?
> 

Not sure I understand this proposal. Can you explain?

regards

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