On 12/07/10 6:04 PM, bubba postgres wrote:
Hello all,
I'm creating a data queue on top of postgres and I'm wondering if I've
made an incorrect assumption about isolation or synchronization or
some similar issue.
Every item in the queue is given a unique ID from a sequence.
CREATE TABLE data_queue
(
sequence_num BIGINT PRIMARY KEY,
sender_key BIGINT NOT NULL,
datablob bytea
);
I read from the queue by passing in the last
_highest_seen_sequence_num to a stored procedure:
SELECT * from data_queue WHERE sequence_num >
_highest_seen_sequence_num ORDER BY sequence_num ASC
Obviously with readers and writers racing I need some sort of
synchronization. I've found the advisory locks and those seem to be my
best bet. I used explicit locking for a while but ran into an issue
with our daily backups and ACCESS_EXCLUSIVE (which I might be able to
revisit)
I'm also trying to create a setup where there is basically no
blocking, writers can always write, readers are not blocked by writers
(though there may be a delay in what is visible to the reader).
why don't you use a serial for your sequence_num ? a serial is a int
or bigint thats connected to a sql sequence, which takes care fo all
that itself, without needing any fancy nonsense.
and, no, you shouldn't need any such global locking, at least as long as
you only have one process
Before I dump a bunch of SQL on the list, my plan in short to stage
writes to a similar table: stage_data_queue.
1 Writers get a shared advisory lock, Insert one row, and release
shared advisory lock (in one stored procedure)
stop with all the locking. design your queries and inserts to work
smoothly with MVCC and none of that should be neccessary.
2 At some point there is a 'tick' and another thread gets the
corresponding exclusive advisory lock (letting all in flight writes
finish).
Then copy all rows into another table visible to the readers, then
Truncate the staging table, and releasing the exclusive lock. (all in
one stored procedure)
My fear is that there is still a race here because the writer (1)
calls unlock at the end of the stored procedure, and thus there is a
window before the row is committed, and (2) may end up truncating that
data...
hard locks are generally a bad idea if they can be avoided. use
transactions to group things together that have to happen atomically.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
