Record insert or edit i have some errors (Cannot read property 'handleCommandComplete' of null or Cannot read property handleDataRow).

PG Version ^8.6.0,
Node Version 12.16.1,
Typeorm Version ^0.2.32.

After changes i was solve my problem in node_modules =>pg=>lib=>client.js
_handleDataRow(msg) {
if (this.activeQuery !== null) { // new line added
this.activeQuery.handleDataRow(msg)

}

_handleCommandComplete(msg) {
if (this.activeQuery !== null) { //new line added
this.activeQuery.handleCommandComplete(msg, this.connection)
}

… meaning they often duplicate each other, can’t be linked to, and cause the HTML to be invalid.

Example from https://node-postgres.com/api/result/#properties:

<h2 id="properties" class="css-jehsw0">properties</h2>
<h3 id="[object Object]" class="css-hf8i38"><code class="css-trsm9o">result.rows: Array&lt;any&gt;</code>

and from https://node-postgres.com/api/client/#client.query:

<h2 id="client.query" class="css-jehsw0">client.query</h2>
<h3 id="[object Object], - text, optional values, and callback." class="css-hf8i38">
  <code class="css-trsm9o">client.query</code> - text, optional values, and callback.
</h3>

I wasn't sure if Pool.ending can be used externally but if so can we document it here?

https://github.com/brianc/node-postgres-docs/blob/master/content/api/1-pool.mdx#properties

Per https://node-postgres.com/api/pool#pool.end I use express-graceful-exit with a callback that calls Pool.end() but only if the pool's ending value is false, e.g.:

function shutdown (app, server, exitCode = 0) {
  gracefulShutdown(app, server, exitCode > 0 ? 'error' : 'info', () => {
    try {
      if (!pgPool.ending) {
        log.info(`Shutting down DB connection pool; waiting: ${pgPool.waitingCount}, idle: ${pgPool.idleCount}`)
        pgPool.end(error => {
          if (error) throw error
        })
      }
    } catch (err) {
      log.error(err, 'An error occurred while shutting down the DB connection pool.')
    } finally {
      setTimeout(() => process.exit(exitCode), 2000)
    }
  })
}

Using pgPool.ending means I don't need to track that myself but I'm unsure if it's a "supported" property to rely on outside of the Pool object itself. If it is, can we document it?

I noticed these docs miss completely mocking the API & testing.

Due to the import / require time setup a lot of the examples give, this made me scratch my head a bit when using proxyquire to patch pg module import.

I've managed to get some bits working and wondered if it was worth documenting, or if the project goals require the standing up of a network service (postgres) when testing this module.

Either way it would be nice to get some docs and as I'm having a play at the moment, I wanted to know if you'd be open to a PR

Your website is just DOWN

the maxUses option was added in pg-pool 3.1, but it's still not mentioned in the docs.

brianc/node-postgres#2157

Couldy you please explain what the difference is between pool end and pool release?

Hi,

I'm trying to use pg to connect to and query a PostgreSQL DB. Using a node.js environment. upon loading the page in the browser, I receive this "uncaught reference error : process is not defined" in pg/lib/defaults.js on line 8.

Package built with yarn,

Any hint ?
Thanks

Is it possible to create a connection pool to an instance, but per request, change the DB it uses for the query?
I have a Saas Application with many DB's on a single instance. A given request needs to connect the user to their specific DB.
Since connections are per server, not DB, this seems that it should be supported?

Trying to update a table with an id (serial primary key) that does not exist.
Connecting directly to the db returns feedback on success/failure:
# UPDATE public_workspaces set name='JOHN' where id = 5;
UPDATE 0
#UPDATE public_workspaces set name='JOHN' where id = 2;
UPDATE 1

Yet pg returns the same result structure (see below). Is there a way to check if the UPDATE has succeeded?

server_1 | Result {
server_1 | command: 'UPDATE',
server_1 | rowCount: 0,
server_1 | oid: null,
server_1 | rows: [],
server_1 | fields: [],
server_1 | _parsers: undefined,
server_1 | _types: TypeOverrides {
server_1 | _types: {
server_1 | getTypeParser: [Function: getTypeParser],
server_1 | setTypeParser: [Function: setTypeParser],
server_1 | arrayParser: [Object],
server_1 | builtins: [Object]
server_1 | },
server_1 | text: {},
server_1 | binary: {}
server_1 | },
server_1 | RowCtor: null,
server_1 | rowAsArray: false
server_1 | }

Regarding the pg.Client doc, there are several instances of:

const { Client } = require('pg').Client

which I think is supposed to be more like const { Client } = require('pg') as it appears in other places.

The pg.Cursor docs aren't clear enough when we (the users of the pg and pg-cursor packages) should explicitly call the cursor.close method to free the resources.

As somebody who's not too experienced with Postgres or SQL cursors in general, I had to read through Postgres docs, to now imagine that cursors are automatically closed and their resources cleared, when they reach the end, though I'm in no way certain of it, that's just my current interpretation of what I've read.

The pg.Cursor API docs show an example of reading the first 100 rows from the cursor, then closing it and releasing the client after.
And the .close method is described as "Used to close the cursor early. If you want to stop reading from the cursor before you get all of the rows returned, call this."

The error handling with cursors is not documented well enough. There are 2 examples, both callback based.
The first one doesn't account for and handle errors at all (for brevity sake, I imagine).
The second one just throws in case of an error, which, I imagine, would stop the entire process and thus release the client anyway.

I'm using the cursor as part of a bigger solution where I don't want to kill the whole process and I want to handle the error gracefully. Also I'm using the async/await syntax.
This involves acquiring the client then wrapping the whole logic into try/finally, where I release the client in the finally block.
Should I call cursor.close() inside a catch block in case of error, or do errors close the cursor internally? Should I call it in finally? Is it unnecessary since I want to read the whole cursor anyway?

I did some inspecting of the pg-cursor package source code, but I can't really be 100%. Also, whenever I find myself having to inspect source code of some dependency, I imagine that's an indicator of sub-optimal documentation.

It would be nice to not only have the callback API in every example.

Would you be open to a PR to

• allow switching between callback & promise API for examples
• provide promise API examples

with ssl true , it doesnot allow me to connect , i receive the below error

unable to get local issuer certificate

I'm developing a Multitenancy application and i need a new connection or pool for any user to connect on he's company database and I'm getting a error: permission denied for table

Using these connection string
let connectionString = 'postgresql://$_tenantDB_$_user:$_secretDB_$@localhost:5432/$_tenantDB_$';
I replace the selected chars and i get a successfull connection
Client { _events: [Object: null prototype] {}, _eventsCount: 0, _maxListeners: undefined, connectionParameters: ConnectionParameters { user: 'tenant001_user', database: 'tenant001', port: 5432, host: 'localhost', binary: false, options: undefined, ssl: false, client_encoding: '', replication: undefined, isDomainSocket: false, application_name: undefined, fallback_application_name: undefined, statement_timeout: false, idle_in_transaction_session_timeout: false, query_timeout: false, connect_timeout: 0 }, user: 'tenant001_user', database: 'tenant001', port: 5432, host: 'localhost', replication: undefined, _Promise: [Function: Promise], _types: TypeOverrides { _types: { getTypeParser: [Function: getTypeParser], setTypeParser: [Function: setTypeParser], arrayParser: [Object], builtins: [Object] }, text: {}, binary: {} }, _ending: false, _connecting: false, _connected: true, _connectionError: false, _queryable: true, ...
So, the _queryable its true, but I cannot make any query works on it.

I am wondering if this is typo or it's normal. For me, it could be PGDATABASE=process.env.DATABASE instead of PGDATABASE=process.env.USER in /1-connecting.mdx file.

Hey Brian,

I noticed a slight documentation error with your client example and attached my proposed fix below it.
Current:

  const client = new Client({
  host: 'my.database-server.com',
  port: 5334,
  user: 'database-user',
  password: 'secretpassword!!',
})

Fix:

  const client = new Client({
  database: 'my.database-server.com',
  port: 5334,
  user: 'database-user',
  password: 'secretpassword!!',
  connectionString: 'postgres://user:password@host:port/database',
})

Hope this helps!

I'm excited about brianc/node-postgres#2345, but it's currently undocumented :)

I have a use case where I need to start a Cursor outside of QueryStream but want to use QueryStream to consume the cursor. It would be nice to pass a Cursor instance in the constructor:

Cursor myCursor = fetchACursor()
QueryStream stream = new QueryStream(myCursor, opts)

Thanks for your consideration.

I liked some of the color scheme of the old site a bit more

perhaps integrate some of that here

My team has a postgres instance in google cloud, and ran into trouble connecting to the database after upgrading to pg 8.0.3 from 7.x

After reading the changelog, we were able to connect by adding rejectUnauthorized : false in the ssl settings

ssl: {
    **rejectUnauthorized: false,**
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

This raised some red flags with us, and one of the developers found the setting for host in the ssl object, which works as expected

ssl: {
    host: "google-cloud-project:postgres-instance",
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

It would be helpful to add this to the documentation page.

I figured I would see a branch or tag for v7, but I don't...

https://github.com/brianc/node-postgres-docs/blame/master/content/api/2-client.mdx#L13
looks to be out of date and thus confusing. @types/pg contain this spec:

export interface ClientConfig {
    user?: string;
    database?: string;
    password?: string;
    port?: number;
    host?: string;
    connectionString?: string;
    keepAlive?: boolean;
    stream?: stream.Duplex;
    statement_timeout?: false | number;
    ssl?: boolean | ConnectionOptions;
    query_timeout?: number;
    keepAliveInitialDelayMillis?: number;
}

Hey Brian - the PG library is fantastic!

This is a pretty basic problem, but after some research, I am still scratching my head around what's going on with the WHERE condition not being respected.

Here's the query:

    const sql = `SELECT id FROM employees WHERE LOWER(email) = LOWER($1);`;

    const { rows } = await query(sql, [email]);

The email parameter is being passed in the function, but when this executes in the app, it returns no records. But when I run the following in DBViz it does work...so it must be something with the LOWER function wrapping the $1 parameter.

DBViz query:
SELECT id FROM employees WHERE LOWER(email) = LOWER('[email protected]');

I could .toLowerCase() the email variable prior to passing it to the query params, but would love for the DB to handle all this. Any ideas?

Thanks for the help!

Hello,

I wish to know if there is a way to specify the authentication method using any configuration within the newly created Pool object which lets me set the authentication method for a postgres connection. Can we change contents of pg_hba.conf ?

I found about this command here: https://stackoverflow.com/questions/17633422/psql-fatal-database-user-does-not-exist/17936043#comment26736790_17633422

None of the examples work unless you create the database first

When I batch insert multiple rows, how to write parameterized sql?
Thanks a lot

I'm working with Docker on W10 and it takes DB a bit longer to start than my app container and I was getting Error: connect ECONNREFUSED... from client.connect(). I wanted to solve it with a timer loop with try to connect every 1 second again, but each other call of client.connect() returned an error Client has already been connected.... And that was not true, DB still hasn't started and any query called on such client ended with an error. What I had to do was to close the client and create a completely new instance on which I could try to call connect again.

My points are:

• connect should be allowed to be called multiple times on one instance if the previous call ended with connection error
• even if that is not wanted, the error message should not say Client has already been connected, when it is not true (again DB has not yet started)

When I retrieve a profile (shown below) from the profiles table you will notice that the 2 enumerated arrays lookingForCountries and lookingForReligions haven't been converted to javascript arrays, but the character varying array photos has.
{
nickname: 'Cool Man Test Account',
description: 'I am the coolest man alive',
age: 40,
location: 'Oxford',
gender: 'Male',
sexuality: 'Straight',
religion: 'Christianity',
alcohol: 'Yes',
smoking: 'No',
lookingForGender: 'Female',
lookingForSexuality: 'Straight',
country: 'Any',
interests: '0',
lookingForCountries: '{"United Kingdom",Albania}',
lookingForReligions: '{Agnostism,Atheism}',
photos: [ '', '', '', '', '' ],
lookingForFromAge: '34',
lookingForToAge: 40
}
I can provide all of the table definitions, enum definitions and javascript code if you like, but this should be easy to replicate as you just create an enum type, create a column of enum array and try to retrieve the table row from Javascript.

I got an express API, and i got this when i was testing a post route that interact with database. I reviewed my env file and everything seems to be right. I saw that there was an issue about this problem, but it was closed.
Node version: v12.16.2
pg version: 8.3.3
mac os Catalina version 10.15.6

The only thing either of these libs are being used for is the animation expand/collapse toggle on mobile when the top nav is collapsed due to screen size. This is probably 5-10 lines of code in react & could remove a lot of client-side libraries. The libraries are likely cached in the browser since they're used in so many places, but still...might be nice. 😄

Hi. I'm wondering if there is any recommendation for whether it makes sense to use a client vs a pool with an Amazon RDS Proxy connection. My understanding is that the point of using a proxy vs a direct connection to an RDS database is that the proxy intrinsically builds in pooling of connection to the database. So, I'm wondering if it makes sense to layer a client on top of the proxy instead of a pool so that we dont have pools on pools?

I have a issue with trying to use this on Amazon Lambdas. When the Lambda runs first it connects and initiates the promise, but once it runs again to recieve the promise it tries to connect again resulting in this sweet little error message.

I've tried to use this solution to no it skips the first retry but eventually skips the if once enough ms has passed. Is it possible to use this package with lambdas?
async query(query, params) { this.connection() // console.log(this.client) if (!this.client.connectionParameters.user.length > 0) { this.connection() } else { console.log('client is all ready connected, skipping connection') } const result = await this.client.query(query, params) this.endConnection() return result.rows }

{
"errorType": "Runtime.UnhandledPromiseRejection",
"errorMessage": "Error: Client has already been connected. You cannot reuse a client.",
"reason": {
"errorType": "Error",
"errorMessage": "Client has already been connected. You cannot reuse a client.",
"stack": [
"Error: Client has already been connected. You cannot reuse a client.",
" at Client._connect (/opt/nodejs/node_modules/pg/lib/client.js:85:19)",
" at /opt/nodejs/node_modules/pg/lib/client.js:157:12",
" at new Promise ()",
" at Client.connect (/opt/nodejs/node_modules/pg/lib/client.js:156:12)",
" at Battery.connection (/var/task/src/resources/postgress-db.js:26:24)",
" at Battery.query (/var/task/src/resources/postgress-db.js:12:10)",
" at Battery.fetchBatteryStatus (/var/task/src/resources/battery.js:24:30)",
" at prepareResponse (/var/task/src/handlers/users/battery/get.handler.js:51:26)",
" at processTicksAndRejections (internal/process/task_queues.js:97:5)"
]
},
"promise": {},
"stack": [
"Runtime.UnhandledPromiseRejection: Error: Client has already been connected. You cannot reuse a client.",
" at process. (/var/runtime/index.js:35:15)",
" at process.emit (events.js:315:20)",
" at process.EventEmitter.emit (domain.js:483:12)",
" at processPromiseRejections (internal/process/promises.js:209:33)",
" at processTicksAndRejections (internal/process/task_queues.js:98:32)"
]
}