Re: Allowing pg_recvlogical to create temporary replication slots

On Wed, Oct 30, 2024 at 9:01 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:

> On 27.10.24 13:37, Torsten Förtsch wrote:
> > The attached patch enables pg_recvlogical to create a temporary slot.
>
> I think you should explain a bit why you want to do that, what use case
> or scenario this is meant to address.
>

In my particular case I want to filter for messages sent by
pg_logical_emit_message(). I don't care much about getting ALL the changes.
If the replication slot disappears and a few changes are lost it does not
matter. So, a temporary rep slot makes more sense than creating one and
then having to make sure it is not retaining wal forever later.

I can imagine this also as a tool to monitor changes for a while and then
simply disconnect without the need to remove the slot.

Why am I interested in pg_logical_emit_message()? We have an application
with relatively complicated transactions involving multiple tables. Some of
them use pg_notify(). We also have synchronous replication. Sometimes I see
lock avalanches that can be traced to the "AccessExclusiveLock on object 0
of class 1262 of database 0". This lock is taken during commit when the
notification is inserted in the queue. After that the backend waits for the
confirmation by the sync replica. So, this lock presses all the
transactions sending notifications into a sequence.

Now, if the application uses pg_logical_emit_message() instead, then I
think there is no equivalent lock. I understand the semantics are a bit
different (timing) but close enough for my use case. Another advantage of
pg_logical_emit_message() is the ability to send them even if the
transaction is aborted.

I was in the process of experimenting with this idea and found that
pg_recvlogical can:
- only create the slot or
- create the slot and immediately use it
- try to create the slot and if the slot is already there use it

So, why not also allow it to create a temporary slot?