Can't immediately receive multiple notificatio

Question

Today i wrote the following code that works with PostgreSQL C# library named Npgsql:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace PostgreSQLNotificationsTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=my_password;Database=helper_db.psql;SyncNotification=true"))
            {
                conn.Notification += OnNotification;
                conn.Open();
                using (var command = new NpgsqlCommand("listen notifytest;", conn))
                {
                    command.ExecuteNonQuery();
                }
                Console.ReadLine();
            };
        }

        private static void OnNotification(object sender, NpgsqlNotificationEventArgs e)
        {
            Console.WriteLine("event handled: " + e.AdditionalInformation);
        }
    }
}

Then i do the following

createdb.exe -h 127.0.0.1 -p 5432 -U postgres -W helper_db.psql

psql.exe -h 127.0.0.1 -p 5432 -U postgres -W helper_db.psql

create table helper(first text primary key, second integer);

CREATE OR REPLACE FUNCTION nf() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('notifytest', format('INSERT %s %s', NEW.first, NEW.second));
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER any_after AFTER INSERT OR UPDATE OR DELETE ON helper FOR EACH ROW EXECUTE PROCEDURE nf();

insert into helper values('first', 10), ('second', 20);

In C# project i get the following output:

event handled: INSERT first 10

There's no "event handled: INSERT second 20". Only when i do the next operation like "delete from helper", i receive it.

Why?

Answer 1

UPDATE (2015-02-27): This bug is already fixed in our master branch on github. Note that master branch is our upcoming 3.0 version and may have some bugs we are working on. Please, give it a try and let me know if it works for you.

This is a bug in Npgsql. We are working to fix it. Check this pull request for more info about it: https://github.com/franciscojunior/Npgsql2/pull/46 in special the commit: https://github.com/glenebob/Npgsql2/commit/98fd469048a20119755777ce3a8ffc4397f4a114

But we are trying another approaches in order to fix this problem. Note that this problem only occurs when you send multiple notifications in a very short period. If you keep a space of 1 or 2 seconds between notifications, they are delivered correctly.

As soon as we get it fixed I'll update my answer here.

Sorry for all the inconvenience this problem may have caused.

Answer 2

Unless your client library supports checking the network socket for buffered data, the only way to receive notifications is to trigger some other activity on the socket.

Many applications periodically send an empty query string ("") to do this.

If the client library supports it and you are not using SSL, it might be possible to periodically call some kind of checkForNotifications() function on the connection. This is possible in PgJDBC, but I don't know nPgSQL, so I can only advise you to check out the documentation for that.