Abusing Postgres in fun ways.

[bubba postgres <bubba.postgres@xxxxxxxxx>]

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