Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

On 25th of January, Tom Lane committed patch:

Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX.
 
This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an
already-existing UNIQUE INDEX.  While the CONSTRAINT isn't very
functionally different from the bare index, it's nice TO be able TO do that
FOR documentation purposes.  The main advantage OVER just issuing a plain
ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH
CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the
TABLE IS locked against updates.
 
ON the way, refactor SOME OF the code IN DefineIndex() AND index_create()
so that we don't have to pass through those functions in order to create
the index constraint's catalog entries.  Also, IN parse_utilcmd.c, pass
around the ParseState pointer IN struct CreateStmtContext TO save ON
notation, AND ADD error location pointers TO SOME error reports that didn't
have one before.
 
Gurjeet Singh, reviewed by Steve Singer and Tom Lane

I, personally, am extremely happy about it.

First, let's see the syntax:

$ CREATE TABLE test (
    i int4 NOT NULL,
    z text
    );
CREATE TABLE
 
$ CREATE UNIQUE INDEX test_idx ON test (i);
CREATE INDEX

Above creates simple table, with unique index, but which is not primary key or unique constraint. But now I can:

$ ALTER TABLE test ADD PRIMARY KEY USING INDEX test_idx;
ALTER TABLE
 
$ \d test
     TABLE "public.test"
 COLUMN |  TYPE   | Modifiers 
--------+---------+-----------
 i      | INTEGER | NOT NULL
 z      | text    | 
Indexes:
    "test_idx" PRIMARY KEY, btree (i)

or:

$ ALTER TABLE test ADD UNIQUE USING INDEX test_idx;
ALTER TABLE
 
$ \d test
     TABLE "public.test"
 COLUMN |  TYPE   | Modifiers 
--------+---------+-----------
 i      | INTEGER | NOT NULL
 z      | text    | 
Indexes:
    "test_idx" UNIQUE CONSTRAINT, btree (i)

Why is that so cool?

Indexes do accumulate bloat. It happens. And, while you can do “REINDEX" – it's locking operation.

But – you can create new index concurrently, with the same specification as bloated indexes, but under different name and then drop old index. This creation can be done CONCURRENTLY which makes it non-locking.

Which is great, but you can't really do it with PRIMARY KEYS or UNIQUE CONSTRAINTS, as you can't drop index (old, bloated) while having constraint. The best that we could do so far, when we had bloated primary key index, was to create new unique index, concurrently, and drop primary key.

This approach leads to problems with tools which use PRIMARY KEYs.

Luckily now – you can add primary key back to table, using recreated, bloat-free index. Or even – add primary key to table “on the fly", with very short lock only.

Sweet. Doesn't solve all problems – for example foreign keys pointing to primary key, but it's a good start.

4 thoughts on “Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index”

  1. The new NOT VALID option for FOREIGN KEYs can help there if you quickly want to point existing foreign keys to the new primary key.

  2. @Marko
    I know. Kind of. I will be testing and writing about it later (perhaps today, not sure yet).

  3. When I am using unique index to enforce primary key, its taking very long time. like index got created in 2 min through parallel 8 but primary key enableing taking 25 min. any help?

Comments are closed.