Thread

Cannot drop and recreate "primary key" index

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-29T06:40:17Z

Steffen Hulegaard (9sch1@txl.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.owen@wgcr.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