Re: insert locking issue for PG 9.0

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ben Chobot
Sent: Wednesday, November 16, 2011 5:48 PM
To: pgsql-general General
Subject: [GENERAL] insert locking issue for PG 9.0

Our application has a table that looks like:

create table jobs
(
id int,
first boolean
);

What we need is for the app to be able to shove data into jobs with an
assigned id, and guarantee that first is only true for one id. In other
words, we could easily enforce what we want by creating a unique index on
jobs (id) where first=true.

-----------------------------------------

Thinking outside the box...

Do records ever get inserted with "first = FALSE" directly or is the only
way for a record to have a false "first" is because another record already
exists?
Can your process re-execute the record insertion if a UNIQUE INDEX failure
occurs?

My suggestion is to forget dealing with "first" and instead create a unique
index on (id, job_time). The earliest record for a given id will always be
"first". This is likely to be fast enough but if not you could always setup
the "first" field and update it later - and then use it as part of a partial
index.

The first question needs to be "No" so that you do not have a situation
where the first inserted record is one that should not be "TRUE"; though by
using three-valued logic you could work around this requirement.
The second question needs to be "Yes" in the rare circumstance that two IDs
are inserted at exactly the same milli/nano-second.

So

CREATE TABLE jobs (id int, job_time timestamptz);
CREATE UNIQUE INDEX (id, job_time [need explicit timezone to avoid
mutability; or convert to some kind of epoch]);

CREATE VIEW first_job AS
SELECT j1.id
FROM jobs j1
WHERE j1.record_creation_ts = (SELECT job_time FROM jobs j2 WHERE
j2.id = j1.id ORDER BY job_time ASC LIMIT 1)

; --syntax not checked but this is the idea...

The main thing you need to decide with this approach is how you calculate
the timestamp and, if two timestamps are equal, how to resolve the
difference. Simply adding a few time units and re-inserting should resolve
the problem in simplistic cases.

David J.