Select for update about node-pg-cursor HOT 3 OPEN

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?

from node-pg-cursor.

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 [email protected]:

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
#11 (comment)
.

from node-pg-cursor.

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.

from node-pg-cursor.