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

[Holger Jahn]

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
>>>
>>