Re: logical decoding and replication of sequences, take 2

On Wed, Jan 11, 2023 at 1:29 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> > I agree that it's fine for the sequence to be slightly ahead, but I
> > think that it can't be too far ahead without causing problems. Suppose
> > for example that transaction #1 creates a sequence. Transaction #2
> > does nextval on the sequence a bunch of times and inserts rows into a
> > table using the sequence values as the PK. It's fine if the nextval
> > operations are replicated ahead of the commit of transaction #2 -- in
> > fact I'd say it's necessary for correctness -- but they can't precede
> > the commit of transaction #1, since then the sequence won't exist yet.
>
> It's not clear to me how could that even happen. If transaction #1
> creates a sequence, it's invisible for transaction #2. So how could it
> do nextval() on it? #2 has to wait for #1 to commit before it can do
> anything on the sequence, which enforces the correct ordering, no?

Yeah, I meant if #1 had committed and then #2 started to do its thing.
I was worried that decoding might reach the nextval operations in
transaction #2 before it replayed #1.

This worry may be entirely based on me not understanding how this
actually works. Do we always apply a transaction as soon as we see the
commit record for it, before decoding any further?

--
Robert Haas
EDB: http://www.enterprisedb.com