Serial Data Type

Thus spake David Hartwig

I have a couple minor issues regarding the 6.4 implementation of the
SERIAL data type. I like the ease of using the serial data type and I
suspect it will be used frequently base on the number of inquiries over
the past months.

1. Should the sequence created by the serial type declaration, be
dropped as a result of dropping the parent table?

Sounds like a good idea.

2. Can a declared serial column be also a primary key? If so, what
will be the side effected? Specifically, how will the unique index be
named? The ODBC driver uses the {relname}_pkey to identify the primary
key of a table. The driver must be able to identify primary keys.

I suspect that the serial field will be the primary key 99% of the time.
I hope it can be.

As for finding the primary, with the new changes we should be able
to do something like this.

SELECT 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';

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

I have a couple minor issues regarding the 6.4 implementation of the
SERIAL data type.
1. Should the sequence created by the serial type declaration, be
dropped as a result of dropping the parent table?

Yes. But it isn't. And I don't know how we would do that on short
notice. I implemented the serial type with ~20 minutes work using the
hooks I'd put in to implement primary keys, and of course Vadim's
sequence capability. The difference is that indices were already
automatically removed when tables are dropped, so I didn't have to deal
with it.

2. Can a declared serial column be also a primary key? If so, what
will be the side effected? Specifically, how will the unique index be
named? The ODBC driver uses the {relname}_pkey to identify the primary
key of a table. The driver must be able to identify primary keys.

Hmm.

postgres=> create table xx (s serial primary key, i int4);
ERROR: parser: parse error at or near "primary"
postgres=> create table xx (s int4 primary key, i int4);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index xx_pkey for
table xx
CREATE

So, at the moment the syntax doesn't allow anything in addition to the
serial type declaration. We should be able to fix that up and allow
both, but perhaps it should wait for Vadim's primary key/foreign key
features, which may be available for v6.4.

I'd expect to be able to get the name to line up with the "_pkey"
convention, though perhaps by then you will be able to look in a column
in a table for real primary/foreign key flags to do this.

- Tom

D'Arcy J.M. Cain wrote:

2. Can a declared serial column be also a primary key? If so, what
will be the side effected? Specifically, how will the unique index be
named? The ODBC driver uses the {relname}_pkey to identify the primary
key of a table. The driver must be able to identify primary keys.

I suspect that the serial field will be the primary key 99% of the time.
I hope it can be.

As for finding the primary, with the new changes we should be able
to do something like this.

SELECT 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';

Is pg_index.indisprimary available now to determine primary'ness. If not,
when?

Thus spake David Hartwig

As for finding the primary, with the new changes we should be able
to do something like this.

SELECT 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';

Is pg_index.indisprimary available now to determine primary'ness. If not,
when?

It is now in the current tree but it isn't used yet. I just manually
set it for the tables I need and then use it. Eventually I will be
able to leave out the manual step and all my code will work. Right
now it is always set to 'f' at create time.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.