Re: bigserial continuity safety

On Mon, Apr 13, 2015 at 3:05 PM, Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com>
wrote:

> Hi.
>
> If I have a table created as:
>
> CREATE TABLE xq_agr (
> id BIGSERIAL PRIMARY KEY,
> node text not null
> );
>
> and that multiple applications insert into. The applications never
> explicitly specify the value for 'id'.
> Is it safe to, on a single connection, do:
>
> - open transaction (default transaction isolation)
> - Open cursor for select * from xq_agr order by id asc
> - do something with current record
> - advance the cursor (and repeat something), but stop at some point (id =
> LAST_ID), and
> - delete from xq_agr where id <= LAST_ID;
> - commit
>
> "safe to" means - whether the cursor will not miss any records that were
> deleted at the end.
>
> I'm suspecting that depending on the commit order, I may have situations
> when:
> - TX1 insert ID 1
> - TX2 insert ID 2
> - TX2 commits
> - TX3 scans 2
> - TX1 commits
> - TX3 deletes <= 2
> - record ID1 is deleted, but never processed.
>
>
​Going to ignore the MVC question for the moment and describe a better
"state transition mechanism" to consider.

pending -> active -> completed

If you ensure you never delete (i.e., transition to completed) something
that isn't active then you can never delete an item in pending.

​Limit the locking to the state transitions only.

The downside is the need to deal with "active" items that have been
abandoned by whatever process marked them active.

Back to your question: you should probably not use "<=" in your where
clause. However, in READ COMMITTED TX3 cannot see ID1 since the snapshot
it took out was created before TX1 committed. I am not fluent enough to
work through the entire scenario in my head. I'd suggest you actually open
up 3 psql sessions and play with them to see how things really behave.

For me, a simply "SELECT FOR UPDATE / UPDATE WHERE" command in a function
solves the problem as small scale with minimal performance degradation.
The transition from "pending" to "active" is effectively serialized and the
transition from "active" to "completed" only occurs when the process has
been performed and it is not possible to have two client simultaneously
processing the same work.

David J.