Re: Abusing Postgres in interesting ways

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.