document how to run query and retrieve results

[dberardo-com]

node-postgres/packages/pg-protocol/src/serializer.ts

Line 1 in e25428c

import { Writer } from './buffer-writer'

for some weird reason i am confronting myself with the low level pg-protocol API in typeorm. i need to fire a query and wait for results, or alternatively i need to listen to the onconnect event when the client reconnects.

i am doing connection.query("select 42;") but this is not a promise ... so i am not sure how to understand that the connection is working fine

[brianc]

Are you asking about a type-orm api? This project is for the actual postgres driver that type-orm uses...and the documentation is at https://node-postgres.com

[dberardo-com]

no i was just giving some context. i am interacting with the low lever postgres API within a typeorm callback, but thats not relevant. i need to check if the connection to the DB is ok and i am using the .query method of the pg-protocol for that. but i am not able to get any return from the query, not exception. i would like to figure out if the connection with the DB is up, and cant figure out a way

[charmander]

Why are you using pg-protocol for that instead of the pg.Client?

[dberardo-com]

yeah, so basically i am trying to implement a workaround to the missing feature of typeorm, whereby the DataSource can auto-reconnect to the DB in case the connection drops .(i really wonder why this hasnt been implemented by typeorm people ... ) to do this i ma trying to hook in the typeorm datasource lifecycle using the callback poolErrorHandler, which gets invoked when there is an error in the connection: typeorm/typeorm#6350

now it turns out the this callback gets the db connection passed as a second argument:

poolErrorHandler: async (err, client) => {}

but this second parameter is not documented in their official doc, so i am not fully sure about its type (https://orkhan.gitbook.io/typeorm/docs/data-source-options#postgres-cockroachdb-data-source-options(. the only thing i know for sure is that the "client" object has a connection attribute which implements the methods of the serializer object: https://github.com/brianc/node-postgres/blob/master/packages/pg-protocol/src/serializer.ts

that's why i am able to use the client.connection.query function, which brings me to this issue.

should i check what other mehods does "client" implement? perhaps i can find some higher-level method to interact with the database in there ? any specific method i should look for ?

[charmander]

In your example poolErrorHandler signature, client is the pg.Client object that you would use, but you should never use a client that has emitted an error event – its connection will be in an undefined state.

But if TypeORM is using pg.Pool as a connection pool, it should already reconnect automatically without you needing to do anything. poolErrorHandler is only called on idle clients anyway, and pg.Pool automatically removes and replaces clients that emit error.

The right thing to do is retry queries when they reject with an error object that you can recognize as (or are willing to treat as) transient.

[dberardo-com]

well i dont know what to say, the fact is that typeorm does not automatically reconnect the client so i have to hack into the system.

adding the client.connection.query part did the job, i dont know why but this perhaps forces the socket to retry the connection ... i have absolutely no log so i cant say whats going on.

as for the pg.client ... is there any way i can change its configuration within the error callback so that it starts performing auto-reconnect automatically ?
Or would it be possible for example to start an infinite loop in the callback where it tries to reconnect the client until it succeeded, IDK, for example try { client.reconnect() } catch {} or so ?

[charmander]

adding the client.connection.query part did the job, i dont know why but this perhaps forces the socket to retry the connection ... i have absolutely no log so i cant say whats going on.

This definitely isn’t a correct fix. If you’re seeing broken connections remain in the pool, you should report the original issue as a bug, with all that entails (the minimum amount of code and environment details necessary to reproduce the issue).

as for the pg.client ... is there any way i can change its configuration within the error callback so that it starts performing auto-reconnect automatically ?

A pg.Client instance can only be connected once, by design. You replace it with a new instance if you’re using pg.Client manually without pg.Pool (which doesn’t seem to be the case).

[boromisp]

I've skimmed the TypeORM pg integration. They seem to be using the pool correctly.

---

They use pool.connect((err, client, release) => {}) with a callback to obtain the clients:
https://github.com/typeorm/typeorm/blob/e7649d2746f907ff36b1efb600402dedd5f5a499/src/driver/postgres/PostgresDriver.ts#L1169-L1200

They call release(err) on the client's error event handler:
https://github.com/typeorm/typeorm/blob/e7649d2746f907ff36b1efb600402dedd5f5a499/src/driver/postgres/PostgresQueryRunner.ts#L110-L128

They call release() after every query, failed or successful, e.g.:
https://github.com/typeorm/typeorm/blob/e7649d2746f907ff36b1efb600402dedd5f5a499/src/data-source/DataSource.ts#L524-L542

Only the first call to release will be executed:
https://github.com/typeorm/typeorm/blob/e7649d2746f907ff36b1efb600402dedd5f5a499/src/driver/postgres/PostgresQueryRunner.ts#L138-L154

I believe the error event handler should be executed before the finally after the query.

---

There could be an issue with holding onto a failed client for too long, and trying to execute queries on a dead connection.

I'm not familiar enough with their API to know if you could use TypeORM in a way that it creates a new query runner for every query, but doing that should work around the problem.

---

The correct fix should go somewhere here: https://github.com/typeorm/typeorm/blob/master/src/driver/postgres/PostgresQueryRunner.ts, or wherever the query runners are used. You can't just substitute a new pg.Client without breaking transactions, custom driver initialization, etc. And you won't be able to anyway.