Thread: tweak CREATE SEQUENCE grammar
This patch makes a few minor changes to the parsing of CREATE SEQUENCE
to allow for some syntax variations consistent with SQL 2002 (or
whatever the official name of the current draft standard will be),
section 11.62:
START -> START [ WITH ]
INCREMENT -> INCREMENT [ BY ]
CYCLE -> [ NO ] CYCLE
Should this new syntax be the default? Being standards-compliant is
nice, and IMHO, the new syntax is nicer, anyway. But I'm not too
worried about that, and I haven't encouraged the new syntax in the
documentation.
BTW, there are a few other small changes needed, as well as the
addition of ALTER SEQUENCE, but other than that the new "sequence
generator" feature of the standard is almost exactly what we've always
implemented, AFAICT.
This should be saved for 7.4, please.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Attachment
This has been saved for the 7.4 release:
http://candle.pha.pa.us/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Neil Conway wrote:
> This patch makes a few minor changes to the parsing of CREATE SEQUENCE
> to allow for some syntax variations consistent with SQL 2002 (or
> whatever the official name of the current draft standard will be),
> section 11.62:
>
> START -> START [ WITH ]
> INCREMENT -> INCREMENT [ BY ]
> CYCLE -> [ NO ] CYCLE
>
> Should this new syntax be the default? Being standards-compliant is
> nice, and IMHO, the new syntax is nicer, anyway. But I'm not too
> worried about that, and I haven't encouraged the new syntax in the
> documentation.
>
> BTW, there are a few other small changes needed, as well as the
> addition of ALTER SEQUENCE, but other than that the new "sequence
> generator" feature of the standard is almost exactly what we've always
> implemented, AFAICT.
>
> This should be saved for 7.4, please.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Neil Conway writes: > This patch makes a few minor changes to the parsing of CREATE SEQUENCE > to allow for some syntax variations consistent with SQL 2002 (or > whatever the official name of the current draft standard will be), > section 11.62: Have they started ballotting on the new standard yet or is it just some intermediate stage that will be changed again at the next meeting? -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes: > Have they started ballotting on the new standard yet or is it just some > intermediate stage that will be changed again at the next meeting? They've started balloting, at least according to the blurb at the beginning of my copy of the standard. BTW, I got my draft copy of the standard here: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/23933607697154bf852566160054bcf7/fc799c12b530dfb188256b410063e8cf?OpenDocument Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway writes: > They've started balloting, at least according to the blurb at the > beginning of my copy of the standard. It seems that the proposed standard's sequence generators have a number of syntactical and semantic differences from ours, including the syntax to retrieve the next value, set the current value, define "serial" columns, and the fact that the sequences are apparently not allowed to contain holes. So I suggest that we address all of theses issues (at least on the syntax side) at once -- if we want to address them at all. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> It seems that the proposed standard's sequence generators have a
> number of syntactical and semantic differences from ours
Yeah, there are some differences, but overall I think the spec-defined
behavior is remarkably similar to what we currently implement.
> [spec defines different syntax to ] set the current value, define
> "serial" columns
Can you point me to where in the standard these are defined? (I looked
briefly but couldn't find it.)
> [SQL 200x] sequences are apparently not allowed to contain holes.
This passage suggests to me that sequences can contain holes (4.22,
p.71):
Changes to the current base value of a sequence generator are
not controlled by SQL-transactions; therefore, commits and
rollbacks of SQL-transactions have no effect on the base value
of a sequence generator.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Patch rejected. Seems we need to address all the SQL standards changes in this area in one patch. --------------------------------------------------------------------------- Neil Conway wrote: > This patch makes a few minor changes to the parsing of CREATE SEQUENCE > to allow for some syntax variations consistent with SQL 2002 (or > whatever the official name of the current draft standard will be), > section 11.62: > > START -> START [ WITH ] > INCREMENT -> INCREMENT [ BY ] > CYCLE -> [ NO ] CYCLE > > Should this new syntax be the default? Being standards-compliant is > nice, and IMHO, the new syntax is nicer, anyway. But I'm not too > worried about that, and I haven't encouraged the new syntax in the > documentation. > > BTW, there are a few other small changes needed, as well as the > addition of ALTER SEQUENCE, but other than that the new "sequence > generator" feature of the standard is almost exactly what we've always > implemented, AFAICT. > > This should be saved for 7.4, please. > > Cheers, > > Neil > > -- > Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Patch rejected. Seems we need to address all the SQL standards changes > in this area in one patch. Why's that? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Patch rejected. Seems we need to address all the SQL standards changes
>> in this area in one patch.
> Why's that?
My two cents: I'm not eager to change our existing semantics of
sequences. If we decide that SQL2002 matches our semantics then it
makes sense to accept their syntax as well as our historical one.
(Unlike Peter and Bruce, I see no reason not to do that work
incrementally, but OTOH there's not much reason not to do it in one
patch either.) But if we decide that the semantics aren't the same
then converging the grammar is probably a mistake.
Accordingly, I think we ought to resolve Peter's objection about the
semantics before deciding how to proceed. The bit Neil quoted sure
sounds like SQL2002 sequences behave like ours; Peter, why do you
think they are different?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > (Unlike Peter and Bruce, I see no reason not to do that work > incrementally, but OTOH there's not much reason not to do it in one > patch either.) Well, one reason is that the changes required to implement part of the standard (ALTER SEQUENCE) in a transaction-safe fashion might require significant changes to the current sequence implementation. However, I haven't looked at that closely, so that might not be the case. > Accordingly, I think we ought to resolve Peter's objection about the > semantics before deciding how to proceed. Yes, that's exactly what I was waiting for. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway writes:
> > [spec defines different syntax to ] set the current value, define
> > "serial" columns
>
> Can you point me to where in the standard these are defined? (I looked
> briefly but couldn't find it.)
The current value is set with ALTER SEQUENCE name RESTART WITH value;
(11.63). "serial" columns are defined like
foo int GENERATED BY DEFAULT AS IDENTITY [(sequence options)]
(11.4).
> This passage suggests to me that sequences can contain holes (4.22,
> p.71):
You're right.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes: > Neil Conway writes: > > Can you point me to where in the standard these are defined? (I looked > > briefly but couldn't find it.) > > The current value is set with ALTER SEQUENCE name RESTART WITH value; > (11.63). Great, thanks for the info. > "serial" columns are defined like > > foo int GENERATED BY DEFAULT AS IDENTITY [(sequence options)] Wow, that's verbose. I suppose it's still worth supporting, but you've got to wonder how the committee every arrived at that syntax... Since it seems the SQL 200x stuff has the same semantics as the sequences we already have, I think this patch is still suitable for application (I'll fix some more of the syntax stuff later, I'll definately have time before 7.4) -- Bruce, can you add this back to the queue for 7.4? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Neil Conway writes: > > > Can you point me to where in the standard these are defined? (I looked > > > briefly but couldn't find it.) > > > > The current value is set with ALTER SEQUENCE name RESTART WITH value; > > (11.63). > > Great, thanks for the info. > > > "serial" columns are defined like > > > > foo int GENERATED BY DEFAULT AS IDENTITY [(sequence options)] > > Wow, that's verbose. I suppose it's still worth supporting, but you've > got to wonder how the committee every arrived at that syntax... > > Since it seems the SQL 200x stuff has the same semantics as the > sequences we already have, I think this patch is still suitable for > application (I'll fix some more of the syntax stuff later, I'll > definately have time before 7.4) -- Bruce, can you add this back to > the queue for 7.4? Sure, would you shoot it over to me again? Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Sure, would you shoot it over to me again? No problem, the patch is attached. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Attachment
This has been saved for the 7.4 release:
http://candle.pha.pa.us/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Neil Conway wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Sure, would you shoot it over to me again?
>
> No problem, the patch is attached.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Neil Conway writes: > > "serial" columns are defined like > > > > foo int GENERATED BY DEFAULT AS IDENTITY [(sequence options)] > > Wow, that's verbose. I suppose it's still worth supporting, but you've > got to wonder how the committee every arrived at that syntax... The above line is just one example. Some of the key words can be changed to get different behavior. All together, it acts as a replacement of the DEFAULT clause. -- Peter Eisentraut peter_e@gmx.net
Patch applied. Thanks. --------------------------------------------------------------------------- Neil Conway wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Sure, would you shoot it over to me again? > > No problem, the patch is attached. > > Cheers, > > Neil > > -- > Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073