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

On Thu, Jun 9, 2022 at 1:01 PM Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)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