Re: logical decoding and replication of sequences, take 2

On 7/29/23 14:38, Tomas Vondra wrote:
>
> ...
>
> The only idea how to improve that is we could keep the non-transactional
> changes (instead of applying them immediately), and then apply them on
> the nearest "commit". That'd mean it's subject to the position tracking,
> and the sequence would not go backwards, I think.
>
> So every time we decode a commit, we'd check if we decoded any sequence
> changes since the last commit, and merge them (a bit like a subxact).
>
> This would however also mean sequence changes from rolled-back xacts may
> not be replictated. I think that'd be fine, but IIRC Andres suggested
> it's a valid use case.
>

I wasn't sure how difficult would this approach be, so I experimented
with this today, and it's waaaay more complicated than I thought. In
fact, I'm not even sure how to do that ...

The part 0008 is an WIP patch where ReorderBufferQueueSequence does not
apply the non-transactional changes immediately, and instead adds the
changes to a top-level list. And then ReorderBufferCommit adds a fake
subxact with all sequence changes up to the commit LSN.

The challenging part is snapshot management - when applying the changes
immediately, we can simply build and use the current snapshot. But with
0008 it's not that simple - we don't even know into which transaction
will the sequence change get "injected". In fact, we don't even know if
the parent transaction will have a snapshot (if it only does nextval()
it may seem empty). I was thinking maybe we could "keep" the snapshots
for non-transactional changes, but I suspect it might confuse the main
transaction in some way.

I'm still not convinced this behavior would actually be desirable ...

regards

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