Dupicate of notification payloads in PostgreSQLConnection notifications

[Gambley1]

I've faced a strange behaviour using your stormberry package. Trying to access to the Database.connection() instance of PostgreSQLConnection makes it impossible to completely use it. For some reason, it is always closed, therefore there is no chance to get access to notifications and messages of connection instance, therefore I can't get any notifications due to closed connection. It is really strange untill the point that everything internaly inside database works perfectly. Every query or transaction runs without any problems, using database directly, without connection().

If you say that I should try to just use database.open(), unfortunately, it doesn't work for me. Because, as I said before, query and other methods runs flawlesy, therefore connection is already oppened and I can't open already opened connection, but directly trying to use connection() is not working, it is always closed for some reason.

I tried to overcome this issue by manually creating a PostgreSQLConnection instance with the same port, host and other fields for the connection. And here, I tried to open and create new instance of it when it's closed, but it lead to issue with too many connected connections, as because I couldn't open PostgreSQLConnection internaly, as it could be done with Database in stormberry, therefore, trying to continuosly reopen or reinit the connection instance in many files led to that problem. I had a lot of attempts to extend myself and solve this issue overcomming it, but I have no more idea how to fix it.

Why Database.connection() instance can be always closed? How I can fix that and directly use database connection in order to have access to the notifications without manuall creating PostgreSQLConnection over an over again that finally leads to another issue with a lot of opened and connectied connection. Untill then, I hope your understood the issue and my problem. If something is unclear, please let me know and I will try to provide more details and context. Thanks!

[Gambley1]

Well, I have once more time tried to deeply investigate princeples of working database and connection in it inernally. I can clearly say that it was my fault trying to using connection() instance inside Database class. I asked why it was always closed but it can not be actually opened, because, as i see it now this connection() instance is only use in _tryOpen() method that simply takes this connection() and use it as a new instance to reinit _cachedConnection and establish connection, therefore there was no way to use connection() inside Database in order to have an access to notifications, messages and etc.

Now I have new question, how I can actually get access to the _cachedConnection inside Database to use notifications and other features? As I see it, there is no way to get an access to the _cachedConnection, what to do so?

[Gambley1]

Well, I have once more time tried to deeply investigate princeples of working database and connection in it inernally. I can clearly say that it was my fault trying to using connection() instance inside Database class. I asked why it was always closed but it can not be actually opened, because, as i see it now this connection() instance is only use in _tryOpen() method that simply takes this connection() and use it as a new instance to reinit _cachedConnection and establish connection, therefore there was no way to use connection() inside Database in order to have an access to notifications, messages and etc.

Now I have new question, how I can actually get access to the _cachedConnection inside Database to use notifications and other features? As I see it, there is no way to get an access to the _cachedConnection, what to do so?

Well, haha, I have fixed this problem😁 The way I did it was preety simple, I made a fork on original stormberry repo and added currentConnection getter method and simply returned _cachedConnection instance.

Now I want to ask for the solution not a problem, but not desired behaviour. Even though I have managed to establish single connection without multiple flows or duplications, therefore I thought I fixed that problem. The main idea is that when I listening to PostgreSQLConnection notifications everytime it notifies about new updated or inserted row(don't matter) it give me multiple duplicated payloads, the number of it always different, there can be 4, 8, 10, 15 duplicated payloads of notification. I don't actually know why it is happening.

Here is the function that notifies about updates on "Order details" table: CREATE OR REPLACE FUNCTION notify_user_order_status_notification_change() RETURNS TRIGGER AS $$ BEGIN PERFORM pg_notify('user_order_status_changed', row_to_json(NEW)::text); RETURN NEW; END; $$ LANGUAGE plpgsql;

and here is the trigger that executes this function: CREATE TRIGGER user_order_status_change_trigger AFTER UPDATE ON "Order details" FOR EACH ROW EXECUTE FUNCTION notify_user_order_status_notification_change();

Maybe the issue can be in this function or trigger that leads to duplicating the same row of updated row in the table. I hope it make sense and everything clear until then. If you need more context and details, please let me know. In advance, appreciate any support!