We currently only allow the words PRIMARY KEY on a SERIAL column.  Is
there a reason we don't allow PRIMARY KEY on an integer field?  Seems it
should be allowed.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 18 Nov 1999 22:26:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Primary key requires SERIAL
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We currently only allow the words PRIMARY KEY on a SERIAL column.

Say what?  There are ColConstraintElem and ConstraintElem productions
for PRIMARY KEY ... are they broken?

			regards, tom lane

Thu, 18 Nov 1999 23:14:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Primary key requires SERIAL 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We currently only allow the words PRIMARY KEY on a SERIAL column.
> 
> Say what?  There are ColConstraintElem and ConstraintElem productions
> for PRIMARY KEY ... are they broken?
> 
> 			regards, tom lane
> 

Oh, I see it now.  The grammer seems to only support it in SERIAL, but I
see it works now.  I guess i am surprised SERIAL PRIMARY creates the
index and sequence, while INTEGER PRIMARY only creates the index.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 18 Nov 1999 23:19:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Primary key requires SERIAL
At 10:26 PM 11/18/99 -0500, Bruce Momjian wrote:
>We currently only allow the words PRIMARY KEY on a SERIAL column.  Is
>there a reason we don't allow PRIMARY KEY on an integer field?  Seems it
>should be allowed.

Presumably the only reason to disallow this is to make life difficult
for those of us who want to port Oracle-based applications.

Given that Oracle represents a huge slice of the established market,
and given that in the past Postgres has been an "Oracle-friendly" db in
terms of dialectical support (nextval and currval on sequences being
germane to the subject at hand) one can only presume that the Postgres
development group wants to make porting of Oracle-ish systems difficult.

Why?

"Currently" must mean the 7.0-in-work because 6.5.1 supports primary
key on integer just fine.

Why support "serial" and not support "primary key on integer" when
Oracle rules the roost, not Sybase? 

If your statement's true, this is a horrible shift in direction for
the PostgreSQL project.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 18 Nov 1999 20:34:20 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Primary key requires SERIAL
At 11:19 PM 11/18/99 -0500, Bruce Momjian wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> > We currently only allow the words PRIMARY KEY on a SERIAL column.
>> 
>> Say what?  There are ColConstraintElem and ConstraintElem productions
>> for PRIMARY KEY ... are they broken?
>> 
>> 			regards, tom lane
>> 
>
>Oh, I see it now.  The grammer seems to only support it in SERIAL, but I
>see it works now.  I guess i am surprised SERIAL PRIMARY creates the
>index and sequence, while INTEGER PRIMARY only creates the index.

Oops, I guess I blew it by responding to a post by Bruce assuming he
was right.

Postgres supports a quasi-serial type by creating an index and
sequence (while Sybase supports it more transparently)

Postgres REALLY supports sequences much like Oracle (and others?
I don't know, my DB knowledge is very sketchy).  In Oracle, if
you define a primary key of type integer and want to sequence
it, you define a sequence and use "sequence_name.nextval" and
"sequence_name.currval".  This is very much like "nextval" and
"currval" in Postgres, and I presume no accident.

And in Oracle you create the sequence by hand - just like you do
in Postgres.

Personally, I think maintaining an "Oracle-ish" framework is wise,
for the simple selfish reason that I'm interested in porting 
Oracle-dependent SQL to Postgres.

If being "Oracle-ish" is still a goal (it was once a goal of at
least some of the implementors, it's obvious) then generating
the sequence just makes porting more difficult.

Actually, I think the inclusion of "serial" as a more integrated
type and leaving primary key stuff alone for existing types is
what makes sense.  You could provide a higher level of Sybase
portability without messing up us Oracle-derived folk.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 18 Nov 1999 20:43:53 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Primary key requires SERIAL
> At 11:19 PM 11/18/99 -0500, Bruce Momjian wrote:
> >> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> > We currently only allow the words PRIMARY KEY on a SERIAL column.
> >> 
> >> Say what?  There are ColConstraintElem and ConstraintElem productions
> >> for PRIMARY KEY ... are they broken?
> >> 
> >> 			regards, tom lane
> >> 
> >
> >Oh, I see it now.  The grammer seems to only support it in SERIAL, but I
> >see it works now.  I guess i am surprised SERIAL PRIMARY creates the
> >index and sequence, while INTEGER PRIMARY only creates the index.
> 
> Oops, I guess I blew it by responding to a post by Bruce assuming he
> was right.

Seems I can't realy on understanding what we support by just looking at
gram.y.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 18 Nov 1999 23:58:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Primary key requires SERIAL
> > >> > We currently only allow the words PRIMARY KEY on a SERIAL column.
> > Oops, I guess I blew it by responding to a post by Bruce assuming he
> > was right.
> Seems I can't realy on understanding what we support by just looking at
> gram.y.

You can, if you read carefully :)

The grammar allows *only* PRIMARY KEY on the SERIAL column
declaration, since the other keywords or clauses are either redundant
or nonsensical in the context of a serial column. As others have
pointed out, PRIMARY KEY is also allowed elsewhere.

                        - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 19 Nov 1999 06:32:14 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Primary key requires SERIAL
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> The grammar allows *only* PRIMARY KEY on the SERIAL column
> declaration, since the other keywords or clauses are either redundant
> or nonsensical in the context of a serial column.

Just to put another item on your todo list ;-) ...

I think it's poor practice to try to enforce such a restriction via
the grammar, because that way you cannot generate an error more
specific than "parse error near FOO".  It'd be better to allow the
same ColQualifier for SERIAL as for any other column type, and then to
put sanity checks in analyze.c that would complain about conflicting
specifications.  We have, or should have, most of those checks in
place already to catch conflicting ColQualifier entries for a plain
column type (eg, "foo int4 NULL NOT NULL").  Also, I do not like
generating hard errors for specifications that are merely redundant
("foo SERIAL NOT NULL"); is there any basis in the SQL spec for
refusing such constructs?

			regards, tom lane

Fri, 19 Nov 1999 01:48:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Primary key requires SERIAL 
> I think it's poor practice to try to enforce such a restriction via
> the grammar, because that way you cannot generate an error more
> specific than "parse error near FOO".  It'd be better to allow the
> same ColQualifier for SERIAL as for any other column type, and then to
> put sanity checks in analyze.c that would complain about conflicting
> specifications.  We have, or should have, most of those checks in
> place already to catch conflicting ColQualifier entries for a plain
> column type (eg, "foo int4 NULL NOT NULL").  Also, I do not like
> generating hard errors for specifications that are merely redundant
> ("foo SERIAL NOT NULL"); is there any basis in the SQL spec for
> refusing such constructs?

Basis? Basis?? Since SERIAL is an extension, there is not anything
defined explicitly. And SQL tends to be a context-sensitive language
(hmm, what's the term for that?) so it does things in different ways
all over the place; it's not very self-consistant. What *should*
happen with a declaration like "foo int NOT NULL NOT NULL"? One could
argue that the backend should just do it, or perhaps should reject
this as a possibly corrupted declaration.

When I first implemented SERIAL, I'm pretty sure I would have had
trouble checking for conflicting qualifiers. But maybe now all the
pieces are there to do it right. Will look at it...

Anyway, I agree with your points, and will put this on my ToDo. btw, I
still have an item about the parser swallowing multiple SERIAL or
PRIMARY KEY declarations (don't remember which right now); will get to
that also.

                   - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 19 Nov 1999 15:32:54 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Primary key requires SERIAL