Thread: Re: Allowing pg_recvlogical to create temporary replication slots

Re: Allowing pg_recvlogical to create temporary replication slots

From
Peter Eisentraut
Date:
On 27.10.24 13:37, Torsten Förtsch wrote:
> This is my very first message to this mailing list. Please advise if I 
> am making any mistakes in the procedure.

Welcome!

> The attached patch enables pg_recvlogical to create a temporary slot.
> 
> What is the next step in the process to get this change into official 
> postgres?

I think you should explain a bit why you want to do that, what use case 
or scenario this is meant to address.




Re: Allowing pg_recvlogical to create temporary replication slots

From
Torsten Förtsch
Date:
On Wed, Oct 30, 2024 at 9:01 AM Peter Eisentraut <peter@eisentraut.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?

Re: Allowing pg_recvlogical to create temporary replication slots

From
Amit Kapila
Date:
On Fri, Nov 1, 2024 at 6:13 PM Torsten Förtsch <tfoertsch123@gmail.com> wrote:
>
> On Wed, Oct 30, 2024 at 9:01 AM Peter Eisentraut <peter@eisentraut.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
gettingALL the changes. If the replication slot disappears and a few changes are lost it does not matter. So, a
temporaryrep 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
removethe slot. 
>

Your use case sounds reasonable to me. So, +1 for adding an option for
temporary slots.

--
With Regards,
Amit Kapila.



Re: Allowing pg_recvlogical to create temporary replication slots

From
Dickson Guedes
Date:
Hello Torsten!

While reviewing your patch for pg_recvlogical, I’m curious about the 
expected behavior when using --if-not-exists in conjunction with --temporary-slot. 

If an existing slot is reused under these options, will it behave as 
a temporary slot and be removed when the connection ends? Or the
existing slot should remain persistent despite the --temporary-slot flag?

--
Dickson S. Guedes

Re: Allowing pg_recvlogical to create temporary replication slots

From
Torsten Förtsch
Date:
Hi Dickson,

While reviewing your patch for pg_recvlogical, I’m curious about the
expected behavior when using --if-not-exists in conjunction with --temporary-slot.

If an existing slot is reused under these options, will it behave as
a temporary slot and be removed when the connection ends? Or the
existing slot should remain persistent despite the --temporary-slot flag?

 If the slot did not exist before, it is created as a temporary slot. If subsequently the connection drops and pg_recvlogical reconnects (no -n option), the slot is created again as a temporary slot. That means changes happening in between will not be captured. 

If the slot existed before, the existing slot will be used, no error is generated. In that case the slot will keep existing when the connection is dropped.

The following situation can happen:
- the slot does not exist and is created as a temporary slot
- the connection drops and pg_recvlogical tries to reconnect after a few seconds
- in that period a permanent slot by the same name is created but no reader is connected to it
- pg_recvlogical reconnects and finds the existing slot

In this case, the now existing slot is simply used. It will remain when the connection is dropped.

Thanks for reviewing.

Basically, --if-not-exists flag tells pg_recvlogical to ignore the error it would get when creating a slot with a name that's already there. The new flag does not change that.

This is the relevant code in src/bin/pg_basebackup/streamutil.c checking the result of the CREATE_REPLICATION_SLOT command.

if (slot_exists_ok &&
    sqlstate &&
    strcmp(sqlstate, ERRCODE_DUPLICATE_OBJECT) == 0)
{
    ...
    return true;
}

Re: Allowing pg_recvlogical to create temporary replication slots

From
"Dickson S. Guedes"
Date:
Em ter., 12 de nov. de 2024 às 07:54, Torsten Förtsch
<tfoertsch123@gmail.com> escreveu:
> Hi Dickson,
>
> [ ... ]
>
> If the slot existed before, the existing slot will be used, no error is generated. In that case the slot will keep
existingwhen the connection is dropped. 
>
> The following situation can happen:
> - the slot does not exist and is created as a temporary slot
> - the connection drops and pg_recvlogical tries to reconnect after a few seconds
> - in that period a permanent slot by the same name is created but no reader is connected to it
> - pg_recvlogical reconnects and finds the existing slot
>
> In this case, the now existing slot is simply used. It will remain when the connection is dropped.
>
> Thanks for reviewing.

Thank you for your clarification.

Are you planning to add changes in docs? Would be important to have
this clarification in there also, IMHO.

Regards.
--
Dickson S. Guedes