Select for update

[otexier]

Hi,

I am looking for a way to accomplish select for updates in Postgresql from NodeJS.

Your module seems quite good for reading a cursor, but is there a way to update the cursor ?

I have looked your code, I didn't find a direct way of doing that. Probably a solution is to read Postgres protocol and implement something.

Have you a plan to add this fonctionnality ?

Many thanks anyway,

Olivier

[brianc]

I'm not sure you can update a cursor via the protocol. You have more control over a cursor if you use text commands to manipulate it. What were you thinking of trying to do to the cursor?

[otexier]

Hi,

I was thinking of selecting rows in a huge table (list of all animal movements), iterate on them, and when some conditions are met for this animal update columns given business criteria. Do a one pass processing.

I know how to achieve this in two phases processing (select then update), but ... there is kind of stupid argument on adoption of nodejs for accessing a huge database in our enterprise.

One of the last arguments to forbid Nodejs on these database is the lack of select for update functionnality.

Personnaly I think a way to achieve that in node will be good, but I am pretty sure that the real question on nodejs adoption must not be answered on this sort of stupid arguments. I know for sure that we can live without this functionnality.

Thank you for your time,

2015-03-10 16:56 GMT+01:00 Brian C notifications@github.com:

I'm not sure you can update a cursor via the protocol. You have more control over a cursor if you use text commands to manipulate it. What were you thinking of trying to do to the cursor?

— Reply to this email directly or view it on GitHub https://github.com/brianc/node-pg-cursor/issues/11#issuecomment-78083907 .

[jeromew]

There are 2 approaching features to what you are discussing.

---

UPDATE table SET ... WHERE CURRENT OF cursor;

is a construct of postgres stored procecures pl/sql. It allows for updating rows as their are selected via the cursor.

---

SELECT ... FOR UPDATE

sql syntax. This enable to LOCK the selected rows so that a further UPDATE acts atomically on the rows.

cf for instance http://stackoverflow.com/questions/14710214/select-for-update-statement-in-postgresql

---

The feature you are requesting looks like the feature regarding pl/psql cursors. It is a sort of "update in place" where the rows can be updated as they are selected, hinting that this is faster than a select followed by an update.

I myself am looking for an efficient way of doing a sort of :

var cursor = ...; cursor.pipe(rowTransformer).pipe(cursor);

as a way to use javascript to do small transformations or cleanups on all rows of a table.

One option could be to install pl-v8 inside postgres and use the stored procedures cursors, but pl-v8 inside postgres has its own set of problems.

another option could be maybe to use node-pg-copy to have a way to 1/ read a table 2/ transform the rows on the fly 3/ flush the rows into another table

This would give you a fast one pass processing at the price of duplicating the table. (+ I am not sure node-pg-copy is already ready for this)

Other than that, if your business criteria is simple and you don't really need javascript, use a pl/sql stored procedure.