Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status.

I want to ensure that the result of this is always either zero or one:

select count(id) from jobs where status in ('P', 'G');

We're using explicit transactions so ideally this check would happen on commit and abort the transaction if the invariant doesn't hold. Handling any racy processing that throws the occasional error is much easier for us to deal with than suddenly ending up with more than one job that is 'in process'.

The solution only needs to work with Postgres so we're quite happy to take a non-standard solution to this. We're currently on 8.4, but we'll be upgrading at some point to 9.x if that makes any difference.

share|improve this question
1  
Does a unique partial functional index work? (partial: only for status 'P' or 'G'; functional (or "index on expression"): map both characters 'P' and 'G' on the same value, e.g., true) –  j.p. Aug 9 '13 at 15:22
add comment

1 Answer

up vote 3 down vote accepted

Tested with Postgres 9.1 & 9.2. Most of it should work for 8.4 as well.

Simple case

Without intermediate states violating the constraint over the course of a single transaction, a partial UNIQUE index on a constant value does the job. Given this test case:

CREATE TEMP TABLE jobs(jobs_id int primary key, status text);
INSERT INTO jobs (jobs_id, status)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'G');

Use this index:

CREATE UNIQUE INDEX jobs_status_uni_idx ON jobs ((TRUE))
WHERE status in ('P', 'G');

Note the additional set of parenthesis around the value TRUE.

Test (one line at a time):

INSERT INTO jobs (jobs_id, status) VALUES (5, 'G'); -- fails
INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- fails

DELETE FROM jobs WHERE status = 'G';

INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- succeeds
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G'); -- fails

-> SQLfiddle

Related answer with more explanation:
PostgreSQL multi-column unique constraint and NULL values

Advanced case with DEFERRED constraint

ideally this check would happen on commit

A partial unique index cannot be deferred and is always checked immediately. Therefore, continuing the example from above, this would fail:

BEGIN;
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- fails immediately!
DELETE FROM jobs WHERE status = 'P';
COMMIT;

Example is with explicit transaction handling. Your client may have automatic transaction handling (autocommit on or off).

If you need this to work, you need a DEFERRABLE constraint that is INITIALLY DEFERRED or SET CONSTRAINTS ALL | name DEFERRED within the transaction.

I quote the manual here

Non-deferred Uniqueness Constraints

When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

More gory details about Index Uniqueness Checks in the manual.

However, a UNIQUE CONSTRAINT can only be defined for columns, not for expressions.
Add a redundant column for this purpose and keep it current with triggers. Like this:

ALTER TABLE jobs ADD COLUMN status_uni boolean;
UPDATE jobs set status_uni = TRUE WHERE status in ('P', 'G');  -- rest stays NULL
ALTER TABLE jobs ADD CONSTRAINT jobs_status_uni
UNIQUE(status_uni) DEFERRABLE INITIALLY DEFERRED;

The additional column is cheap, since it is filled with NULLs, an existing the NULL bitmap should normally swallow it without the need for additional physical disk space.
NULL values do not violate a unique constraint per definition.

Create triggers to keep the column current at all times:

CREATE OR REPLACE FUNCTION trg_jobs_status()
  RETURNS trigger AS
$func$
BEGIN
NEW.status_uni = (NEW.status IN ('G', 'P') OR NULL);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

For all inserts:

CREATE TRIGGER insbef
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

To optimize, only for relevant updates:

CREATE TRIGGER upbef
BEFORE UPDATE OF status, status_uni ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

Test

Now your transactions go through, as long as the state is consistent at the end:

BEGIN;
-- SET CONSTRAINTS jobs_status_uni IMMEDIATE;  -- only for INITIALLY IMMEDIATE
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- check deferred
DELETE FROM jobs WHERE status = 'P';
COMMIT;  -- succeeds!

-> SQLfiddle.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.