Thread: SERIAL + PRIMARY KEY = redundant indexes
With current sources: regression=> create table t1 ( f1 serial primary key ); NOTICE: CREATE TABLE will create implicit sequence t1_f1_seq for SERIAL column t1.f1 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t1_pkey for table t1 CREATE OK so far ... regression=> create table t2 ( f1 serial, regression-> primary key (f1) ); NOTICE: CREATE TABLE will create implicit sequence t2_f1_seq for SERIAL column t2.f1 NOTICE: CREATE TABLE/UNIQUE will create implicit index t2_f1_key for table t2 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t2_pkey for table t2 CREATE And, indeed, it's made two separate indexes on t2's f1 field. This is a bug, no? regards, tom lane
> regression=> create table t2 ( f1 serial, primary key (f1) ); > NOTICE: CREATE TABLE will create implicit sequence t2_f1_seq for SERIAL column t2.f1 > NOTICE: CREATE TABLE/UNIQUE will create implicit index t2_f1_key for table t2 > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index t2_pkey for table t2 > CREATE > And, indeed, it's made two separate indexes on t2's f1 field. This is > a bug, no? Si. I'll look at it. - Tom -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California
fwiw, there is this comment in the code: * Note that this code does not currently look for all possible redundant cases* and either ignore or stop with warning. The create might fail later when* names for indices turn out to be duplicated, or a user might have specified* extra useless indices which might hurt performance. - thomas 1997-12-08 But I should (probably) be able to fix this particular case. - Tom -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California