public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
To: kyzevan23@mail.ru
To: pgsql-bugs@lists.postgresql.org
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Subject: Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always
Date: Wed, 20 May 2026 10:07:15 +0530
Message-ID: <CAJTYsWUQBwCOQ=uGnLP7C2ofN6TqUpGeOHan2N99mza1DjybsQ@mail.gmail.com> (raw)
In-Reply-To: <19488-d7ccfca2bf6b74b0@postgresql.org>
References: <19488-d7ccfca2bf6b74b0@postgresql.org>
Hi,
On Wed, 20 May 2026 at 02:34, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19488
> Logged by: Egor Chindyaskin
> Email address: kyzevan23@mail.ru
> PostgreSQL version: 18.4
> Operating system: Ubuntu 26.04
> Description:
>
> Hello!
> In a master + physical standby setup, connection to the standby fails after
> creating a login event trigger on the master, enabling it as always, and
> then dropping it without reconnecting to the master.
> Also reproduces on master branch.
> Steps to reproduce:
>
> 1. Run the following SQL script on the master:
> CREATE OR REPLACE FUNCTION init_session()
> RETURNS event_trigger SECURITY DEFINER
> LANGUAGE plpgsql AS
> $$
> BEGIN
> RAISE NOTICE 'init_session';
> END;
> $$;
>
> CREATE EVENT TRIGGER init_session
> ON login
> EXECUTE FUNCTION init_session();
>
> ALTER EVENT TRIGGER init_session ENABLE ALWAYS;
>
> DROP EVENT TRIGGER init_session;
>
> 2. Try to connect to the standby:
> psql -p5433
>
> Result:
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5433" failed:
> FATAL: cannot acquire lock mode AccessExclusiveLock on database objects
> while recovery is in progress
> HINT: Only RowExclusiveLock or less can be acquired on database objects
> during recovery.
>
> --
> With best regards,
> Egor Chindyaskin
>
> Postgres Professional: https://postgrespro.com
Thanks for the report and the precise repro.
The cause is in EventTriggerOnLogin(). When a session connects to a
database whose pg_database.dathasloginevt flag is set but no login
event triggers are actually present, the function tries to clear the
flag via:
ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId, 0,
AccessExclusiveLock);
On a hot standby, LockAcquireExtended() refuses any lock stronger than
RowExclusiveLock on LOCKTAG_OBJECT/LOCKTAG_RELATION while
RecoveryInProgress() is true, which surfaces as a FATAL on the new
connection. The standby ends up in this state precisely after your
steps because the primary set dathasloginevt = true while the trigger
was active, then dropped the trigger but (intentionally) left the
flag set on disk for the next normal connection to clean up. That
state replicates to the standby, and any subsequent connection on the
standby then enters the cleanup path and crashes.
A standby should not try to clear that flag itself(?) the only correct
update path on a standby is WAL replay from the primary.
Attached patch adds a RecoveryInProgress() check to skip the cleanup
branch on standbys. I think this needs to be backpatched too.
Regards,
Ayush
Attachments:
[application/octet-stream] v1-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch (2.8K, 3-v1-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch)
download | inline diff:
From 872acc7d8dc3c4efdabc2ce32ef5556c46440e4d Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Wed, 20 May 2026 03:33:46 +0000
Subject: [PATCH v1] Skip pg_database.dathasloginevt cleanup on standby
EventTriggerOnLogin() tries to clear pg_database.dathasloginevt when
the database no longer has any login event triggers but the flag is
still set. To make that safe against concurrent flag setters, it
takes a conditional AccessExclusiveLock on the database object.
On a hot standby, that lock acquisition fails outright with
FATAL: cannot acquire lock mode AccessExclusiveLock on database
objects while recovery is in progress
because LockAcquireExtended() refuses locks stronger than
RowExclusiveLock on database objects during recovery. The standby
already replays the flag's value from the primary, so the dangling
flag is the result of replaying a state in which the primary had
already dropped its login event triggers but not yet run a login event
trigger pass to clear the flag. Any session connecting to the standby
in that window therefore fails to connect.
Skip the cleanup on a standby. The flag will be cleared via WAL
replay once the primary clears it on its side, which is the only
correct way to update a catalog from a standby.
Reported-by: Egor Chindyaskin <kyzevan23@mail.ru>
Discussion: https://postgr.es/m/19488-...@postgresql.org
---
src/backend/commands/event_trigger.c | 11 ++++++++++-
1 file changed, 10 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index dcd2f5a09bb..0680421268d 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -18,6 +18,7 @@
#include "access/htup_details.h"
#include "access/table.h"
#include "access/xact.h"
+#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
@@ -937,8 +938,16 @@ EventTriggerOnLogin(void)
* lock to prevent concurrent SetDatabaseHasLoginEventTriggers(), but we
* don't want to hang the connection waiting on the lock. Thus, we are
* just trying to acquire the lock conditionally.
+ *
+ * Skip this on a hot standby: the conditional AccessExclusiveLock on the
+ * database object would fail with "cannot acquire lock mode ... while
+ * recovery is in progress", which the caller would surface as a FATAL
+ * connection error. On a standby we cannot (and must not) clear the
+ * pg_database flag ourselves; it will be cleared via WAL replay once the
+ * primary's next login event trigger run clears it on the primary.
*/
- else if (ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId,
+ else if (!RecoveryInProgress() &&
+ ConditionalLockSharedObject(DatabaseRelationId, MyDatabaseId,
0, AccessExclusiveLock))
{
/*
--
2.43.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ayushtiwari.slg01@gmail.com, kyzevan23@mail.ru, pgsql-bugs@lists.postgresql.org, aekorotkov@gmail.com
Subject: Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always
In-Reply-To: <CAJTYsWUQBwCOQ=uGnLP7C2ofN6TqUpGeOHan2N99mza1DjybsQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox