Complex constraint across all data in a table

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.

Simple case

If you don't need intermediate states violating the constraint over the course of a single transaction, a partial UNIQUE index on a constant value does the job:

CREATE TABLE jobs (
  jobs_id int PRIMARY KEY
, status text
);

INSERT INTO jobs VALUES
  (1, 'A')
, (2, 'B')
, (3, 'G')
;

CREATE UNIQUE INDEX jobs_status_uniq_idx ON jobs ((true))
WHERE status in ('P', 'G');

Note the additional set of parenthesis around the constant 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

This sequence of commands works, of course:

BEGIN;
DELETE FROM jobs WHERE status IN ('P', 'G');
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');
COMMIT;

A single command with CTE works, too:

WITH ins AS (
   INSERT INTO jobs (jobs_id, status) VALUES (8, 'G')
   )
DELETE FROM jobs WHERE status IN ('P', 'G');

fiddle

Sub-statements in WITH and the main query are executed virtually concurrently. All of them see the same snapshot of the database. So the sequence of sub-commands does not matter.

Related:

• PostgreSQL multi-column unique constraint and NULL values

But this fails (like it typically should!):

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

Advanced case with DEFERRED constraint

If you really need to allow violating intermediary states within the same transaction, you need a DEFERRABLE constraint that is INITIALLY DEFERRED, or SET CONSTRAINTS ALL | name DEFERRED within the transaction.

The manual:

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 in the chapter Index Uniqueness Checks.

A partial unique index cannot be deferred and is always checked immediately. A UNIQUE CONSTRAINT can only be defined for columns, not for expressions. So add a redundant column. It's cheap, since it is filled with null values. An existing null bitmap typically swallows it without allocating more physical disk space. null values do not violate a unique constraint by default. See:

• Create unique constraint with null columns

Postgres 12 or newer

Add a generated column:

ALTER TABLE jobs
  ADD COLUMN in_process boolean GENERATED ALWAYS AS (status IN ('P', 'G') OR NULL) STORED
, ADD CONSTRAINT jobs_in_process_uniq UNIQUE(in_process) DEFERRABLE INITIALLY DEFERRED
;

fiddle

See:

• Computed / calculated / virtual / derived / generated columns in PostgreSQL

Postgres 11 or older

Add a plain column and keep it current with triggers:

ALTER TABLE jobs ADD COLUMN in_process boolean;
UPDATE jobs set in_process = true
WHERE  status in ('P', 'G');  -- rest stays null

Triggers keep the column current:

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

-- 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, in_process ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

DEFERRABLE constraint

Either way, create a DEFERRABLE UNIQUE constraint on the added column:

ALTER TABLE jobs
ADD CONSTRAINT jobs_in_process_uniq UNIQUE(in_process) DEFERRABLE INITIALLY DEFERRED;

Now transactions go through as long as the state is consistent at the end of each transaction:

BEGIN;
-- SET CONSTRAINTS jobs_in_process_uniq DEFERRED;  -- if created with INITIALLY IMMEDIATE
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- check deferred
DELETE FROM jobs WHERE status = 'P';
COMMIT;  -- succeeds!

StackExchange Database Administrators Q#47840, answer score: 10