Re: Additional Notes

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(at)neophi(dot)com> [http://danielr.neophi.com/]

On Wed, Nov 15, 2023 at 1:05 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>