Steffen Hulegaard ([EMAIL PROTECTED]) 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 <[EMAIL PROTECTED]> | 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
