Re: logical decoding and replication of sequences, take 2

On Wed, Feb 14, 2024 at 10:21 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> On 2/13/24 17:37, Robert Haas wrote:
>
> > In other words, the fact that some sequence changes are
> > non-transactional creates ordering hazards that don't exist if there
> > are no non-transactional changes. So in that way, sequences are
> > different from table modifications, where applying the transactions in
> > order of commit is all we need to do. Here we need to apply the
> > transactions in order of commit and also apply the non-transactional
> > changes at the right point in the sequence. Consider the following
> > alternative apply sequence:
> >
> > 1. T1.
> > 2. T2's transactional changes (i.e. the ALTER SEQUENCE INCREMENT and
> > the subsequent nextval)
> > 3. T3's nextval
> > 4. T2's first nextval
> >
> > That's still in commit order. It's also wrong.
> >
>
> Yes, this would be wrong. Thankfully the apply is not allowed to reorder
> the changes like this, because that's not what "non-transactional" means
> in this context.
>
> It does not mean we can arbitrarily reorder the changes, it only means
> the changes are applied as if they were independent transactions (but in
> the same order as they were executed originally).
>

In this regard, I have another scenario in mind where the apply order
could be different for the changes in the same transactions. For
example,

Transaction T1
Begin;
Insert ..
Insert ..
nextval .. --consider this generates WAL
..
Insert ..
nextval .. --consider this generates WAL

In this case, if the nextval operations will be applied in a different
order (aka before Inserts) then there could be some inconsistency.
Say, if, it doesn't follow the above order during apply then a trigger
fired on both pub and sub for each row insert that refers to the
current sequence value to make some decision could have different
behavior on publisher and subscriber. If this is not how the patch
will behave then fine but otherwise, isn't this something that we
should be worried about?

--
With Regards,
Amit Kapila.