MIME-Version: 1.0
References: <19488-d7ccfca2bf6b74b0@postgresql.org>
In-Reply-To: <19488-d7ccfca2bf6b74b0@postgresql.org>
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Wed, 20 May 2026 10:07:15 +0530
Message-ID: 
 <CAJTYsWUQBwCOQ=uGnLP7C2ofN6TqUpGeOHan2N99mza1DjybsQ@mail.gmail.com>
Subject: Re: BUG #19488: Standby connection fails after dropping on login
 event trigger enabled always
To: kyzevan23@mail.ru, pgsql-bugs@lists.postgresql.org
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Content-Type: multipart/mixed; boundary="0000000000004cc5ed0652385af1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJTYsWUQBwCOQ%3DuGnLP7C2ofN6TqUpGeOHan2N99mza1DjybsQ%40mail.gmail.com>
Precedence: bulk

--0000000000004cc5ed0652385af1
Content-Type: multipart/alternative; boundary="0000000000004cc5ec0652385aef"

--0000000000004cc5ec0652385aef
Content-Type: text/plain; charset="UTF-8"

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

--0000000000004cc5ec0652385aef
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,<div><br></div></div><div class=3D"gma=
il_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On We=
d, 20 May 2026 at 02:34, PG Bug reporting form &lt;<a href=3D"mailto:norepl=
y@postgresql.org">noreply@postgresql.org</a>&gt; wrote:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">The following bug has been logged o=
n the website:<br>
<br>
Bug reference:=C2=A0 =C2=A0 =C2=A0 19488<br>
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Egor Chindyaskin<br>
Email address:=C2=A0 =C2=A0 =C2=A0 <a href=3D"mailto:kyzevan23@mail.ru" tar=
get=3D"_blank">kyzevan23@mail.ru</a><br>
PostgreSQL version: 18.4<br>
Operating system:=C2=A0 =C2=A0Ubuntu 26.04<br>
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
<br>
Hello!<br>
In a master + physical standby setup, connection to the standby fails after=
<br>
creating a login event trigger on the master, enabling it as always, and<br=
>
then dropping it without reconnecting to the master.<br>
Also reproduces on master branch.<br>
Steps to reproduce:<br>
<br>
1. Run the following SQL script on the master:<br>
CREATE OR REPLACE FUNCTION init_session()<br>
=C2=A0 RETURNS event_trigger SECURITY DEFINER<br>
=C2=A0 LANGUAGE plpgsql AS<br>
$$<br>
BEGIN<br>
=C2=A0 RAISE NOTICE &#39;init_session&#39;;<br>
END;<br>
$$;<br>
<br>
CREATE EVENT TRIGGER init_session<br>
=C2=A0 ON login<br>
=C2=A0 EXECUTE FUNCTION init_session();<br>
<br>
ALTER EVENT TRIGGER init_session ENABLE ALWAYS;<br>
<br>
DROP EVENT TRIGGER init_session;<br>
<br>
2. Try to connect to the standby:<br>
psql -p5433<br>
<br>
Result:<br>
psql: error: connection to server on socket &quot;/tmp/.s.PGSQL.5433&quot; =
failed:<br>
FATAL:=C2=A0 cannot acquire lock mode AccessExclusiveLock on database objec=
ts<br>
while recovery is in progress<br>
HINT:=C2=A0 Only RowExclusiveLock or less can be acquired on database objec=
ts<br>
during recovery.<br>
<br>
--<br>
With best regards,<br>
Egor Chindyaskin<br>
<br>
Postgres Professional: <a href=3D"https://postgrespro.com" rel=3D"noreferre=
r" target=3D"_blank">https://postgrespro.com</a></blockquote><div>=C2=A0</d=
iv>Thanks for the report and the precise repro.<br><br>The cause is in Even=
tTriggerOnLogin().=C2=A0 When a session connects to a<br>database whose pg_=
database.dathasloginevt flag is set but no login<br>event triggers are actu=
ally present, the function tries to clear the<br>flag via:<br><br>=C2=A0 Co=
nditionalLockSharedObject(DatabaseRelationId, MyDatabaseId, 0,<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 AccessExclusiveLock);<br><br>On a hot standby, Loc=
kAcquireExtended() refuses any lock stronger than<br>RowExclusiveLock on LO=
CKTAG_OBJECT/LOCKTAG_RELATION while<br>RecoveryInProgress() is true, which =
surfaces as a FATAL on the new<br>connection.=C2=A0 The standby ends up in =
this state precisely after your<br>steps because the primary set dathaslogi=
nevt =3D true while the trigger<br>was active, then dropped the trigger but=
 (intentionally) left the<br>flag set on disk for the next normal connectio=
