Re: logical decoding and replication of sequences, take 2

On Thu, Dec 21, 2023 at 6:47 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> On 12/19/23 13:54, Christophe Pettus wrote:
> > Hi,
> >
> > I wanted to hop in here on one particular issue:
> >
> >> On Dec 12, 2023, at 02:01, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> >> - desirability of the feature: Random IDs (UUIDs etc.) are likely a much
> >> better solution for distributed (esp. active-active) systems. But there
> >> are important use cases that are likely to keep using regular sequences
> >> (online upgrades of single-node instances, existing systems, ...).
> >
> > +1.
> >
> > Right now, the lack of sequence replication is a rather large
> > foot-gun on logical replication upgrades. Copying the sequences
> > over during the cutover period is doable, of course, but:
> >
> > (a) There's no out-of-the-box tooling that does it, so everyone has
> > to write some scripts just for that one function.
> >
> > (b) It's one more thing that extends the cutover window.
> >
>
> I agree it's an annoying gap for this use case. But if this is the only
> use cases, maybe a better solution would be to provide such tooling
> instead of adding it to the logical decoding?
>
> It might seem a bit strange if most data is copied by replication
> directly, while sequences need special handling, ofc.
>

One difference between the logical replication of tables and sequences
is that we can guarantee with synchronous_commit (and
synchronous_standby_names) that after failover transactions data is
replicated or not whereas for sequences we can't guarantee that
because of their non-transactional nature. Say, there are two
transactions T1 and T2, it is possible that T1's entire table data and
sequence data are committed and replicated but T2's sequence data is
replicated. So, after failover to logical subscriber in such a case if
one routes T2 again to the new node as it was not successful
previously then it would needlessly perform the sequence changes
again. I don't how much that matters but that would probably be the
difference between the replication of tables and sequences.

I agree with your point above that for upgrades some tool like
pg_copysequence where we can provide a way to copy sequence data to
subscribers from the publisher would suffice the need.

--
With Regards,
Amit Kapila.