| 1 |
# Copyright (c) 2026, PostgreSQL Global Development Group |
| 2 |
# |
| 3 |
# Verify that connecting to a standby still works after a login event |
| 4 |
# trigger has been created and dropped on the primary. |
| 5 |
# |
| 6 |
# CREATE EVENT TRIGGER ... ON login sets pg_database.dathasloginevt to |
| 7 |
# true on the primary, but DROP EVENT TRIGGER does not clear it -- the |
| 8 |
# next login event trigger pass clears the flag lazily on the primary. |
| 9 |
# That dangling flag replicates to the standby. Before the |
| 10 |
# RecoveryInProgress() guard in EventTriggerOnLogin(), the standby |
| 11 |
# tried to clear the flag itself, which requires AccessExclusiveLock |
| 12 |
# on the database object; that lock mode is forbidden during recovery, |
| 13 |
# so the new connection died with FATAL. |
| 14 |
# |
| 15 |
# To keep the test robust the event trigger is set up in a dedicated |
| 16 |
# database (regress_login_evt). All synchronisation helpers below -- |
| 17 |
# wait_for_replay_catchup() and friends -- connect to "postgres" on |
| 18 |
# the primary; if the trigger were created in "postgres" itself, that |
| 19 |
# probe connection would enter the cleanup branch on the primary and |
| 20 |
# silently clear the flag before the test even runs, making the |
| 21 |
# scenario unreproducible. |
| 22 |
|
| 23 |
use strict; |
| 24 |
use warnings FATAL => 'all'; |
| 25 |
use PostgreSQL::Test::Cluster; |
| 26 |
use PostgreSQL::Test::Utils; |
| 27 |
use Test::More; |
| 28 |
|
| 29 |
# Set up primary and a streaming standby. |
| 30 |
my $primary = PostgreSQL::Test::Cluster->new('primary'); |
| 31 |
$primary->init(allows_streaming => 1); |
| 32 |
$primary->start; |
| 33 |
|
| 34 |
my $backup_name = 'login_evt_backup'; |
| 35 |
$primary->backup($backup_name); |
| 36 |
|
| 37 |
my $standby = PostgreSQL::Test::Cluster->new('standby'); |
| 38 |
$standby->init_from_backup($primary, $backup_name, has_streaming => 1); |
| 39 |
$standby->start; |
| 40 |
|
| 41 |
# A dedicated database isolates the dangling dathasloginevt flag from |
| 42 |
# any helper that connects to the default "postgres" database. |
| 43 |
$primary->safe_psql('postgres', 'CREATE DATABASE regress_login_evt'); |
| 44 |
$primary->wait_for_replay_catchup($standby); |
| 45 |
|
| 46 |
# Sanity check: the standby can connect to the new database before |
| 47 |
# the trigger machinery has touched it. |
| 48 |
$standby->safe_psql('regress_login_evt', 'SELECT 1'); |
| 49 |
|
| 50 |
# Create and drop a login event trigger inside the dedicated database |
| 51 |
# in a single session. CREATE EVENT TRIGGER sets |
| 52 |
# pg_database.dathasloginevt = true for regress_login_evt; mark it |
| 53 |
# ENABLE ALWAYS so the scenario matches the original bug report. |
| 54 |
# After DROP the flag remains set on disk until a subsequent login on |
| 55 |
# the primary clears it; since later helpers only touch the |
| 56 |
# "postgres" database, regress_login_evt's flag stays set and |
| 57 |
# replicates that way to the standby. |
| 58 |
$primary->safe_psql( |
| 59 |
'regress_login_evt', q{ |
| 60 |
CREATE FUNCTION init_session() RETURNS event_trigger |
| 61 |
LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'init_session'; END $$; |
| 62 |
CREATE EVENT TRIGGER init_session ON login |
| 63 |
EXECUTE FUNCTION init_session(); |
| 64 |
ALTER EVENT TRIGGER init_session ENABLE ALWAYS; |
| 65 |
DROP EVENT TRIGGER init_session; |
| 66 |
DROP FUNCTION init_session(); |
| 67 |
}); |
| 68 |
|
| 69 |
# Wait for the standby to replay the CREATE/DROP catalog state. This |
| 70 |
# probes "postgres", not regress_login_evt, so it does not disturb |
| 71 |
# the dangling flag. |
| 72 |
$primary->wait_for_replay_catchup($standby); |
| 73 |
|
| 74 |
# The flag remains set in regress_login_evt on both sides. |
| 75 |
is( $primary->safe_psql( |
| 76 |
'postgres', |
| 77 |
"SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" |
| 78 |
), |
| 79 |
't', |
| 80 |
'dathasloginevt remains set on primary after DROP EVENT TRIGGER'); |
| 81 |
is( $standby->safe_psql( |
| 82 |
'postgres', |
| 83 |
"SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" |
| 84 |
), |
| 85 |
't', |
| 86 |
'dathasloginevt replicated to standby'); |
| 87 |
|
| 88 |
# A new connection to regress_login_evt on the standby exercises |
| 89 |
# EventTriggerOnLogin()'s cleanup branch. With the |
| 90 |
# RecoveryInProgress() guard it succeeds; without it the session |
| 91 |
# aborts with a FATAL about AccessExclusiveLock. |
| 92 |
my ($ret, $stdout, $stderr) = $standby->psql('regress_login_evt', 'SELECT 1'); |
| 93 |
is($ret, 0, |
| 94 |
'standby accepts connection to database with dangling dathasloginevt'); |
| 95 |
unlike( |
| 96 |
$stderr, |
| 97 |
qr/cannot acquire lock mode AccessExclusiveLock/, |
| 98 |
'no AccessExclusiveLock FATAL on standby login'); |
| 99 |
|
| 100 |
# Finally exercise the primary-side cleanup that the standby is meant |
| 101 |
# to defer to. Opening a fresh session against regress_login_evt on |
| 102 |
# the primary enters EventTriggerOnLogin()'s cleanup branch with the |
| 103 |
# trigger list empty; AccessExclusiveLock is allowed outside recovery, |
| 104 |
# so the flag is cleared in place. The in-place update emits a |
| 105 |
# XLOG_HEAP_INPLACE record but does not assign an xid or write a |
| 106 |
# commit record, so the WAL is not auto-flushed -- force a flush via |
| 107 |
# pg_switch_wal() so the record reaches the standby. |
| 108 |
$primary->safe_psql('regress_login_evt', 'SELECT 1'); |
| 109 |
is( $primary->safe_psql( |
| 110 |
'postgres', |
| 111 |
"SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" |
| 112 |
), |
| 113 |
'f', |
| 114 |
'primary clears dathasloginevt on next login after DROP'); |
| 115 |
|
| 116 |
$primary->safe_psql('postgres', 'SELECT pg_switch_wal()'); |
| 117 |
$primary->wait_for_replay_catchup($standby); |
| 118 |
is( $standby->safe_psql( |
| 119 |
'postgres', |
| 120 |
"SELECT dathasloginevt FROM pg_database WHERE datname = 'regress_login_evt'" |
| 121 |
), |
| 122 |
'f', |
| 123 |
'cleared dathasloginevt replicates to standby'); |
| 124 |
|
| 125 |
done_testing(); |