pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedAdditional Notes
4+ messages / 4 participants
[nested] [flat]
* Additional Notes
@ 2023-11-15 17:38 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2023-11-15 17:38 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: danielr@neophi.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/sql-notify.html
Description:
It would be good to add to the notes section that use of NOTIFY especially
within a TRIGGER requires an AccessExclusiveLock which may cause performance
issues. Old thread for reference:
https://www.postgresql.org/message-id/3598.1363354686%40sss.pgh.pa.us
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Additional Notes
@ 2023-11-15 18:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-11-15 18:05 UTC (permalink / raw)
To: danielr@neophi.com; pgsql-docs@lists.postgresql.org
On Wed, 2023-11-15 at 17:38 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/sql-notify.html
> Description:
>
> It would be good to add to the notes section that use of NOTIFY especially
> within a TRIGGER requires an AccessExclusiveLock which may cause performance
> issues. Old thread for reference:
> https://www.postgresql.org/message-id/3598.1363354686%40sss.pgh.pa.us
I don't see what this has to do with triggers. Even deferred triggers run
*before* this notify lock is taken.
The only possibility I see for such a lock to be held for a long time is if
COMMIT spends a long time waiting for a reply from a synchronous standby
server. Is that your problem?
I don't think that would require special documentation, because if your
synchronous standby does not respond in time, you normally have worse
problems than NOTIFY performance.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Additional Notes
@ 2023-11-16 14:35 Daniel Rinehart <danielr@neophi.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Daniel Rinehart @ 2023-11-16 14:35 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
Our callout use of NOTIFY within a TRIGGER may be tangential to the root
cause. What we wanted to call out is that neither the NOTIFY page or the
https://www.postgresql.org/docs/16/explicit-locking.html page mention that
NOTIFY uses an AccessExclusiveLock.
-- Daniel R. <danielr@neophi.com> [http://danielr.neophi.com/]
On Wed, Nov 15, 2023 at 1:05 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2023-11-15 at 17:38 +0000, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/sql-notify.html
> > Description:
> >
> > It would be good to add to the notes section that use of NOTIFY
> especially
> > within a TRIGGER requires an AccessExclusiveLock which may cause
> performance
> > issues. Old thread for reference:
> > https://www.postgresql.org/message-id/3598.1363354686%40sss.pgh.pa.us
>
> I don't see what this has to do with triggers. Even deferred triggers run
> *before* this notify lock is taken.
>
> The only possibility I see for such a lock to be held for a long time is if
> COMMIT spends a long time waiting for a reply from a synchronous standby
> server. Is that your problem?
>
> I don't think that would require special documentation, because if your
> synchronous standby does not respond in time, you normally have worse
> problems than NOTIFY performance.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Additional Notes
@ 2023-11-16 17:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Rinehart <danielr@neophi.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-11-16 17:05 UTC (permalink / raw)
To: Daniel Rinehart <danielr@neophi.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs@lists.postgresql.org
Daniel Rinehart <danielr@neophi.com> writes:
> Our callout use of NOTIFY within a TRIGGER may be tangential to the root
> cause. What we wanted to call out is that neither the NOTIFY page or the
> https://www.postgresql.org/docs/16/explicit-locking.html page mention that
> NOTIFY uses an AccessExclusiveLock.
Like Laurenz, I don't see this as being tremendously important.
The lock does not conflict with any user-acquirable lock, and
since it's not a lock on a relation it doesn't wind up getting
propagated to standby servers. We only use it as a handy way
to serialize commit of transactions that are writing the NOTIFY
queue. If it were a lesser but still exclusive lock type,
it wouldn't make any difference.
explicit-locking.html is really only about locks on tables.
Maybe that should be clarified somewhere?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-16 17:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-15 17:38 Additional Notes PG Doc comments form <noreply@postgresql.org>
2023-11-15 18:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-16 14:35 ` Daniel Rinehart <danielr@neophi.com>
2023-11-16 17:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox