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

[Holger Jahn]

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

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
>