Remove implicit unique index creation on SERIAL columns?

From: Rod Taylor <rbt(at)zort(dot)ca>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Remove implicit unique index creation on SERIAL columns?
Date: 2002-08-17 20:57:40
Message-ID: 1029617861.37296.59.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'd like to propose dropping the auto-creation of UNIQUE indexes on
serial columns for the following reasons:

1. Serials with indexes are quite difficult to handle in pg_dump. It
means that the implicitly created unique index must be destroyed prior
to loading the data, then re-created afterward else risk a performance
hit.

2. SERIAL columns are usually used as the primary key of the table. As
such one must specify PRIMARY KEY at creation time to override the
implicitly created UNIQUE index.

3. Consistency with other databases.

MySQL's AUTO_INCREMENT suggests quite heavily that an index be applied,
but it doesn't appear to do it for you.

Oracle has SEQUENCES, but it is up to the user to associate them with a
column as per my understanding. At least, thats all I could find.

SAP -> SERIAL and SERIAL(n). No index is created (creates sequence /
default)

A nice side effect is that analyze.c will become somewhat cleaner.

Any thoughts?

Does anyone create serial columns without manually specifying UNIQUE
when wanting an index? Examples in our documentation do. See section
5.1.4 in the current docset:
http://developer.postgresql.org/docs/postgres/datatype.html

Removal of implicit UNIQUE index creation would not affect structure of
current or prior db versions -- strictly new tables on 7.3.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joe Conway 2002-08-17 21:26:10 Re: Remove implicit unique index creation on SERIAL columns?
Previous Message Tom Lane 2002-08-17 20:11:19 Re: compile warnings in CVS