|
|
If there's any difference regarding LISTEN/NOTIFY behavior between PgAdmin and npgsql, the former uses simple protocol while the latter uses extended protocol. To confirm that, i need a pgpool log with log_client_messages = on. Can you please provide it? |
|
|
Here is a part of the log file when I use LISTEN/NOTIFY from pgadmin. Parameters of the config file: log_connections = on log_hostname = on log_statement = on log_per_node_statement = on log_client_messages = on debug_level = 1 log_error_verbosity = verbose client_min_messages = debug1 log_min_messages = debug1 listen-notify-pgadmin.png (69,990 bytes) |
|
|
The log with the provided simple test code running (LISTEN) and a NOTIFY manually triggered from pgAdmin. By the way, when I set client_min_messages > notice (e.g. debug1) the program is not able to obtain the connection. And oddly the program receive the notification but after 1 minute and 40 / 45 seconds. |
|
|
Changing Keepalive=N (e.g. 120 to 10) in the connection string has an impact on the behavior. Maybe it's related to https://www.postgresql.org/docs/12/sql-notify.html : "NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY. But it can be disconcerting if one is expecting the notification events to be delivered immediately. Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot “take back” a notification once it has sent it to the client. So notification events are only delivered between transactions. The upshot of this is that applications using NOTIFY for real-time signaling should try to keep their transactions short." |
|
|
Npgsql Keepalive https://www.npgsql.org/doc/keepalive.html: "Some clients keep idle connections for long periods of time - this is especially frequent when waiting for PostgreSQL notifications. In this scenario, how can the client know the connection is still up, and hasn't been broken by a server or network outage? For this purpose, Npgsql has a keepalive feature, which makes it send periodic SELECT NULL queries. This feature is by default disabled, and must be enabled via the Keepalive connection string parameter, setting the number of seconds between each keepalive." |
|
|
> The log with the provided simple test code running (LISTEN) and a NOTIFY manually triggered from pgAdmin. What I wanted was the log while you are using npgsql, not pgAdmin. Can you please resend the log? |
|
|
An extraction of the log file when running this code: using Npgsql; using System; using System.Threading; namespace TestListenNotify { class Program { static void Main(string[] args) { // Via pgpool var connectionString = "Host=192.168.NNN.NN0;Port=9999;Database=DB_NAME;Username=DB_USER;Password=DB_PASSWORD;Pooling=false;Keepalive=20;"; // Direct // connectionString = "Host=192.168.NNN.NN1;Port=5432;Database=DB_NAME;Username=DB_USER;Password=DB_PASSWORD;Pooling=true;Keepalive=120;"; var listenConn = new NpgsqlConnection(connectionString); try { listenConn.Open(); listenConn.Notification += (o, e) => { // Via pgpool -> KO // Direct -> OK Console.WriteLine($"Received notification: '{e.Payload}' at {DateTime.Now.TimeOfDay}"); }; using (var cmd = new NpgsqlCommand("LISTEN channel_test", listenConn)) { cmd.ExecuteNonQuery(); } // var notifyThread = new Thread(new ThreadStart(delegate { Thread.Sleep(2000); var notifyConn = new NpgsqlConnection(connectionString); notifyConn.Open(); int notificationId = 1; while (true) { using (var cmd = new NpgsqlCommand($"NOTIFY channel_test, 'test payload from simple code {notificationId}'", notifyConn)) { Console.WriteLine($"Sended notification: '{notificationId}' at {DateTime.Now.TimeOfDay}"); cmd.ExecuteNonQuery(); } notificationId++; Thread.Sleep(3000); } })); notifyThread.IsBackground = true; notifyThread.Start(); // while (true) { listenConn.Wait(); } } catch (Exception ex) { Console.Write(ex); } } } } |
|
|
and for better understanding, a screenshot of the output: output.png (61,201 bytes) |
|
|
don't really know if it could help but: wireshark-notify-compare.png (156,148 bytes) |
|
|
In my local test using pgproto it works. To find put why you have problem with it, I need more detailed pgpool log with log_min_messages = debug5. Can you please provide it? |
|
|
Here is it (pgsql version 9.5 but we have the same behavior with version 12): |
|
|
I found an oversight in Pgpool-II 4.1. The notification messages was surely received by Pgpool-II: Jan 10 09:25:20 gomsgpool-0 pgpool[11411]: [4200-3] 2020-01-10 09:25:20: pid 11411: LOCATION: pool_process_query.c:3282 Jan 10 09:25:20 gomsgpool-0 pgpool[11411]: [4201-1] 2020-01-10 09:25:20: pid 11411: DEBUG: processing backend response Jan 10 09:25:20 gomsgpool-0 pgpool[11411]: [4201-2] 2020-01-10 09:25:20: pid 11411: DETAIL: received kind 'A'(41) from backend Jan 10 09:25:20 gomsgpool-0 pgpool[11411]: [4201-3] 2020-01-10 09:25:20: pid 11411: LOCATION: pool_proto_modules.c:2714 but was buffered and never sent to frontend. Can you please try attached patch to fix the issue? notification-fix.diff (1,566 bytes) |
|
|
Thank you, It works with the patch. |
|
|
Thank you for the feedback. May I close this issue? |
|
|
Yes you can. |
- Anonymous
