On 2013-04-17 09:18:13 +0200, Albe Laurenz said:
Philipp Kraus wrote:
My PG database is connected to differend cluster nodes (MPI). Each
programm / process on each node are independed and run the SQL
select * from table where status = waiting
after that I update the row with the update statement (set status = working)
so in this case one process can run the select, than comes another
process and runs also the select, but both processes get an equal row.
But this does not allowed. The second process need not see the row,
which is taken by the first process. So can I suppress, that a select
call
sees a row, which is locked by a transaction? So I would like to do
this with a store procedure, that runs the select and the update and
after that
it returns the PK of the selected dataset. If two (or more) processes
run the SP at the same time, but the update can create an error, so the
stored
procedure is stopped and must called again.
I need a solution, that a row, which is taken by one process not shown
by all other processes
Do you want to implement something like a queue?
Yes
I can think of two techniques:
1) Locking
----------
In a transaction, you get a few rows for processing by
SELECT * FROM table WHERE status = waiting
ORDER BY id LIMIT 5 FOR UPDATE;
("id" is the primary key here).
Then you process and update the rows and commit.
This will cause concurrent SELECT FOR UPDATE operations
to block until the transaction is committed, effectively
serializing the processing.
2) Set a marker
---------------
You get a few rows by
UPDATE table SET status = processing WHERE id IN
(SELECT id FROM table WHERE status = waiting
ORDER BY id LIMIT 5) RETURNING *;
Then process and update the rows.
This won't block concurrent processes for the whole
time it takes to process the rows, so it's probably
closer to what you want.
The marker solution seems to be the correct idea, I must think about it.
Would you create the call within a stored procedure (and call the SP
from the client) or would you use the
statement from a client direct?
Thx
Phil
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
