Database Reference
In-Depth Information
That seems pretty good, yet it doesn't work on primary keys. Why not? Because you can't add
a primary index to a table concurrently, in PostgreSQL 9.0 at least.
So we have another trick, slightly more complex than the last. First, we create another index
with the same definition as the primary key as follows:
F
CREATE UNIQUE INDEX new_pkey ON test (id);
and check internal identifiers again as follows:
SELECT oid, relname, relfilenode
FROM pg_class
WHERE oid in (SELECT indexrelid
FROM pg_index
WHERE indrelid = 'test'::regclass);
oid | relname | relfilenode
-------+-------------------+-------------
16639 | test_pkey | 16639
16642 | test_category_idx | 16642
16643 | new_pkey | 16643
(3 rows)
Now we're going to swap the two indexes over, so that all the primary key constraints stay
active and so do all of the foreign keys that depend upon them. So, we need to swap the
relfilenode
values as follws:
BEGIN;
LOCK TABLE test;
UPDATE pg_class SET relfilenode = 16643 WHERE oid = 16639;
UPDATE pg_class SET relfilenode = 16639 WHERE oid = 16643;
DROP INDEX new_pkey;
COMMIT;
which we confirm has succeeded using the following:
SELECT oid, relname, relfilenode
FROM pg_class
WHERE oid in (SELECT indexrelid
FROM pg_index
WHERE indrelid = 'test'::regclass);
oid | relname | relfilenode
-------+-------------------+-------------
16639 | test_pkey | 16643
16642 | test_category_idx | 16642
16643 | new_pkey | 16639
(3 rows)
