Pg-promise: Reuse prepared statements

[ghusse]

Hello,

Is it possible to reuse PreparedStatement objects in multiple queries? The main issue I have with them is that values are attached to the object.

I need to use the same prepared query multiple times, in different promises. With the current design I cannot see how to simply use the same PreparedStatement because I'm not sure previously used values have actually been successfully sent to the server.

My code looks like this:

const BPromise = require('bluebird);
const values = [{id: 1 value: 2}, {id: 2, value: 42}]
const prepared = new pg.PreparedStatement('foo', 'UPDATE foobar SET value=$1 WHERE id = $2');


BPromise.map(values, value => {
  prepared.values = [value.value, value.id];
  return db.none(prepared);
});

It appears that not all values are updated in the DB.

If I replace the prepared statement by the SQL text & second argument, all values are correctly updated.

[ghusse]

I figured (looking at the source code) I needed to create an object for each query. pg-promise seem to handle the creation only once by session.

The API is not very clear on this point. I assumed I could reuse the same object (in a same way the prepared statement reuses a declaration on the server).

Thanks for the utility

[vitaly-t]

Object PreparedStatement is fully reusable. And the way you are using it seems correct.

It appears that not all values are updated in the DB.

That shouldn't be the case. I would suggest investigating further what is really happening.

I know about projects that fully rely on reusable Prepared Statements exactly the way you do, and they always work. There must be something else wrong in your code and/or the database server, if you do not see the updates.

Example of such a project: https://github.com/PCSTrac/napi
Here he is even caching reusable PreparedStatements that use Query Files, and the whole thing works like a clock. Plus, we have plenty of tests for them.

[vitaly-t]

Wait, I know what's wrong in your code! :) It is because you are executing each Prepared Statement on a separate session, which you should never do. It is bad in many ways. It will be depleting your connections, and it will be overriding your Prepared Statement values all the time.

I could show the alternative way of doing it, but then using Prepared Statements like that to run multiple updates is wrong anyway. It would be a misuse of Prepared Statements.

You should execute all such updates as a single query, which will work 10 times faster. See helpers.update for that.

[vitaly-t]

@ghusse Later on as I was doing some performance optimization within the query execution, your post helped me realize that even though the updates you showed isn't a good idea, they still should work anyway. So, I have reviewed the code and made some changes that I just released as version 4.2.4.

Thank you for the idea. Your code example that you gave now should work as you expect it. But I still strongly suggest that you do it differently, if you want to get good performance out of it ;)

[ghusse]

Hi,

Thanks for the update, I will definitely test it again with the new version.

What do you suggest instead for the updates? In my case I have to run
multiple update queries that use another table for the condition (like a
join but there is no join condition in updates in of)
Le 21 mai 2016 6:24 AM, "Vitaly Tomilov" [email protected] a
écrit :

@ghusse https://github.com/ghusse Later on as I was doing some
performance optimization within the query execution, your post helped me
realize that even though execution updates isn't such a good idea, it still
should work regardless. So, I have reviewed the code and made some changes
that I just released as version 4.2.4
https://github.com/vitaly-t/pg-promise/releases/tag/v.4.2.4.

Thank you for the idea. Your code example that you gave now should work as
you expect it. But I still strongly suggest that you do it differently, if
you want to get good performance out of it ;)

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
https://github.com/vitaly-t/pg-promise/issues/146#issuecomment-220757724

[vitaly-t]

What do you suggest instead for the updates?

Not instead of updates, I was suggesting that if you are running the same type of updates, you should do it via a single query, with the help of method update (plenty of examples there). You would generate the main body, and then append WHERE part with all your JOIN logic as needed ;)

[ghusse]

Thanks for the info & the docs. I did not know about this utility.

I'm not sure to see how to use it in my case, because I have to use a query like this one:

UPDATE table1 set table1.val = $1 
FROM table2
WHERE table1.foo = table2.foo
AND table1.id = $2

The update utility generates a query including the FROM and WHERE parts. If I want to reuse that, I need to either parse the generated query to insert my second table after the FROM or generate a query by myself with the same 'trick' (listing values in the query).

[vitaly-t]

The update utility generates a query including the FROM and WHERE parts.

No it does not, WHERE part is not included. Are you sure that data in your update comes from a table and not from memory? 'Cos that wouldn't be a typical update.

Anyway, that update method can only help with data updated from memory, not from a table. If you need to update from table, then you don't need it, you can update in a usual way.

[ghusse]

Thanks for the help.

I went to this kind of solution, I just used as.format to generate values. I have an utility for that in our code, and this way I have the whole query in a file.

[vitaly-t]

Don't forget QueryFile ;) It is really good at loading and pre-formatting queries ;)

[ghusse]

That's exactly what I did :)

[vitaly-t]

@ghusse There was an improvement in the new v.4.3.0.

Now you can pass Prepared Statement parameters into query methods to set/override the values.

i.e. you can simplify this:

BPromise.map(values, value => {
  prepared.values = [value.value, value.id];
  return db.none(prepared);
});

into this:

BPromise.map(values, value => db.none(prepared, [value.value, value.id]));

[ghusse]

Great news! That is exactly what I wanted to use :)

[geonanorch]

I was about to open an issue when I found this one. I just experienced the same problem: using a prepared statement repeatedly with separate queries, something like:

const ps = new PS('my-insert', 'INSERT INTO mytable(id) SELECT $1');
myarray.map( (id) => {
  ps.values = [ id ];
  pgp.none(ps);
})

Arguably and as I learned from this thread that is not the proper way to chain inserts, but that is not the point: the fact that the prepared statement values are not copied during the call to pgp.none() was counter-intuitive for me (like for @ghusse). Currently if one wants to play it safe a prepared statement should only be used in one place of the code –and even then paying attention not to loop.

It seems to me that ps.values should be copied synchronously. Or be locked read-only while a query is using it. Or at least have a big fat warning in the doc that it should not be modified until said query resolved.

This being said, I am very grateful for this great library and its excellent documentation. And I shall use helpers.update()! ;-)

[vitaly-t]

@geonanorch that's the cost of reusing the same PreparedStatement object. Just like any object that can be modified asynchronously.

PreparedStatement is only useful for large/complex queries that benefit from prepared execution on the server side. Inserts and updates really should be done via the [helpers], especially repeated ones, that's singificantly more performance-efficient, as per the [Performance Boost] article ;)