[pgpool-general: 6839] Re: NOTIFY/LISTEN delay explanation?

[Tatsuo Ishii]

>>> Interesting. Does it also happen if you try it using psql to LISTEN?
>>> 
> 
> Good question. Here 's what I have tried to answer it so far:
> 
> 1) Two apps are connected to PGPOOL displaying their usual table
> content.
> 
> 2) I'm logged straight into PG using PSQL and issue a LISTEN, then an
> INSERT command. In PSQL, I see the "asynchronous notification" message
> right away. The apps display reception messages a few seconds later.
> 
> 3) I'm using PSQL to log into PGPOOL and issue a LISTEN, then an
> INSERT command. Same result as in 2). I see the "asynchronous
> notification" message right away. The apps display receptions a few
> seconds later.
> 
> 4) Lastly, I recompiled the app to use the PG port directly. Then
> repeat 2) and the apps displayed the NOTIFY reception message the very
> moment my finger hit the enter key to send the INSERT command.
> 
> So, there seems to be a lag in NOTIFY propagation between PGPOOL and
> the connected apps. PGPOOL seem to receive the notifications from PG
> just fine.
> 
> Note, there were are no suspicious messages in either PG or PGPOOL log
> files whilst running these tests.

My guess is, psql uses "simple query protocol" while your apps use
"extended query protocol" (see
https://www.postgresql.org/docs/12/protocol-overview.html#PROTOCOL-MESSAGE-CONCEPTS
for the difference between them). If so, a bug recently found in 4.1
could relate to what you are seeing.

https://www.pgpool.net/mantisbt/view.php?id=573

And the fix is here.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=45eb0633d7175d03f3d0865de75bee8f0b5b213f

You can download the patch there (of course you can download 4.1
stable head, which will be the next 4.1.1 release if there's no more
fix).

Or you can wait for 4.1.1 supposed to be released on Feburary 20.

> On 1/02/20 3:40 am, Tatsuo Ishii wrote:
>>> Hi there,
>>>
>>> Just installed PgPool 4.1.0 from sources today (with SSL
>>> enabled). Using it against PostgreSQL 12.1 on Arch Linux.
>>>
>>> The PgPool configuration is a straight raw setup copied from the
>>> pgpool.conf.sample. Nothing fancy, just testing connection pooling.
>>>
>>> Here is my question.
>>>
>>> I have some rules in PG which respond to UPDATE/INSERT/DELETE to one
>>> particular table and send NOTIFY messages to connected apps. When all
>>> these apps are connected straight to Postgres (!), and some app is
>>> making changes to this table, then a NOTIFY is triggered and sent and
>>> received by all connected apps instantly. Literally, all happening at
>>> once.
>>>
>>> However, if the same apps are connected to PgPool, instead of straight
>>> to Postgres, then there is a delay of about 3-5 seconds between making
>>> a modification and the reception of the database NOTIFY.
>>>
>>> Now, from an app/deployment standpoint I am perfectly fine with that,
>>> but I would like to satisfy my curiosity as to WHY there is such a
>>> delay. I studied the config file settings, but I can not find any
>>> option that could relate to this and/or explain what is going on.
>>>
>>> Anybody out there who can shed some light?
>> Interesting. Does it also happen if you try it using psql to LISTEN?
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>