Search Postgresql Archives

Abusing Postgres in fun ways.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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 did 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).

Before I dump a bunch of SQL on the list, my plan in short is to stage writes to a similar table: stage_data_queue, and then copy them all into a table visible by readers.

1 Writers get a shared advisory lock, get the next sequence_num and Insert one row, then release a shared advisory lock (in one stored procedure)

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 release 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...

I think I could fix this by leaving the (1) shared lock locked through the end of the stored procedure, and calling back unlocking it later.
I might also be able to fix this with Explicit Locks because I assume those will get properly unlocked after the Insert is truly committed.

Am I on the wrong track here?
-JD

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux