August 11th, 2009 by depesz | Tags: , , , , , , , | 6 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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 :)

  1. 6 comments

  2. Aug 11, 2009

    Thanks, the deferrable uniqueness helps me a lot.

  3. # Richard Broersma
    Aug 11, 2009

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

  4. # Hans Maier
    Aug 13, 2009

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

  5. # rski
    Aug 28, 2009

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

  6. # Julian
    Dec 16, 2009

    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..

  7. Apr 3, 2012

    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 +

Leave a comment