Waiting for 8.5 – deferrable uniqueness

On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:

Support deferrable uniqueness constraints.
 
The current implementation fires an AFTER ROW trigger for each tuple that
looks like it might be non-unique according to the index contents at the
time of insertion.  This works well as long as there aren't many conflicts,
but won't scale to massive unique-key reassignments.  Improving that case
is a TODO item.
 
Dean Rasheed

Finally!

If you don't get why it's cool, let's see this example in any previous version:

# CREATE TABLE test (
    i INT4 PRIMARY KEY
);
 
# INSERT INTO test (i) VALUES (1), (2), (3);
 
# SELECT * FROM test;
 i
---
 1
 2
 3
(3 ROWS)

and when you try to update:

# UPDATE test SET i = i + 2;
ERROR:  duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey"

It fails because it checks the unique after every row update. It is still possible to do this update with some kind of loop that will order the rows in descending order over i, but it's not trivial.

In 8.5 the same construction generates error as well:

# UPDATE test SET i = i + 2;
ERROR:  duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey"
DETAIL:  KEY (i)=(3) already EXISTS.

But if I create table like this:

# CREATE TABLE test (
    i INT4 PRIMARY KEY DEFERRABLE INITIALLY DEFERRED
);
 
# INSERT INTO test (i) VALUES (1), (2), (3);
 
# SELECT * FROM test;
 i
---
 1
 2
 3
(3 ROWS)

You actually can update the values of i:

# UPDATE test SET i = i + 1;
UPDATE 3
# SELECT * FROM test;
 i
---
 2
 3
 4
(3 ROWS)

There still are some issues with this code – unclear error message when we try to set constraint as deffered when it was defined without “DEFERRABLE", some clarification issues with “DEFERRABLE INITIALLY IMMEDIATE" but it's definitely a cool feature.

Also – please note that it applies to constraints, not indexes!.

So, while you can have:

ALTER TABLE test ADD UNIQUE (i) DEFERRABLE INITIALLY DEFERRED;

You can't:

CREATE UNIQUE INDEX q ON test (i) DEFERRABLE INITIALLY DEFERRED;

Which means that you're out of luck if you'd like partial, functional, unique index to be deferred. But I think we will get there. Maybe even in 8.5 – who knows 🙂

7 thoughts on “Waiting for 8.5 – deferrable uniqueness”

  2. Does anyone know of any other RDBMS that support this feature? As far as I know, PostgreSQL is the first.

  3. No, PostgreSQL is not the first RDBMS. Microsoft SQL Server 2000 has the feature already included, which was released almost 10 years ago.

  4. In Oracle there is no problem with such updates, so it looks like oracle also has that feature built in.

  5. In other RDBMS performing an update like the one you have written about would never trigger an unique-constraint violation since it does not actually violate any unique-constraints.
    In this case postgres is about 10 years behind the competition..

  6. In fact, if you read Franck Edgar Codd 12 rules for RDBMS, he says in 1985 (27 years before) the rule taht PG realize 24 years later ! (Off course PG was not there at this time, but Ingres, is ancestor was..)

    Rule 7: High-level insert, update, and delete:
    The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

    PG violate this rule by doing a per row UPDATE and not a set based update from the origin.

    PG also violate undirectly the Rule 12: The nonsubversion rule:

    If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.

    By furnishing a by-pass solution to a non trivial problem.

    In fact the probleme a PG is the fact that the engine was not designed for SET based operation and one main appareance of this affirmation is the incapability of doing parallelism in intra query processing !

    A +

  7. Running Postgres 9.5 and you still can’t do

    CREATE UNIQUE INDEX q on test (i) DEFERRABLE INITIALLY DEFERRED;

    I would love to see them add deferrable indexes. The only way to have any sort of partial unique constraint in Postgres is still to create an index with a WHERE clause, but not being able to defer it limits the usefulness of this approach.

Comments are closed.