Re: Extended SERIAL parsing - Mailing list pgsql-hackers

> Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
>> after some experimentation, I came up with the attached patch,
>> which implements parsing the following SERIAL types:
>
> As has been pointed out before, it would be a seriously bad idea to
> implement the SQL syntax for identity columns without matching the
> SQL semantics for them.  That would leave us behind the eight-ball
> when we wanted to implement the SQL semantics.  Right now we have
> a useful but non-standard semantics, and a useful but non-standard
> syntax, and those two should stick together.

Well, I read all sections of 5WD-02-Foundation-2003-09.pdf
where "identity" appears, here are the list of changes that will
be needed for an identity column:

- Only one identity column can appear in the column list.  I have to check for this at CREATE, TABLE, ALTER TABLE ADD
COLUMN and ALTER TABLE ALTER COLUMN.
 
- ALTER TABLE ALTER COLUMN ... RESTART [WITH] or SET  alter the sequence on the column.
- If colname is SERIAL GENERATED ALWAYS, then only "UPDATE SER colname = default" may occur.

Then there's the DROP default question: should PostgreSQL
allow it or not? What I found about this in the standard is this:
definitions of the DEFAULT clause, the identity column specification
and the generation clause are mutually exclusive, see 11.4.
So, if you cannot specify a DEFAULT for an identity column,
you must not be able to drop it, although this isn't expressed
in the standard, it's just my opinion.

Is there anything else? I haven't found any.
Or I can't read between the lines, which is a skill
that isn't required for reading standards. :-)

> I'm not too happy with converting SERIAL4 and SERIAL8 into reserved
> words, either, as I believe this patch does.

Not really, only IDENTITY is added to the list of reserved words,
serial/serial4/serial8/bigserial are just type names:

# create table serial8 (serial8 serial8 primary key);
NOTICE:  CREATE TABLE will create implicit sequence "serial8_serial8_seq"
for serial column "serial8.serial8"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"serial8_pkey" for table "serial8"
CREATE TABLE

The others (AS, GENERATED) were added to the
non-reserved keyword list.

> Some other things missing are documentation and pg_dump support.

I am working on that. The documentation is easier. :-)

Also note, that I misread the generated column syntax as part of the
identity column syntax. So, the parsing should only recognize

SERIAL [ GENERATED { ALWAYS | BY DEFAULT }                 AS IDENTITY [ ( sequence_options ) ] ]

which I already fixed here and the sequence_options list
cannot be empty as with my previous attempt.

Best regards,
Zoltán Böszörményi