5 ms·

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statem

by latch 3y ago

If you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated. But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection). I agree that sentence didn't make any sense. So I looked at the code (1) and what they mean is that they'll use a named prepared statement automatically, essentially caching the prepared statement within PG and the driver itself. They create a signature for the statement. I agree, this is opinionated! (1) The main place where the parse/describe/bind/execute/sync data is created is, in my opinion, pretty bad code: https://github.com/porsager/postgres/blob/bf082a5c0ffe214924cd54752a7aeb4e618d279b/src/connection.js#L184 https://github.com/porsager/postgres/blob/bf082a5c0ffe214924...

noduerme 3y ago
Thanks... I haven't got the mental energy to follow their code ATM but yeah, it seems weird to buffer a static query as a prepared statement if it's only going to be used once. Maybe that kind of goes with a Nodejs philosophy, though? It seems like an assumption that in most cases a static query will recur... and maybe that's usually accurate with long running persistent connections. I'm much more used to working in PHP and not using persistent connections, and so sparing hitting a DB with any extra prepare call if you don't have to, unless it's directly going to benefit you later in the script.
- porsager 3y ago
  The input parameters are still dynamic which is made explicit with tagged template literals, so that makes almost all queries static in nature.
- ndriscoll 3y ago
  I believe it's typical for database libraries with auto-prepare to also have batch statement sending (and use it for prepare-executes), so there's no real cost to doing the prepare. The database has to parse/plan the statement regardless, and there's no round-trip delay with statement batching.
noduerme 3y ago
my own, very opinionated way of doing this in nodejs was to wrap node-mysql in promises and then force myself to make it explicit when preparing a a query whether I want it to set up a prepared statement which returns a reusable handle or run directly. That requires tracking prepared statement names and key/values for each one. you'll probably find this bad code too, but it was more of an experiment... I still don't feel safe using nodejs in deployment. https://github.com/joshstrike/StrikeDB/blob/master/src/StrikeDB.ts https://github.com/joshstrike/StrikeDB/blob/master/src/Strik...
fauigerzigerk 3y ago
Looks like named prepared statements can be disabled by setting "prepare" to false in the connection settings: https://github.com/porsager/postgres#connection-details https://github.com/porsager/postgres#connection-details
butlerm 3y ago
In most SQL databases, once you have a connection handle, you can allocate an arbitrary number of statement handles, and each SQL "statement" can be executed independently, has an associated query, parameters, a result set, and so on. It is entirely ordinary with an API like that to prepare a statement, bind parameters and columns, and execute and fetch the results. You can then reuse a statement in its prepared state, but usually with different parameter values, as many times as you want within the same session. The performance advantage of doing this for non-trivial queries is so substantial that many databases have a server side parse cache that is checked for a match even when a client has made no attempt to reuse a statement as such. That is easier if you bind parameters, but it is possible for a database to internally treat all embedded literals as parameters for caching purposes.

by latch 3y ago

noduerme 3y ago

Thanks... I haven't got the mental energy to follow their code ATM but yeah, it seems weird to buffer a static query as a prepared statement if it's only going to be used once. Maybe that kind of goes with a Nodejs philosophy, though? It seems like an assumption that in most cases a static query will recur... and maybe that's usually accurate with long running persistent connections. I'm much more used to working in PHP and not using persistent connections, and so sparing hitting a DB with any extra prepare call if you don't have to, unless it's directly going to benefit you later in the script.

porsager 3y ago
The input parameters are still dynamic which is made explicit with tagged template literals, so that makes almost all queries static in nature.
ndriscoll 3y ago
I believe it's typical for database libraries with auto-prepare to also have batch statement sending (and use it for prepare-executes), so there's no real cost to doing the prepare. The database has to parse/plan the statement regardless, and there's no round-trip delay with statement batching.

noduerme 3y ago

my own, very opinionated way of doing this in nodejs was to wrap node-mysql in promises and then force myself to make it explicit when preparing a a query whether I want it to set up a prepared statement which returns a reusable handle or run directly. That requires tracking prepared statement names and key/values for each one. you'll probably find this bad code too, but it was more of an experiment... I still don't feel safe using nodejs in deployment. https://github.com/joshstrike/StrikeDB/blob/master/src/StrikeDB.ts https://github.com/joshstrike/StrikeDB/blob/master/src/Strik...

fauigerzigerk 3y ago

Looks like named prepared statements can be disabled by setting "prepare" to false in the connection settings: https://github.com/porsager/postgres#connection-details https://github.com/porsager/postgres#connection-details

butlerm 3y ago

In most SQL databases, once you have a connection handle, you can allocate an arbitrary number of statement handles, and each SQL "statement" can be executed independently, has an associated query, parameters, a result set, and so on. It is entirely ordinary with an API like that to prepare a statement, bind parameters and columns, and execute and fetch the results. You can then reuse a statement in its prepared state, but usually with different parameter values, as many times as you want within the same session. The performance advantage of doing this for non-trivial queries is so substantial that many databases have a server side parse cache that is checked for a match even when a client has made no attempt to reuse a statement as such. That is easier if you bind parameters, but it is possible for a database to internally treat all embedded literals as parameters for caching purposes.