Re: [GENERAL] NOTIFY command impact - Mailing list pgsql-general

In my case the monitoring user will be connecting, issuing the notify, then immediately disconnecting.

And we don't have any systems using listen/notify.

So I'm hoping there won't be a problem.

That's why I'm asking ☺

--Rob

On 2/21/17, 3:17 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:

    On 02/21/2017 01:07 PM, Rob Brucks wrote:
    > Hi All,
    >
    >
    >
    > I just wanted to check with you guys to make sure that constantly
    > issuing "NOTIFY" commands without corresponding "LISTEN" commands will
    > not cause any harm or excessive work for the PostgreSQL cluster. Nothing
    > that would put my cluster at risk.
    >
    >
    >
    > The reason I ask is because I was trying to implement a reliable method
    > of monitoring replication lag for streaming replication on 9.2+ systems
    > using the following SQL on slaves:
    >
    >     select extract(epoch from now() - pg_last_xact_replay_timestamp());
    >
    >
    >
    > This SQL provides me with a time-based measure of replication lag
    > instead of a byte-based measure. Time-based lag measurement is more
    > meaningful for us in time-sensitive applications.
    >
    >
    >
    > During my testing I noticed that if the database went "quiet" (no update
    > activity on the master) for a period of time, then the last replay
    > timestamp remained unchanged. Having little or no update activity
    > after-hours is very common on our smaller systems.
    >
    >
    >
    > This made the monitoring of replication lag inconsistent because,
    > despite the slave being "caught up" with the master, it was reporting an
    > increasing time lag.  And I didn't want our DBAs to get false alerts
    > from our monitoring.
    >
    >
    >
    > So I went on the hunt for a method of forcing replay to occur without
    > actually performing any database updates. I also did not want to grant
    > any kind of update capability on the database to my monitoring role, for
    > tighter security.
    >
    >
    >
    > I discovered that the monitoring role, despite not having any update
    > permissions, could successfully issue a "NOTIFY" command to a bogus
    > channel and that this command actually forced the log to replay on the
    > slave, updating the replay timestamp. This seems like a viable solution
    > to my problem.
    >
    >
    >
    > My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds
    > to ensure the timestamp is updated at least that frequently. But there
    > will not be an associated "LISTEN" for these notifications.
    >
    >
    >
    > However, I don't want to cause any problems for the PostgreSQL cluster
    > itself by having messages inserted with nobody to listen for them, which
    > is why I'm posting here.
    >
    >
    >
    > Do you see any long-term problems with constantly issuing "NOTIFY"
    > commands every 30 seconds without an associated "LISTEN" command?
    
    Depending on how long 'long term' is:
    
    https://www.postgresql.org/docs/9.6/static/sql-notify.html
    
    "There is a queue that holds notifications that have been sent but not 
    yet processed by all listening sessions. If this queue becomes full, 
    transactions calling NOTIFY will fail at commit. The queue is quite 
    large (8GB in a standard installation) and should be sufficiently sized 
    for almost every use case. However, no cleanup can take place if a 
    session executes LISTEN and then enters a transaction for a very long 
    time. Once the queue is half full you will see warnings in the log file 
    pointing you to the session that is preventing cleanup. In this case you 
    should make sure that this session ends its current transaction so that 
    cleanup can proceed."
    
    >
    >
    >
    > Thank you,
    >
    > Rob Brucks
    >
    
    
    -- 
    Adrian Klaver
    adrian.klaver@aklaver.com