Cannot drop and recreate "primary key" index

Steffen Hulegaard (9sch1(at)txl(dot)com) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
Cannot drop and recreate "primary key" index

Long Description
Just an FYI ...

To quickly load bulk data (COPY FROM), PostgreSQL documentation
suggests dropping indices, loading the data and then recreating
the indices. This does not seem to work *quite* correctly for the indices created as a side effect of a primary key declaration (in a
CREATE TABLE statement). The script below demonstrates that the
recreated primary key index is always demoted to non-primary key status whenever it is dropped and recreated (I think). Although I would hope this tiny difference only confuses CASE tools, and other
users of the PostgreSQL system catalog, I would alert you to
the possibility of other reprecussions.

A work-around would be to use ALTER TABLE <tname> PRIMARY KEY (< key column/attribute list >) (SQL-92) - when PostgreSQL supports that.
If this were supported, the implicit index could be recreated
with all of the usual system catalog annotations. However, once users want to control their index TABLESPACE, the index attribute operator classes (e.g. bigbox_ops), index type (hash/btree/rtree) and the like, the implicit/side-effect index associated with the primary key assertion promises to be endlessly problematic (unless the
convenience of primary key declarations can be replaced with
explicit create index commands). The ALTER TABLE <tname> PRIMARY KEY won't mitigate these implicit/side-effect indexing limitations (without an expanded and evolving syntax).

Sample Code
/* $Id$
+--------------------------------------------------------------------
| No Copyright. Public Domain.
+--------------------------------------------------------------------
|
| bug4.sql Constraints using inherited attributes fail
|
| FYI: Dropping and recreating indices always
| demotes primary key indices to ordinary
| status in the system catalog. This might
| have repercussions. The lack of an
| ALTER TABLE <tname> PRIMARY KEY ( <column list> )
| means there is no work-around if this
| difference ever becomes meaningful.
|
| The lack of syntax to control tablespace,
| index type (btree/hash/rtree), index
| attribute operator class (e.g. bigbox_ops), etc.,
| promises to make the implicit/side-effect
| index forever problematic.
|
| Environment ----------------------------------------------------
| RedHat 6.2
| select version();
| PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
| # rpm -qi postgresql-7.0.2-2
| Name : postgresql Relocations: /usr
| Version : 7.0.2 Vendor: The Ramifordistat
| Release : 2 Build Date: Mon 12 Jun 2000 02:21:35 PM PDT
| Install date: Fri 04 Aug 2000 11:40:39 AM PDT Build Host: utility.wgcr.org
| Group : Applications/Databases Source RPM: postgresql-7.0.2-2.src.rpm
| Size : 7431735 License: BSD
| Packager : Lamar Owen <lamar(dot)owen(at)wgcr(dot)org>
| URL : http://www.postgresql.org/
| Summary : PostgreSQL client programs and libraries.
|
| 01/28/2000 SC Hulegaard Created.
+ ------------------------------------------------------------------- */

CREATE TABLE test_pki (
record_id INT4 NOT NULL,
press_id INT4 NOT NULL DEFAULT 4,
central_area BOX NOT NULL DEFAULT '((0,0),(0,0))',
CONSTRAINT test_pki_record_id_ix
PRIMARY KEY ( record_id, press_id ) ) ;

/* Is this right? Is there no better way? */
SELECT 1 AS ord, pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
UNION
SELECT 2 AS ord, pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[1] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
ORDER BY relname, ord;

DROP INDEX test_pki_record_id_ix;

CREATE UNIQUE INDEX test_pki_record_id_ix
ON test USING BTREE ( record_id );

/* Is this right? Is there no better way? */
SELECT 1 AS ord, pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
UNION
SELECT 2 AS ord, pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[1] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
ORDER BY relname, ord;

DROP TABLE test_pki ;

No file was uploaded with this report