Re: logical decoding and replication of sequences, take 2

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.

> I don't think it is a good idea to make it mandatory: for example,
> there's a strong use case for replicating a table but not a sequence
> associated with it. But it's definitely a missing feature in
> logical replication.

I don't think the plan was to make replication of sequences mandatory,
certainly not with the built-in replication. If you don't add sequences
to the publication, the sequence changes will be skipped.

But it still needs to be part of the decoding, which adds overhead for
all logical decoding uses, even if the sequence changes end up being
discarded. That's somewhat annoying, especially considering sequences
are fairly common part of the WAL stream.

regards

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