3

Is there anything in the modern PostgreSQL connection protocol that would indicate the server version?

And if not, is there a special low-level request that an endpoint can execute against an open connection to pull the server details that would contain the version?

I'm looking at a possible extension of node-postgres that would automatically provide the server version upon every fresh connection. And I want to know if this is at all possible.

Having to execute SELECT version() upon every new connection and then parsing it is too high-level for the base driver that manages the connection. It should be done on the protocol level.

6
  • 2
    You don't really need to parse the output from version(). You can use show server_version_num or show server_version to get a more "friendly" number. The underlying Postgres protocol does provide the server version and the connection object can return that without a query: postgresql.org/docs/current/… but I don't know if that is available from within Node.js Commented Nov 6, 2019 at 8:22
  • It was just suggested elsewhere that the start-up message might contain the version. Commented Nov 6, 2019 at 8:25
  • Then on the other hand, running show server_version after the connection was established, isn't really such a big overhead. Commented Nov 6, 2019 at 8:27
  • @a_horse_with_no_name We should not need to execute any query, the server should provide the version automatically, and I believe it does, just need to figure out how exactly. Commented Nov 6, 2019 at 8:27
  • 1
    @a_horse_with_no_name I have found out that it already does :) Commented Nov 6, 2019 at 9:15

1 Answer 1

5

After a bit of research, I found that PostgreSQL does provide server version during connection, within the start-up message.

And specifically within node-postgres driver, we can make Pool provide a custom Client that handles event parameterStatus on the connection, and exposes the server version:

const {Client, Pool} = require('pg');

class MyClient extends Client {
    constructor(config) {
        super(config);
        this.connection.on('parameterStatus', msg => {
            if (msg.parameterName === 'server_version') {
                this.version = msg.parameterValue;
            }
        });
    }
}

const cn = {
    database: 'my-db',
    user: 'postgres',
    password: 'bla-bla',
    Client: MyClient // here's our custom Client type
};

const pool = new Pool(cn);

pool.connect()
    .then(client => {
        console.log('Server Version:', client.version);
        client.release(true);
    })
    .catch(console.error);

On my test PC, I use PostgreSQL v11.2, so this test outputs:

Server Version: 11.2

UPDATE - 1

Library pg-promise has been updated to support the same functionality in TypeScript. And you can find a complete example in this ticket.

UPDATE - 2

See example here:

// tests connection and returns Postgres server version,
// if successful; or else rejects with connection error:
async function testConnection() {
    const c = await db.connect(); // try to connect
    c.done(); // success, release connection
    return c.client.serverVersion; // return server version
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.