Postgres Pro
Downloads
Home   >   mailing lists

Thread: Serial Data Type

Serial Data Type

From
David Hartwig
Date:
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?

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.

Comments?

Re: [HACKERS] Serial Data Type

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.

Re: [HACKERS] Serial Data Type

From
"Thomas G. Lockhart"
Date:
> 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

Re: [HACKERS] Serial Data Type

From
David Hartwig
Date:

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?

Re: [HACKERS] Serial Data Type

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.