n to clean up.=C2=A0 That<br>state replicates to the standby, and any subse=
quent connection on the<br>standby then enters the cleanup path and crashes=
.<br><br>A standby should not try to clear that flag itself(?) the only cor=
rect<br>update path on a standby is WAL replay from the primary.<br><br>Att=
ached patch adds a RecoveryInProgress() check to skip the cleanup<br><div>b=
ranch on standbys. I think this needs to be backpatched too.<br><br>Regards=
,<br>Ayush</div></div></div>

--0000000000004cc5ec0652385aef--

--0000000000004cc5ed0652385af1
Content-Type: application/octet-stream;
	name="v1-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch"
Content-Disposition: attachment;
	filename="v1-0001-Skip-pg_database.dathasloginevt-cleanup-on-standb.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mpdkjl520>
X-Attachment-Id: f_mpdkjl520

RnJvbSA4NzJhY2M3ZDhkYzNjNGVmZGFiYzJjZTMyZWY1NTU2YzQ2NDQwZTRkIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBeXVzaCBUaXdhcmkgPGF5dXNodGl3YXJpLnNsZzAxQGdtYWls
LmNvbT4KRGF0ZTogV2VkLCAyMCBNYXkgMjAyNiAwMzozMzo0NiArMDAwMApTdWJqZWN0OiBbUEFU
Q0ggdjFdIFNraXAgcGdfZGF0YWJhc2UuZGF0aGFzbG9naW5ldnQgY2xlYW51cCBvbiBzdGFuZGJ5
CgpFdmVudFRyaWdnZXJPbkxvZ2luKCkgdHJpZXMgdG8gY2xlYXIgcGdfZGF0YWJhc2UuZGF0aGFz
bG9naW5ldnQgd2hlbgp0aGUgZGF0YWJhc2Ugbm8gbG9uZ2VyIGhhcyBhbnkgbG9naW4gZXZlbnQg
dHJpZ2dlcnMgYnV0IHRoZSBmbGFnIGlzCnN0aWxsIHNldC4gIFRvIG1ha2UgdGhhdCBzYWZlIGFn
YWluc3QgY29uY3VycmVudCBmbGFnIHNldHRlcnMsIGl0CnRha2VzIGEgY29uZGl0aW9uYWwgQWNj
ZXNzRXhjbHVzaXZlTG9jayBvbiB0aGUgZGF0YWJhc2Ugb2JqZWN0LgoKT24gYSBob3Qgc3RhbmRi
eSwgdGhhdCBsb2NrIGFjcXVpc2l0aW9uIGZhaWxzIG91dHJpZ2h0IHdpdGgKCiAgRkFUQUw6ICBj
YW5ub3QgYWNxdWlyZSBsb2NrIG1vZGUgQWNjZXNzRXhjbHVzaXZlTG9jayBvbiBkYXRhYmFzZQog
ICAgICAgICAgb2JqZWN0cyB3aGlsZSByZWNvdmVyeSBpcyBpbiBwcm9ncmVzcwoKYmVjYXVzZSBM
b2NrQWNxdWlyZUV4dGVuZGVkKCkgcmVmdXNlcyBsb2NrcyBzdHJvbmdlciB0aGFuClJvd0V4Y2x1
c2l2ZUxvY2sgb24gZGF0YWJhc2Ugb2JqZWN0cyBkdXJpbmcgcmVjb3ZlcnkuICBUaGUgc3RhbmRi
eQphbHJlYWR5IHJlcGxheXMgdGhlIGZsYWcncyB2YWx1ZSBmcm9tIHRoZSBwcmltYXJ5LCBzbyB0
aGUgZGFuZ2xpbmcKZmxhZyBpcyB0aGUgcmVzdWx0IG9mIHJlcGxheWluZyBhIHN0YXRlIGluIHdo
aWNoIHRoZSBwcmltYXJ5IGhhZAphbHJlYWR5IGRyb3BwZWQgaXRzIGxvZ2luIGV2ZW50IHRyaWdn
ZXJzIGJ1dCBub3QgeWV0IHJ1biBhIGxvZ2luIGV2ZW50CnRyaWdnZXIgcGFzcyB0byBjbGVhciB0
aGUgZmxhZy4gIEFueSBzZXNzaW9uIGNvbm5lY3RpbmcgdG8gdGhlIHN0YW5kYnkKaW4gdGhhdCB3
aW5kb3cgdGhlcmVmb3JlIGZhaWxzIHRvIGNvbm5lY3QuCgpTa2lwIHRoZSBjbGVhbnVwIG9uIGEg
c3RhbmRieS4gIFRoZSBmbGFnIHdpbGwgYmUgY2xlYXJlZCB2aWEgV0FMCnJlcGxheSBvbmNlIHRo
ZSBwcmltYXJ5IGNsZWFycyBpdCBvbiBpdHMgc2lkZSwgd2hpY2ggaXMgdGhlIG9ubHkKY29ycmVj
dCB3YXkgdG8gdXBkYXRlIGEgY2F0YWxvZyBmcm9tIGEgc3RhbmRieS4KClJlcG9ydGVkLWJ5OiBF
Z29yIENoaW5keWFza2luIDxreXpldmFuMjNAbWFpbC5ydT4KRGlzY3Vzc2lvbjogaHR0cHM6Ly9w
b3N0Z3IuZXMvbS8xOTQ4OC0uLi5AcG9zdGdyZXNxbC5vcmcKLS0tCiBzcmMvYmFja2VuZC9jb21t
YW5kcy9ldmVudF90cmlnZ2VyLmMgfCAxMSArKysrKysrKysrLQogMSBmaWxlIGNoYW5nZWQsIDEw
IGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9j
b21tYW5kcy9ldmVudF90cmlnZ2VyLmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9ldmVudF90cmln
Z2VyLmMKaW5kZXggZGNkMmY1YTA5YmIuLjA2ODA0MjEyNjhkIDEwMDY0NAotLS0gYS9zcmMvYmFj
a2VuZC9jb21tYW5kcy9ldmVudF90cmlnZ2VyLmMKKysrIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMv
ZXZlbnRfdHJpZ2dlci5jCkBAIC0xOCw2ICsxOCw3IEBACiAjaW5jbHVkZSAiYWNjZXNzL2h0dXBf
ZGV0YWlscy5oIgogI2luY2x1ZGUgImFjY2Vzcy90YWJsZS5oIgogI2luY2x1ZGUgImFjY2Vzcy94
YWN0LmgiCisjaW5jbHVkZSAiYWNjZXNzL3hsb2cuaCIKICNpbmNsdWRlICJjYXRhbG9nL2NhdGFs
b2cuaCIKICNpbmNsdWRlICJjYXRhbG9nL2RlcGVuZGVuY3kuaCIKICNpbmNsdWRlICJjYXRhbG9n
L2luZGV4aW5nLmgiCkBAIC05MzcsOCArOTM4LDE2IEBAIEV2ZW50VHJpZ2dlck9uTG9naW4odm9p
ZCkKIAkgKiBsb2NrIHRvIHByZXZlbnQgY29uY3VycmVudCBTZXREYXRhYmFzZUhhc0xvZ2luRXZl
bnRUcmlnZ2VycygpLCBidXQgd2UKIAkgKiBkb24ndCB3YW50IHRvIGhhbmcgdGhlIGNvbm5lY3Rp
b24gd2FpdGluZyBvbiB0aGUgbG9jay4gIFRodXMsIHdlIGFyZQogCSAqIGp1c3QgdHJ5aW5nIHRv
IGFjcXVpcmUgdGhlIGxvY2sgY29uZGl0aW9uYWxseS4KKwkgKgorCSAqIFNraXAgdGhpcyBvbiBh
IGhvdCBzdGFuZGJ5OiB0aGUgY29uZGl0aW9uYWwgQWNjZXNzRXhjbHVzaXZlTG9jayBvbiB0aGUK
KwkgKiBkYXRhYmFzZSBvYmplY3Qgd291bGQgZmFpbCB3aXRoICJjYW5ub3QgYWNxdWlyZSBsb2Nr
IG1vZGUgLi4uIHdoaWxlCisJICogcmVjb3ZlcnkgaXMgaW4gcHJvZ3Jlc3MiLCB3aGljaCB0aGUg
Y2FsbGVyIHdvdWxkIHN1cmZhY2UgYXMgYSBGQVRBTAorCSAqIGNvbm5lY3Rpb24gZXJyb3IuICBP
biBhIHN0YW5kYnkgd2UgY2Fubm90IChhbmQgbXVzdCBub3QpIGNsZWFyIHRoZQorCSAqIHBnX2Rh
dGFiYXNlIGZsYWcgb3Vyc2VsdmVzOyBpdCB3aWxsIGJlIGNsZWFyZWQgdmlhIFdBTCByZXBsYXkg
b25jZSB0aGUKKwkgKiBwcmltYXJ5J3MgbmV4dCBsb2dpbiBldmVudCB0cmlnZ2VyIHJ1biBjbGVh
cnMgaXQgb24gdGhlIHByaW1hcnkuCiAJICovCi0JZWxzZSBpZiAoQ29uZGl0aW9uYWxMb2NrU2hh
cmVkT2JqZWN0KERhdGFiYXNlUmVsYXRpb25JZCwgTXlEYXRhYmFzZUlkLAorCWVsc2UgaWYgKCFS
ZWNvdmVyeUluUHJvZ3Jlc3MoKSAmJgorCQkJIENvbmRpdGlvbmFsTG9ja1NoYXJlZE9iamVjdChE
YXRhYmFzZVJlbGF0aW9uSWQsIE15RGF0YWJhc2VJZCwKIAkJCQkJCQkJCQkgMCwgQWNjZXNzRXhj
bHVzaXZlTG9jaykpCiAJewogCQkvKgotLSAKMi40My4wCgo=
--0000000000004cc5ed0652385af1--