On Wed, Feb 9, 2022, at 12:06 PM, Ashutosh Sharma wrote:
Just wondering if we should also be detecting the incorrect conninfo
set with ALTER SUBSCRIPTION command as well. See below:
-- try creating a subscription with incorrect conninfo. the command fails.
postgres=# create subscription sub1 connection 'host=localhost
port=5490 dbname=postgres' publication pub1;
ERROR: could not connect to the publisher: connection to server at
"localhost" (::1), port 5490 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5490 failed:
Connection refused
Is the server running on that host and accepting TCP/IP connections?
That's because by default 'connect' parameter is true.
The important routine for all SUBSCRIPTION commands that handle connection
string is to validate the connection string e.g. check if all parameters are
correct. See walrcv_check_conninfo that calls PQconninfoParse.
The connection string is syntactically correct. Hence, no error. It could be
the case that the service is temporarily down. It is a useful and common
scenario that I wouldn't want to be forbid.
-- reset the connninfo in the subscription to some wrong value. the
command succeeds.
postgres=# alter subscription sub1 connection 'host=localhost
port=5490 dbname=postgres';
ALTER SUBSCRIPTION
postgres=#
postgres=# drop subscription sub1;
ERROR: could not connect to publisher when attempting to drop
replication slot "sub1": connection to server at "localhost" (::1),
port 5490 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5490 failed:
Connection refused
Is the server running on that host and accepting TCP/IP connections?
HINT: Use ALTER SUBSCRIPTION ... SET (slot_name = NONE) to
disassociate the subscription from the slot.
Again, dropping a subscription that is associated with a replication slot
requires a connection to remove the replication slot. If the publisher is gone
(and so the replication slot), follow the HINT advice.