node-postgres

node-postgres copied to clipboard

It would be useful, for example, to enforce that clients connect using UTC. Currently I believe you'd have to pass the string argument, and even then I think it might get stripped.

It looks like the pg-parsing library whitelists keys and probably also should be updated.

What is pg-parsing library? Such doesn't even exist :)

Sorry - parse-connection-string or some such.

Can you elaborate? I can't even understand what that means - to connect using UTC. What does UTC have to do with the database connection?

See the "TimeZone" setting here: http://www.postgresql.org/docs/9.4/static/runtime-config-client.html

Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information.

(or any of those settings)

that is typically overridden in postgresql.conf

Why try overriding this? Set the value in that file, and that's it.

Why try overriding this? Set the value in that file, and that's it.

I manage the development environments for about 20 developers, any one of which may have misconfigured their local Postgres instance. Or imagine deploying a client that runs on customer machines, any one of which may have misconfigured Postgres. Or, imagine you want to defend in depth, and even though postgresql.conf is configured correctly, you just really want to be sure, and make sure catastrophic things don't happen.

Or imagine wanting to change another value in that file, such as setting the default transaction isolation level for the session, or the statement timeout, or the lock timeout, or the locale for formatting money, without doing an extra roundtrip to the database.

If you are using Native Bindings - just set PGTZ variable.

The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

from here: http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES

We're blocked on using native bindings because of https://github.com/brianc/node-postgres/issues/972. Furthermore, that wouldn't help with any of the other settings - say the statement_timeout or lock_timeout or default_isolation_level, which I imagine should also be settable.

Do you want to do a PR for this?

I'm not sure I'll have time to in the near future. I just wanted to observe this for the moment, as I tried to do it and observed that it would be difficult with the current configuration settings. Most important for me is to be able to set the timezone, the other things would also be nice.

See also #850

Another use case for why you might want to be able to set run-time parameters is lock_timeout. A decently common pattern for a webserver would be to have TWO connection pools: one for real-time queries and one for long-running queries for background work. For the real-time pool we'd want to set lock_timeout / statement_timeout to something much lower than the long-running pool.

Most of the "runtime config" (https://www.postgresql.org/docs/12/runtime-config-client.html) can't be set in the DSN but can be sent in the startup message (196608, https://www.postgresql.org/docs/12/protocol-message-formats.html). This is what lib/pq in Go does.

It looks like you are already sending a startup message: https://github.com/brianc/node-postgres/blob/54b87523e29ea53379d7b9a26e45f83886f371af/packages/pg-protocol/src/serializer.ts#L23

UPDATE: There is already explicit support for statement_timeout: https://github.com/brianc/node-postgres/blob/54b87523e29ea53379d7b9a26e45f83886f371af/packages/pg/lib/client.js#L403-L405

---

Another update: this is possible TODAY using options. To verify, I used extra in TypeORM:

    extra: {
      options: '-c lock_timeout=400ms -c TimeZone=America/Los_Angeles',
      statement_timeout: 500,
    },

which translates to passing options and statement_timeout directly to pg.Pool or pg.Client. Then ran

  const lockTimeout = await connection.manager.query('SHOW lock_timeout')
  console.log(['lockTimeout', lockTimeout])
  const statementTimeout = await connection.manager.query('SHOW statement_timeout')
  console.log(['statementTimeout', statementTimeout])
  const timezone = await connection.manager.query('SHOW Timezone')
  console.log(['timezone', timezone])

which produces

query: SHOW lock_timeout
[ 'lockTimeout', [ { lock_timeout: '400ms' } ] ]
query: SHOW statement_timeout
[ 'statementTimeout', [ { statement_timeout: '500ms' } ] ]
query: SHOW Timezone
[ 'timezone', [ { TimeZone: 'America/Los_Angeles' } ] ]

Note that if I remove extra from my TypeORM connection options, the queries return

query: SHOW lock_timeout
[ 'lockTimeout', [ { lock_timeout: '0' } ] ]
query: SHOW statement_timeout
[ 'statementTimeout', [ { statement_timeout: '0' } ] ]
query: SHOW Timezone
[ 'timezone', [ { TimeZone: 'UTC' } ] ]

In PostgreSQL, we can set a timezone per session. It would be great if you add a timezone setting at the config level in node-postgres

@mustakimkr The preceding comment shows how to do that with options.