Question: Does `sql.listen` reconnect?

[lukeed]

Hey, as the title says, I'm wondering how any errors within sql.listen are handled or should be handled. Since there's no Pool and connections are created lazily (except for sql.listen) I'm curious what the approach here should be.

Thanks!

[porsager]

That's a very nice observation @lukeed !

Currently there's no "out of bound" error handling or reconnect on sql.listen.

It should definitely reconnect automatically!

The initial sql.listen call for a channel will return the promise from the actual query sent. sql.listen will then only throw if there's a connection error initially or an error in the channel name. That means we should be able to reconnect automatically if the initial query succeeded since the connection and channel name should work.

I can't think of any errors that would be of interest on the listener connection except for those related to listen queries. Can you?

[lukeed]

:) I'm thinking of standard pooling arrangements. It's somewhat straightforward to attempt new connections until an initial sql.listen succeeds. But if the Postgres server dies (after listening) there doesn't seem to be an easy way to be aware of that and/or start listening again once the DB comes back up.

[porsager]

Right, I think postgres could handle that by automatically reconnecting the listener connection if any listeners were added, but I guess you might still want to be aware of that situation in userland, if for instance the server doesn't come up within a reasonable amount of time... Maybe an options object as 3rd arg with config for reconnection and an onclose handler..

I've got a little test with a reconnect on close implementation here.. 5c96875
Would probably need some kind of backoff / timeout for the reconnect.

[FrigoEU]

Hey @porsager,

Thanks for all the work on this! The package works really well. I've been trying out this reconnection functionality and found that for some errors (the one I found is ECONNRESET, when I kill the database server manually) the onclose handler of the Connection doesn't get called. Reason is that in the onclose of the postgresSocket, the error is an instance of Error and so it doesn't call close() but it calls error(). This makes it very difficult to do reliable reconnecting, or even a reliable callback towards the user when the "listen" session is killed.

I'm not sure if it's intentional that some fatal errors in postgresSocket don't call the onclose handler of the Connection?

Simon

[porsager]

Hi @FrigoEU that sounds like a bug, so thanks a lot for bringing it up, I'll try to see if I can reproduce :)

[FrigoEU]

Thanks for the quick response!

How I reproduce it:

1. Set a console.log in the onclose function of postgresSocket
2. Start a connection (eg: set up a .listen session)
3. Stop postgresql
4. I get a few errors in sequence: "PostgresError: the database system is shutting down", then "Error: write CONNECTION_CLOSED localhost:5432", then Error: "read ECONNRESET"

(I tried this on v2.0.0-beta.10)

[uasan]

Yes, this problem is reproducible if the connection was terminated externally.
In this case (if there are listeners) the client should automatically reconnect and restore all listener.

[porsager]

Yeah, I'm currently improving the connection handling, and should have a fix up for this and a couple of other issues today / tomorrow.

[uasan]

@porsager, any update progress?

[porsager]

@uasan I ended up in a pretty deep rabbit hole with these changes, since I was also doing improvements to the multi host failover connections and backoff with retries. The current connection implementation needed a touch up to handle these cases properly, but I'll see if I can backport a fix for this specific issue for now...

[uasan]

I can backport a fix for this specific issue for now...

Yes it was good, this problem, on AWS RDS is reproduced every day, because at night the connections are closed by timeout

[porsager]

Would you mind giving https://github.com/porsager/postgres/tree/listen-reconnect a shot?

[uasan]

Ok, I testing this branch