Prepared statements are not prepared if rolled back

[IvanSanchez]

In one of my apps, I have the following scenario:

• Start a transaction
• Run prepared statement A, ignoring errors
• Run prepared statement B, ignoring errors
• Run prepared statement C, ignoring errors
• End the transaction, letting PostgreSQL roll back automatically if it needs to
• Wait a few seconds, then do the whole thing again.

The problem is: if a transaction block like this fails in prep statement A the first time the loop runs, then statements B and C will not exist in PostgreSQL, but node-postgres will assume they exist.

I've written down an example of this behaviour in a gist: https://gist.github.com/IvanSanchez/229feee233e59b32d76b

I expect node-postgres to somehow not assume that a prepared statement has been prepared if a rollback happened.

[brianc]

Easiest and probably best behavior in this case would be to clear the entire prepared statement cache off of a client if any error is returned from the backend to that client.

[IvanSanchez]

Do you mean the node-postgres cache (names of prepped statements), or the PostgreSQL prep statements themselves?

I ask because I'm trying to hack around the issue, and clearing node-postgres' list of prepped statements makes me run into SQL error "42P05" AKA "Prepared statement already exists".

[brianc]

I mean node-postgres cache on the client. The client bypasses the "parse" step in the server if you supply a query name to the client & the client has already prepared that statement once.

There are a few ways to mitigate this...the first one being node-postgres should probably do this internally...but beyond that...

1. dispose of the client and create a new one whenever you hit an error on the backend - not really a bad idea.
2. reset the client.connection.parsedStatements object...like so:

var q = client.query('blah', function(err, res) {
  if(err) {
    client.connection.parsedStatements = {}
   //normal error handling goes here
  }
});

[brianc]

I should have this fixed by Monday! sorry for the delay - I've been really busy & sometimes issues slip through the cracks.

[brianc]

I got a pull request up to fix this 🏆