public inbox for [email protected]help / color / mirror / Atom feed
BUG #19488: Standby connection fails after dropping on login event trigger enabled always 10+ messages / 4 participants [nested] [flat]
* BUG #19488: Standby connection fails after dropping on login event trigger enabled always @ 2026-05-19 11:06 PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: PG Bug reporting form @ 2026-05-19 11:06 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] The following bug has been logged on the website: Bug reference: 19488 Logged by: Egor Chindyaskin Email address: [email protected] 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> @ 2026-05-20 04:37 ` Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Ayush Tiwari @ 2026-05-20 04:37 UTC (permalink / raw) To: [email protected]; [email protected]; +Cc: Alexander Korotkov <[email protected]> Hi, On Wed, 20 May 2026 at 02:34, PG Bug reporting form <[email protected]> wrote: > The following bug has been logged on the website: > > Bug reference: 19488 > Logged by: Egor Chindyaskin > Email address: [email protected] > 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 <[email protected]> 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 <[email protected]> Discussion: https://postgr.es/m/[email protected] --- 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> @ 2026-05-20 10:03 ` Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Fujii Masao @ 2026-05-20 10:03 UTC (permalink / raw) To: Ayush Tiwari <[email protected]>; +Cc: [email protected]; [email protected]; Alexander Korotkov <[email protected]> On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari <[email protected]> wrote: > Thanks for the report and the precise repro. +1 > Attached patch adds a RecoveryInProgress() check to skip the cleanup > branch on standbys. Thanks for investigating this issue and for the patch! The patch looks good to me. > I think this needs to be backpatched too. Yes. Seems this should be backpatched to v17, where login event triggers were introduced. Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> @ 2026-05-20 11:31 ` Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Alexander Korotkov @ 2026-05-20 11:31 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; [email protected]; [email protected] On Wed, May 20, 2026 at 1:03 PM Fujii Masao <[email protected]> wrote: > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari > <[email protected]> wrote: > > Thanks for the report and the precise repro. > > +1 > > > Attached patch adds a RecoveryInProgress() check to skip the cleanup > > branch on standbys. > > Thanks for investigating this issue and for the patch! > The patch looks good to me. > > > I think this needs to be backpatched too. > > Yes. Seems this should be backpatched to v17, where login event triggers > were introduced. I've added a tap test reproducing the bug. I'm going to push and backpatch this to v17 if no objections. ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] v2-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch (7.1K, 2-v2-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch) download | inline diff: From 7ba4d92d461bb7e5d7cd8c0a9b0b4eebeb30f5f1 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov <[email protected]> Date: Wed, 20 May 2026 14:20:52 +0300 Subject: [PATCH v2] 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. Add a recovery TAP test that reproduces the original report: create and drop a login event trigger on the primary in one session, wait for the standby to replay, then verify that a fresh connection to the standby succeeds. Backpatch to v17, where the login event triggers were introduced. Author: Ayush Tiwari <[email protected]> Reported-by: Egor Chindyaskin <[email protected]> Reviewed-by: Fujii Masao <[email protected]> Reviewed-by: Alexander Korotkov <[email protected]> Discussion: https://postgr.es/m/19488-d7ccfca2bf6b74b0%40postgresql.org Backpatch-through: 17 --- src/backend/commands/event_trigger.c | 10 ++- src/test/recovery/meson.build | 1 + .../t/053_standby_login_event_trigger.pl | 78 +++++++++++++++++++ 3 files changed, 88 insertions(+), 1 deletion(-) create mode 100644 src/test/recovery/t/053_standby_login_event_trigger.pl diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index dcd2f5a09bb..adc6eabc0f4 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -937,8 +937,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)) { /* diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index 36d789720a3..9eb8ed11425 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -61,6 +61,7 @@ tests += { 't/050_redo_segment_missing.pl', 't/051_effective_wal_level.pl', 't/052_checkpoint_segment_missing.pl', + 't/053_standby_login_event_trigger.pl', ], }, } diff --git a/src/test/recovery/t/053_standby_login_event_trigger.pl b/src/test/recovery/t/053_standby_login_event_trigger.pl new file mode 100644 index 00000000000..ebd8cc278d3 --- /dev/null +++ b/src/test/recovery/t/053_standby_login_event_trigger.pl @@ -0,0 +1,78 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Verify that connecting to a standby still works after a login event +# trigger has been created and dropped on the primary. +# +# CREATE EVENT TRIGGER ... ON login sets pg_database.dathasloginevt to +# true on the primary, but DROP EVENT TRIGGER does not clear it -- the +# next login event trigger pass clears the flag lazily on the primary. +# That dangling flag replicates to the standby. Before the +# RecoveryInProgress() guard in EventTriggerOnLogin(), the standby +# tried to clear the flag itself, which requires AccessExclusiveLock +# on the database object; that lock mode is forbidden during recovery, +# so the new connection died with FATAL. + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Set up primary and a streaming standby. +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(allows_streaming => 1); +$primary->start; + +my $backup_name = 'login_evt_backup'; +$primary->backup($backup_name); + +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($primary, $backup_name, has_streaming => 1); +$standby->start; + +# Sanity check: a fresh standby with dathasloginevt = false accepts +# connections without exercising the cleanup branch. +$standby->safe_psql('postgres', 'SELECT 1'); + +# Create and drop a login event trigger on the primary in a single +# session. CREATE EVENT TRIGGER sets pg_database.dathasloginevt = +# true; mark it ENABLE ALWAYS so the scenario matches the original +# bug report. After DROP the flag remains set on disk until a +# subsequent login on the primary clears it -- so we must not open +# another connection to the primary that would clear it before the +# WAL containing dathasloginevt = true is shipped to the standby. +$primary->safe_psql( + 'postgres', q{ +CREATE FUNCTION init_session() RETURNS event_trigger +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; +DROP FUNCTION init_session(); +}); + +# Wait for the standby to replay the CREATE/DROP catalog state. At +# this point the standby's pg_database.dathasloginevt is still true. +$primary->wait_for_replay_catchup($standby); + +# A new connection to the standby exercises EventTriggerOnLogin()'s +# cleanup branch. With the RecoveryInProgress() guard, that branch is +# skipped on the standby and the connection succeeds. Without it the +# session aborts with a FATAL about AccessExclusiveLock. Probing the +# flag itself via safe_psql is what triggers the cleanup path. +is( $standby->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), + 't', + 'standby accepts connection and reports dangling dathasloginevt'); + +# Repeat with a non-die psql to make sure no FATAL is emitted. +my ($ret, $stdout, $stderr) = $standby->psql('postgres', 'SELECT 1'); +is($ret, 0, 'standby accepts second connection with dangling dathasloginevt'); +unlike( + $stderr, + qr/cannot acquire lock mode AccessExclusiveLock/, + 'no AccessExclusiveLock FATAL on standby login'); + +done_testing(); -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> @ 2026-05-20 12:05 ` Fujii Masao <[email protected]> 2026-05-20 12:45 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Fujii Masao @ 2026-05-20 12:05 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; [email protected]; [email protected] On Wed, May 20, 2026 at 8:31 PM Alexander Korotkov <[email protected]> wrote: > > On Wed, May 20, 2026 at 1:03 PM Fujii Masao <[email protected]> wrote: > > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari > > <[email protected]> wrote: > > > Thanks for the report and the precise repro. > > > > +1 > > > > > Attached patch adds a RecoveryInProgress() check to skip the cleanup > > > branch on standbys. > > > > Thanks for investigating this issue and for the patch! > > The patch looks good to me. > > > > > I think this needs to be backpatched too. > > > > Yes. Seems this should be backpatched to v17, where login event triggers > > were introduced. > > I've added a tap test reproducing the bug. I'm going to push and > backpatch this to v17 if no objections. +# Wait for the standby to replay the CREATE/DROP catalog state. At +# this point the standby's pg_database.dathasloginevt is still true. +$primary->wait_for_replay_catchup($standby); + +# A new connection to the standby exercises EventTriggerOnLogin()'s +# cleanup branch. With the RecoveryInProgress() guard, that branch is +# skipped on the standby and the connection succeeds. Without it the +# session aborts with a FATAL about AccessExclusiveLock. Probing the +# flag itself via safe_psql is what triggers the cleanup path. +is( $standby->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), + 't', + 'standby accepts connection and reports dangling dathasloginevt'); The test looks unstable to me. wait_for_replay_catchup() may connect to the primary to obtain the flush LSN, which could cause dathasloginevt to become false before the subsequent safe_psql() call on the standby. Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> @ 2026-05-20 12:45 ` Ayush Tiwari <[email protected]> 2026-05-21 08:10 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Ayush Tiwari @ 2026-05-20 12:45 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; [email protected]; [email protected] Hi, On Wed, 20 May 2026 at 17:35, Fujii Masao <[email protected]> wrote: > On Wed, May 20, 2026 at 8:31 PM Alexander Korotkov <[email protected]> > wrote: > > > > On Wed, May 20, 2026 at 1:03 PM Fujii Masao <[email protected]> > wrote: > > > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari > > > <[email protected]> wrote: > > > > Thanks for the report and the precise repro. > > > > > > +1 > > > > > > > Attached patch adds a RecoveryInProgress() check to skip the cleanup > > > > branch on standbys. > > > > > > Thanks for investigating this issue and for the patch! > > > The patch looks good to me. > > > > > > > I think this needs to be backpatched too. > > > > > > Yes. Seems this should be backpatched to v17, where login event > triggers > > > were introduced. > > > > I've added a tap test reproducing the bug. I'm going to push and > > backpatch this to v17 if no objections. > > +# Wait for the standby to replay the CREATE/DROP catalog state. At > +# this point the standby's pg_database.dathasloginevt is still true. > +$primary->wait_for_replay_catchup($standby); > + > +# A new connection to the standby exercises EventTriggerOnLogin()'s > +# cleanup branch. With the RecoveryInProgress() guard, that branch is > +# skipped on the standby and the connection succeeds. Without it the > +# session aborts with a FATAL about AccessExclusiveLock. Probing the > +# flag itself via safe_psql is what triggers the cleanup path. > +is( $standby->safe_psql( > + 'postgres', > + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), > + 't', > + 'standby accepts connection and reports dangling dathasloginevt'); > > The test looks unstable to me. wait_for_replay_catchup() may connect to > the primary to obtain the flush LSN, which could cause dathasloginevt to > become false before the subsequent safe_psql() call on the standby. > I think Fuji-san is right, can we do something like this: my $drop_lsn = $primary->safe_psql( 'postgres', q{ BEGIN; DROP EVENT TRIGGER init_session; DROP FUNCTION init_session(); COMMIT; SELECT pg_current_wal_lsn(); }); $primary->wait_for_catchup($standby, 'replay', $drop_lsn); Then the following standby connection should reliably exercise the case where dathasloginevt is still true on the standby but no login event trigger remains. Regards, Ayush ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 12:45 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> @ 2026-05-21 08:10 ` Ayush Tiwari <[email protected]> 2026-05-21 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Ayush Tiwari @ 2026-05-21 08:10 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; [email protected]; [email protected] Hi, On Wed, 20 May 2026 at 18:15, Ayush Tiwari <[email protected]> wrote: > Hi, > > On Wed, 20 May 2026 at 17:35, Fujii Masao <[email protected]> wrote: > >> On Wed, May 20, 2026 at 8:31 PM Alexander Korotkov <[email protected]> >> wrote: >> > >> > On Wed, May 20, 2026 at 1:03 PM Fujii Masao <[email protected]> >> wrote: >> > > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari >> > > <[email protected]> wrote: >> > > > Thanks for the report and the precise repro. >> > > >> > > +1 >> > > >> > > > Attached patch adds a RecoveryInProgress() check to skip the cleanup >> > > > branch on standbys. >> > > >> > > Thanks for investigating this issue and for the patch! >> > > The patch looks good to me. >> > > >> > > > I think this needs to be backpatched too. >> > > >> > > Yes. Seems this should be backpatched to v17, where login event >> triggers >> > > were introduced. >> > >> > I've added a tap test reproducing the bug. I'm going to push and >> > backpatch this to v17 if no objections. >> >> +# Wait for the standby to replay the CREATE/DROP catalog state. At >> +# this point the standby's pg_database.dathasloginevt is still true. >> +$primary->wait_for_replay_catchup($standby); >> + >> +# A new connection to the standby exercises EventTriggerOnLogin()'s >> +# cleanup branch. With the RecoveryInProgress() guard, that branch is >> +# skipped on the standby and the connection succeeds. Without it the >> +# session aborts with a FATAL about AccessExclusiveLock. Probing the >> +# flag itself via safe_psql is what triggers the cleanup path. >> +is( $standby->safe_psql( >> + 'postgres', >> + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), >> + 't', >> + 'standby accepts connection and reports dangling dathasloginevt'); >> >> The test looks unstable to me. wait_for_replay_catchup() may connect to >> the primary to obtain the flush LSN, which could cause dathasloginevt to >> become false before the subsequent safe_psql() call on the standby. >> > I had registered this in commitfest, could see CI bot failing https://commitfest.postgresql.org/patch/6790/ my $drop_lsn = $primary->safe_psql( > 'postgres', q{ > BEGIN; > DROP EVENT TRIGGER init_session; > DROP FUNCTION init_session(); > COMMIT; > SELECT pg_current_wal_lsn(); > }); > > $primary->wait_for_catchup($standby, 'replay', $drop_lsn); > Attaching v3 with this change on top of Alexander's changes. Regards, Ayush Attachments: [application/octet-stream] v3-0001-Skip-pg_database.dathasloginevt-cleanup-on-standby.patch (7.2K, 3-v3-0001-Skip-pg_database.dathasloginevt-cleanup-on-standby.patch) download | inline diff: From f3bf9aeee0fe0ceda9ec4ce193de2c43b223db0e Mon Sep 17 00:00:00 2001 From: Ayush Tiwari <[email protected]> Date: Wed, 20 May 2026 14:20:52 +0300 Subject: [PATCH v3] 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. Add a recovery TAP test that reproduces the original report: create and drop a login event trigger on the primary in one session, wait for the standby to replay, then verify that a fresh connection to the standby succeeds. Backpatch to v17, where the login event triggers were introduced. Author: Ayush Tiwari <[email protected]> Reported-by: Egor Chindyaskin <[email protected]> Reviewed-by: Fujii Masao <[email protected]> Reviewed-by: Alexander Korotkov <[email protected]> Discussion: https://postgr.es/m/19488-d7ccfca2bf6b74b0%40postgresql.org Backpatch-through: 17 --- src/backend/commands/event_trigger.c | 10 ++- src/test/recovery/meson.build | 1 + .../t/053_standby_login_event_trigger.pl | 79 +++++++++++++++++++ 3 files changed, 89 insertions(+), 1 deletion(-) create mode 100644 src/test/recovery/t/053_standby_login_event_trigger.pl diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index dcd2f5a09bb..adc6eabc0f4 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -937,8 +937,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)) { /* diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index 36d789720a3..9eb8ed11425 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -61,6 +61,7 @@ tests += { 't/050_redo_segment_missing.pl', 't/051_effective_wal_level.pl', 't/052_checkpoint_segment_missing.pl', + 't/053_standby_login_event_trigger.pl', ], }, } diff --git a/src/test/recovery/t/053_standby_login_event_trigger.pl b/src/test/recovery/t/053_standby_login_event_trigger.pl new file mode 100644 index 00000000000..20dcad0451d --- /dev/null +++ b/src/test/recovery/t/053_standby_login_event_trigger.pl @@ -0,0 +1,79 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Verify that connecting to a standby still works after a login event +# trigger has been created and dropped on the primary. +# +# CREATE EVENT TRIGGER ... ON login sets pg_database.dathasloginevt to +# true on the primary, but DROP EVENT TRIGGER does not clear it -- the +# next login event trigger pass clears the flag lazily on the primary. +# That dangling flag replicates to the standby. Before the +# RecoveryInProgress() guard in EventTriggerOnLogin(), the standby +# tried to clear the flag itself, which requires AccessExclusiveLock +# on the database object; that lock mode is forbidden during recovery, +# so the new connection died with FATAL. + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Set up primary and a streaming standby. +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(allows_streaming => 1); +$primary->start; + +my $backup_name = 'login_evt_backup'; +$primary->backup($backup_name); + +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($primary, $backup_name, has_streaming => 1); +$standby->start; + +# Sanity check: a fresh standby with dathasloginevt = false accepts +# connections without exercising the cleanup branch. +$standby->safe_psql('postgres', 'SELECT 1'); + +# Create and drop a login event trigger on the primary in a single +# session. CREATE EVENT TRIGGER sets pg_database.dathasloginevt = +# true; mark it ENABLE ALWAYS so the scenario matches the original +# bug report. After DROP the flag remains set on disk until a +# subsequent login on the primary clears it -- so we must not open +# another connection to the primary that would clear it before the +# standby replays the DROP EVENT TRIGGER. +my $drop_login_trigger_lsn = $primary->safe_psql( + 'postgres', q{ +CREATE FUNCTION init_session() RETURNS event_trigger +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; +DROP FUNCTION init_session(); +SELECT pg_current_wal_lsn(); +}); + +# Wait for the standby to replay the CREATE/DROP catalog state. At +# this point the standby's pg_database.dathasloginevt is still true. +$primary->wait_for_catchup($standby, 'replay', $drop_login_trigger_lsn); + +# A new connection to the standby exercises EventTriggerOnLogin()'s +# cleanup branch. With the RecoveryInProgress() guard, that branch is +# skipped on the standby and the connection succeeds. Without it the +# session aborts with a FATAL about AccessExclusiveLock. Probing the +# flag itself via safe_psql is what triggers the cleanup path. +is( $standby->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), + 't', + 'standby accepts connection and reports dangling dathasloginevt'); + +# Repeat with a non-die psql to make sure no FATAL is emitted. +my ($ret, $stdout, $stderr) = $standby->psql('postgres', 'SELECT 1'); +is($ret, 0, 'standby accepts second connection with dangling dathasloginevt'); +unlike( + $stderr, + qr/cannot acquire lock mode AccessExclusiveLock/, + 'no AccessExclusiveLock FATAL on standby login'); + +done_testing(); -- 2.34.1 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 12:45 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-21 08:10 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> @ 2026-05-21 11:31 ` Alexander Korotkov <[email protected]> 2026-05-21 11:48 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Alexander Korotkov @ 2026-05-21 11:31 UTC (permalink / raw) To: Ayush Tiwari <[email protected]>; +Cc: Fujii Masao <[email protected]>; [email protected]; [email protected] On Thu, May 21, 2026 at 11:10 AM Ayush Tiwari <[email protected]> wrote: > On Wed, 20 May 2026 at 18:15, Ayush Tiwari <[email protected]> wrote: >> >> Hi, >> >> On Wed, 20 May 2026 at 17:35, Fujii Masao <[email protected]> wrote: >>> >>> On Wed, May 20, 2026 at 8:31 PM Alexander Korotkov <[email protected]> wrote: >>> > >>> > On Wed, May 20, 2026 at 1:03 PM Fujii Masao <[email protected]> wrote: >>> > > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari >>> > > <[email protected]> wrote: >>> > > > Thanks for the report and the precise repro. >>> > > >>> > > +1 >>> > > >>> > > > Attached patch adds a RecoveryInProgress() check to skip the cleanup >>> > > > branch on standbys. >>> > > >>> > > Thanks for investigating this issue and for the patch! >>> > > The patch looks good to me. >>> > > >>> > > > I think this needs to be backpatched too. >>> > > >>> > > Yes. Seems this should be backpatched to v17, where login event triggers >>> > > were introduced. >>> > >>> > I've added a tap test reproducing the bug. I'm going to push and >>> > backpatch this to v17 if no objections. >>> >>> +# Wait for the standby to replay the CREATE/DROP catalog state. At >>> +# this point the standby's pg_database.dathasloginevt is still true. >>> +$primary->wait_for_replay_catchup($standby); >>> + >>> +# A new connection to the standby exercises EventTriggerOnLogin()'s >>> +# cleanup branch. With the RecoveryInProgress() guard, that branch is >>> +# skipped on the standby and the connection succeeds. Without it the >>> +# session aborts with a FATAL about AccessExclusiveLock. Probing the >>> +# flag itself via safe_psql is what triggers the cleanup path. >>> +is( $standby->safe_psql( >>> + 'postgres', >>> + "SELECT dathasloginevt FROM pg_database WHERE datname = 'postgres'"), >>> + 't', >>> + 'standby accepts connection and reports dangling dathasloginevt'); >>> >>> The test looks unstable to me. wait_for_replay_catchup() may connect to >>> the primary to obtain the flush LSN, which could cause dathasloginevt to >>> become false before the subsequent safe_psql() call on the standby. > > > I had registered this in commitfest, could see CI bot failing > https://commitfest.postgresql.org/patch/6790/ > >> my $drop_lsn = $primary->safe_psql( >> 'postgres', q{ >> BEGIN; >> DROP EVENT TRIGGER init_session; >> DROP FUNCTION init_session(); >> COMMIT; >> SELECT pg_current_wal_lsn(); >> }); >> >> $primary->wait_for_catchup($standby, 'replay', $drop_lsn); > > > Attaching v3 with this change on top of Alexander's changes. I suggest another approach. Create a separate test database and apply event trigger on it. wait_for_catchup() and others use 'postgres' database and wouldn't touch our test database. I also added check for successful clearance of the flag on both primary and standby. One issue spotted there: in-place heap update doesn't issue a WAL flush. But I think that's minor, WAL could be flushed by any subsequent operation. ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] v4-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch (9.0K, 2-v4-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch) download | inline diff: From 1d2a809274e0265c2347fe1ccfbd433444b9967a Mon Sep 17 00:00:00 2001 From: Alexander Korotkov <[email protected]> Date: Wed, 20 May 2026 14:20:52 +0300 Subject: [PATCH v4] 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. Add a recovery TAP test that reproduces the original report: create and drop a login event trigger on the primary in one session, wait for the standby to replay, then verify that a fresh connection to the standby succeeds. Backpatch to v17, where the login event triggers were introduced. Author: Ayush Tiwari <[email protected]> Reported-by: Egor Chindyaskin <[email protected]> Reviewed-by: Fujii Masao <[email protected]> Reviewed-by: Alexander Korotkov <[email protected]> Discussion: https://postgr.es/m/19488-d7ccfca2bf6b74b0%40postgresql.org Backpatch-through: 17 --- src/backend/commands/event_trigger.c | 10 +- src/test/recovery/meson.build | 1 + .../t/053_standby_login_event_trigger.pl | 125 ++++++++++++++++++ 3 files changed, 135 insertions(+), 1 deletion(-) create mode 100644 src/test/recovery/t/053_standby_login_event_trigger.pl diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index dcd2f5a09bb..adc6eabc0f4 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -937,8 +937,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)) { /* diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index 36d789720a3..9eb8ed11425 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -61,6 +61,7 @@ tests += { 't/050_redo_segment_missing.pl', 't/051_effective_wal_level.pl', 't/052_checkpoint_segment_missing.pl', + 't/053_standby_login_event_trigger.pl', ], }, } diff --git a/src/test/recovery/t/053_standby_login_event_trigger.pl b/src/test/recovery/t/053_standby_login_event_trigger.pl new file mode 100644 index 00000000000..81903379092 --- /dev/null +++ b/src/test/recovery/t/053_standby_login_event_trigger.pl @@ -0,0 +1,125 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Verify that connecting to a standby still works after a login event +# trigger has been created and dropped on the primary. +# +# CREATE EVENT TRIGGER ... ON login sets pg_database.dathasloginevt to +# true on the primary, but DROP EVENT TRIGGER does not clear it -- the +# next login event trigger pass clears the flag lazily on the primary. +# That dangling flag replicates to the standby. Before the +# RecoveryInProgress() guard in EventTriggerOnLogin(), the standby +# tried to clear the flag itself, which requires AccessExclusiveLock +# on the database object; that lock mode is forbidden during recovery, +# so the new connection died with FATAL. +# +# To keep the test robust the event trigger is set up in a dedicated +# database (regress_login_evt). All synchronisation helpers below -- +# wait_for_replay_catchup() and friends -- connect to "postgres" on +# the primary; if the trigger were created in "postgres" itself, that +# probe connection would enter the cleanup branch on the primary and +# silently clear the flag before the test even runs, making the +# scenario unreproducible. + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Set up primary and a streaming standby. +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(allows_streaming => 1); +$primary->start; + +my $backup_name = 'login_evt_backup'; +$primary->backup($backup_name); + +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($primary, $backup_name, has_streaming => 1); +$standby->start; + +# A dedicated database isolates the dangling dathasloginevt flag from +# any helper that connects to the default "postgres" database. +$primary->safe_psql('postgres', 'CREATE DATABASE regress_login_evt'); +$primary->wait_for_replay_catchup($standby); + +# Sanity check: the standby can connect to the new database before +# the trigger machinery has touched it. +$standby->safe_psql('regress_login_evt', 'SELECT 1'); + +# Create and drop a login event trigger inside the dedicated database +# in a single session. CREATE EVENT TRIGGER sets +# pg_database.dathasloginevt = true for regress_login_evt; mark it +# ENABLE ALWAYS so the scenario matches the original bug report. +# After DROP the flag remains set on disk until a subsequent login on +# the primary clears it; since later helpers only touch the +# "postgres" database, regress_login_evt's flag stays set and +# replicates that way to the standby. +$primary->safe_psql( + 'regress_login_evt', q{ +CREATE FUNCTION init_session() RETURNS event_trigger +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; +DROP FUNCTION init_session(); +}); + +# Wait for the standby to replay the CREATE/DROP catalog state. This +# probes "postgres", not regress_login_evt, so it does not disturb +# the dangling flag. +$primary->wait_for_replay_catchup($standby); + +# The flag remains set in regress_login_evt on both sides. +is( $primary->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" + ), + 't', + 'dathasloginevt remains set on primary after DROP EVENT TRIGGER'); +is( $standby->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" + ), + 't', + 'dathasloginevt replicated to standby'); + +# A new connection to regress_login_evt on the standby exercises +# EventTriggerOnLogin()'s cleanup branch. With the +# RecoveryInProgress() guard it succeeds; without it the session +# aborts with a FATAL about AccessExclusiveLock. +my ($ret, $stdout, $stderr) = $standby->psql('regress_login_evt', 'SELECT 1'); +is($ret, 0, + 'standby accepts connection to database with dangling dathasloginevt'); +unlike( + $stderr, + qr/cannot acquire lock mode AccessExclusiveLock/, + 'no AccessExclusiveLock FATAL on standby login'); + +# Finally exercise the primary-side cleanup that the standby is meant +# to defer to. Opening a fresh session against regress_login_evt on +# the primary enters EventTriggerOnLogin()'s cleanup branch with the +# trigger list empty; AccessExclusiveLock is allowed outside recovery, +# so the flag is cleared in place. The in-place update emits a +# XLOG_HEAP_INPLACE record but does not assign an xid or write a +# commit record, so the WAL is not auto-flushed -- force a flush via +# pg_switch_wal() so the record reaches the standby. +$primary->safe_psql('regress_login_evt', 'SELECT 1'); +is( $primary->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" + ), + 'f', + 'primary clears dathasloginevt on next login after DROP'); + +$primary->safe_psql('postgres', 'SELECT pg_switch_wal()'); +$primary->wait_for_replay_catchup($standby); +is( $standby->safe_psql( + 'postgres', + "SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" + ), + 'f', + 'cleared dathasloginevt replicates to standby'); + +done_testing(); -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 12:45 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-21 08:10 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-21 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> @ 2026-05-21 11:48 ` Ayush Tiwari <[email protected]> 2026-05-25 08:53 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Ayush Tiwari @ 2026-05-21 11:48 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Fujii Masao <[email protected]>; [email protected]; [email protected] Hi, On Thu, 21 May 2026 at 17:01, Alexander Korotkov <[email protected]> wrote: > > I suggest another approach. Create a separate test database and apply > event trigger on it. wait_for_catchup() and others use 'postgres' > database and wouldn't touch our test database. > I also added check for successful clearance of the flag on both > primary and standby. One issue spotted there: in-place heap update > doesn't issue a WAL flush. But I think that's minor, WAL could be > flushed by any subsequent operation. I agree the approach you are suggesting is better. Patch looks good to me! Regards, Ayush ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Fujii Masao <[email protected]> 2026-05-20 12:45 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-21 08:10 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> 2026-05-21 11:31 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Alexander Korotkov <[email protected]> 2026-05-21 11:48 ` Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always Ayush Tiwari <[email protected]> @ 2026-05-25 08:53 ` Alexander Korotkov <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Alexander Korotkov @ 2026-05-25 08:53 UTC (permalink / raw) To: Ayush Tiwari <[email protected]>; +Cc: Fujii Masao <[email protected]>; [email protected]; [email protected] On Thu, May 21, 2026 at 2:48 PM Ayush Tiwari <[email protected]> wrote: > On Thu, 21 May 2026 at 17:01, Alexander Korotkov <[email protected]> wrote: >> >> >> I suggest another approach. Create a separate test database and apply >> event trigger on it. wait_for_catchup() and others use 'postgres' >> database and wouldn't touch our test database. >> I also added check for successful clearance of the flag on both >> primary and standby. One issue spotted there: in-place heap update >> doesn't issue a WAL flush. But I think that's minor, WAL could be >> flushed by any subsequent operation. > > > I agree the approach you are suggesting is better. > > Patch looks good to me! Thank you. I'm going to push and backpatch it if no objections. ------ Regards, Alexander Korotkov Supabase ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-05-25 08:53 UTC | newest] Thread overview: 10+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-19 11:06 BUG #19488: Standby connection fails after dropping on login event trigger enabled always PG Bug reporting form <[email protected]> 2026-05-20 04:37 ` Ayush Tiwari <[email protected]> 2026-05-20 10:03 ` Fujii Masao <[email protected]> 2026-05-20 11:31 ` Alexander Korotkov <[email protected]> 2026-05-20 12:05 ` Fujii Masao <[email protected]> 2026-05-20 12:45 ` Ayush Tiwari <[email protected]> 2026-05-21 08:10 ` Ayush Tiwari <[email protected]> 2026-05-21 11:31 ` Alexander Korotkov <[email protected]> 2026-05-21 11:48 ` Ayush Tiwari <[email protected]> 2026-05-25 08:53 ` Alexander Korotkov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox