Additional Notes
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:
/messages/by-id/3598.1363354686@sss.pgh.pa.us
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:
/messages/by-id/3598.1363354686@sss.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
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:
Show quoted text
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:
/messages/by-id/3598.1363354686@sss.pgh.pa.usI 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
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