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

[Tatsuo Ishii]

Great! Glad to hear that. Thank you for your feedback!

> Ha! That's it. NOTIFYs are coming through right away now.
> 
> Loooovely! :-)
> 
> Thank you so very much for your prompt fix.
> 
> With much appreciation,
> 
> Holger
> 
> On 1/02/20 12:56 pm, Tatsuo Ishii wrote:
>>>>> 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
>>>>
>>>
>