Thread: A proposal to provide a timeout option for CREATE_REPLICATION_SLOT/pg_create_logical_replication_slot

At Thu, 9 Jun 2022 10:25:06 +0530, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote in 
> Hi,
> 
> Currently CREATE_REPLICATION_SLOT/pg_create_logical_replication_slot waits
> unboundedly if there are any in-progress write transactions [1]. The wait
> is for a reason actually i.e. for building an initial snapshot, but waiting
> unboundedly isn't good for usability of the command/function and when
> stuck, the callers will not have any information as to why.
> 
> How about we provide a timeout for the command/function instead of letting
> them wait unboundedly? The behavior will be something like this - if the
> logical replication slot isn't created within this timeout, the
> command/function will fail.
> 
> We could've asked callers to set statement_timeout before calling
> CREATE_REPLICATION_SLOT/pg_create_logical_replication_slot but that impacts
> the queries running in all other sessions and it may not be always possible
> to set this parameter just for the session that runs command
> CREATE_REPLICATION_SLOT.
>
> Thoughts?

How can the other sessions get affected by setting statement_timeout a
session?  And "SET LOCAL" narrows the effect down to within a
transaction. I think that is sufficient. On the other hand,
CREATE_REPLICATION_SLOT doesn't honor statement_timeout, but honors
lock_timeout. (It's a bit strange but I would hardly go so far as to
say we should "fix" it..) If a program issues CREATE_REPLICATION_SLOT,
it's hard to believe that the same program cannot issue SET (for
lock_timeout) command as well.

When CREATE_REPLICATION_SLOT is called from a CREATE SUBSCRIPTION
command, the latter command itself honors statement_timeout and
disconnects the peer walsender. Thus, client_connection_check_interval
set on publisher side kills the walsender shortly after the
disconnection.

In short, I don't see much point in the timeout of the function/command.

As a general discussion on the timeout of functions/commands by a
parameter, I can only come up with pg_terminate_backend() for now, but
its timeout parameter not only determines timeout seconds but also
specifies whether the function waits for the process termination. That
functionality cannot be achieved by statement timeout.  In that sense
it is a bit apart from pg_logical_replication_slot().

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

On Thu, Jun 9, 2022 at 1:01 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> > Currently CREATE_REPLICATION_SLOT/pg_create_logical_replication_slot waits
> > unboundedly if there are any in-progress write transactions [1]. [....]
> >
> > How about we provide a timeout for the command/function instead of letting
> > them wait unboundedly?
>
> How can the other sessions get affected by setting statement_timeout a
> session?  And "SET LOCAL" narrows the effect down to within a
> transaction. I think that is sufficient.

SET LOCAL needs to be run within an explicit txn whereas CREATE
SUBSCRIPTION can't.

> On the other hand,
> CREATE_REPLICATION_SLOT doesn't honor statement_timeout, but honors
> lock_timeout. (It's a bit strange but I would hardly go so far as to
> say we should "fix" it..) If a program issues CREATE_REPLICATION_SLOT,
> it's hard to believe that the same program cannot issue SET (for
> lock_timeout) command as well.

Yes it can issue lock_timeout.

> When CREATE_REPLICATION_SLOT is called from a CREATE SUBSCRIPTION
> command, the latter command itself honors statement_timeout and
> disconnects the peer walsender. Thus, client_connection_check_interval
> set on publisher side kills the walsender shortly after the
> disconnection.

Right.

> In short, I don't see much point in the timeout of the function/command.

I played with it a bit today. There are a couple of ways to get around
the CREATE SUBSCRIPTION blocking issue - set statement_timeout [1] or
transaction_timeout [2] on the subscriber at the session level before
creating the subscription, or set lock_timeout [3] on the publisher.

Since we have a bunch of timeouts already (transaction_timeout being
the latest addition), I don't think we need another one here. So I
withdraw my initial idea on this thread to have a separate timeout to
create a logical replication slot.

[1]
postgres=# SET transaction_timeout = '10s';
SET
postgres=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres
port=5432' PUBLICATION mypub;
FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

[2]
postgres=# SET statement_timeout = '10s';
SET
postgres=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres
port=5432' PUBLICATION mypub;

ERROR:  canceling statement due to statement timeout

[3]
postgres=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres
port=5432' PUBLICATION mypub;

ERROR:  could not create replication slot "mysub": ERROR:  canceling
statement due to lock timeout

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com