public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: kyzevan23@mail.ru
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always
Date: Wed, 20 May 2026 18:15:44 +0530
Message-ID: <CAJTYsWXDoWuXCbgOYfTiBFTxg6-d+CX8urk0KswQX0Fm4JS+ag@mail.gmail.com> (raw)
In-Reply-To: <CAHGQGwHEVC1181tW78HuotgbF2jMF_59wzKrtS7hFYn_f9tuew@mail.gmail.com>
References: <19488-d7ccfca2bf6b74b0@postgresql.org>
	<CAJTYsWUQBwCOQ=uGnLP7C2ofN6TqUpGeOHan2N99mza1DjybsQ@mail.gmail.com>
	<CAHGQGwG=F8_12nPKxUkYm-uS5QFJp+KF=3X5zM2vKP=0SKAeHw@mail.gmail.com>
	<CAPpHfdt_Fj73xYhKtEc66v7c_ZLaUSFqXfCWhRdp3tMX6ksMjQ@mail.gmail.com>
	<CAHGQGwHEVC1181tW78HuotgbF2jMF_59wzKrtS7hFYn_f9tuew@mail.gmail.com>

Hi,

On Wed, 20 May 2026 at 17:35, Fujii Masao <masao.fujii@gmail.com> wrote:

> On Wed, May 20, 2026 at 8:31 PM Alexander Korotkov <aekorotkov@gmail.com>
> wrote:
> >
> > On Wed, May 20, 2026 at 1:03 PM Fujii Masao <masao.fujii@gmail.com>
> wrote:
> > > On Wed, May 20, 2026 at 1:37 PM Ayush Tiwari
> > > <ayushtiwari.slg01@gmail.com> 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

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, masao.fujii@gmail.com, aekorotkov@gmail.com, kyzevan23@mail.ru, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #19488: Standby connection fails after dropping on login event trigger enabled always
  In-Reply-To: <CAJTYsWXDoWuXCbgOYfTiBFTxg6-d+CX8urk0KswQX0Fm4JS+ag@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