Thread: NEXT VALUE FOR...
It would appear the spec was approved of before we got foo.nextval, so
here it is again.
NEXT VALUE FOR and CURRENT VALUE FOR where CURRENT is an unreserved
keyword and VALUE is not reserved in any way (ident with comparison to
"value").
This allows the default of a table to depend on a sequence, CASCADE drop
of the sequence removes removes references to it.
CURRENT VALUE FOR is an extension of the spec.
This gives us almost everything required for the Sequence feature (T176)
(as per the draft).
We're missing the datatype specification on the sequence.
CREATE SEQUENCE t AS numeric(130);
Rod Taylor <rbt ( at ) rbt ( dot ) ca> writes:
> Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
> value is not a reserved word)? Or should I hold onto that until the
> spec has gone through the final draft / release?
By that time we'll have done the Oracle-style foo.nextval, and it'll
become kind of a moot point ;-)
regards, tom lane
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
I will probably hold it in the queue until Tom returns.
---------------------------------------------------------------------------
Rod Taylor wrote:
> It would appear the spec was approved of before we got foo.nextval, so
> here it is again.
>
> NEXT VALUE FOR and CURRENT VALUE FOR where CURRENT is an unreserved
> keyword and VALUE is not reserved in any way (ident with comparison to
> "value").
>
> This allows the default of a table to depend on a sequence, CASCADE drop
> of the sequence removes removes references to it.
>
> CURRENT VALUE FOR is an extension of the spec.
>
>
> This gives us almost everything required for the Sequence feature (T176)
> (as per the draft).
>
> We're missing the datatype specification on the sequence.
>
> CREATE SEQUENCE t AS numeric(130);
>
>
> Rod Taylor <rbt ( at ) rbt ( dot ) ca> writes:
> > Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where
> > value is not a reserved word)? Or should I hold onto that until the
> > spec has gone through the final draft / release?
>
> By that time we'll have done the Oracle-style foo.nextval, and it'll
> become kind of a moot point ;-)
>
> regards, tom lane
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
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
Rod Taylor <pg@rbt.ca> writes:
> NEXT VALUE FOR and CURRENT VALUE FOR where CURRENT is an unreserved
> keyword and VALUE is not reserved in any way (ident with comparison to
> "value").
I see one pretty big problem with this: the SQL2003 spec says clearly
that multiple occurrences of NEXT VALUE FOR should all generate the same
value within a particular row. (See, eg, last sentence of 4.21.2 or the
detailed rules in 6.13. The motivation is analogous to the behavior of
current_timestamp I suppose.) I think it's a bad idea to use the spec's
syntax to represent a non-spec-compliant behavior, which is what this
patch would provide, because that would foreclose doing the right thing
later on.
Offhand I see no simple way to do what the spec asks for within Postgres
:-( but that doesn't mean we should ignore the requirement.
> CURRENT VALUE FOR is an extension of the spec.
If it's not required by the spec, why bother? Since currval is
nonstandard anyway it might as well use a less special-purpose,
more extensible syntax. I would still like to do the Oracle-like
nextval(seqname) idea sometime.
regards, tom lane
On Fri, 2004-05-07 at 14:38, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > NEXT VALUE FOR and CURRENT VALUE FOR where CURRENT is an unreserved
> > keyword and VALUE is not reserved in any way (ident with comparison to
> > "value").
>
> I see one pretty big problem with this: the SQL2003 spec says clearly
> that multiple occurrences of NEXT VALUE FOR should all generate the same
> value within a particular row. (See, eg, last sentence of 4.21.2 or the
<snip>
> Offhand I see no simple way to do what the spec asks for within Postgres
> :-( but that doesn't mean we should ignore the requirement.
You're right. I had missed that.
Does that mean the below insert should give both col1 and col2 the same
value?
CREATE TABLE test (
col1 integer DEFAULT NEXT VALUE FOR t_seq,
col2 integer DEFAULT NEXT VALUE FOR t_seq
);
INSERT INTO TABLE DEFAULT VALUES;
> How we do what the spec wants isn't entirely clear, but maybe we could > link all the supposed-to-track-each-other occurrences of NEXT VALUE FOR > late in the planning stage or during executor startup, somewhat like > what happens with aggregate functions. Isn't there a statement level memory location that we could shove a boolean variable? Then let the executor determine whether to call todays version of nextval() or currval() based on that?
Rod Taylor <pg@rbt.ca> writes:
> Does that mean the below insert should give both col1 and col2 the same
> value?
> CREATE TABLE test (
> col1 integer DEFAULT NEXT VALUE FOR t_seq,
> col2 integer DEFAULT NEXT VALUE FOR t_seq
> );
> INSERT INTO TABLE DEFAULT VALUES;
AFAICS that's what they're asking for.
What would make sense to me is to keep the current behavior for a
nextval() call written as a function (either the present syntax with a
text-string argument, or a hypothetical improved version that exposes
the sequence object more directly). But do it the spec's way for the
NEXT VALUE FOR syntax. Then people would have the choice of which
behavior they wanted, and also we'd not be taking any
backwards-compatibility hit.
How we do what the spec wants isn't entirely clear, but maybe we could
link all the supposed-to-track-each-other occurrences of NEXT VALUE FOR
late in the planning stage or during executor startup, somewhat like
what happens with aggregate functions.
regards, tom lane
Rod Taylor <pg@rbt.ca> writes:
> Isn't there a statement level memory location that we could shove a
> boolean variable?
Not per se, and anyway remember that this behavior is per sequence
generator not global.
I was just mentally designing something that I think might work. We
need two data structures: the first is a List hanging off the EState,
containing one shared entry for each sequence generator used in the plan
tree. The entry contents look like:
OID of sequence object (search key for finding list entry)
number of calls (initially zero, see below)
latest result of underlying nextval function (initially unset)
The ExprState record for a NEXTVALUE node has to contain a number of
calls counter and a pointer to the appropriate shared entry.
ExecInitExpr can fill this in (creating the shared entry if not already
present).
Then the algorithm for evaluating NEXTVALUE looks like:
1. Increment local number-of-calls counter.
2. Compare local counter to shared counter.
2a. If local > shared: this is first NEXTVALUE call of a new per-row
cycle. Call underlying nextval(), store its result in the shared
entry, set shared number-of-calls counter equal to local, return
nextval result.
2b. If local = shared: this is a duplicate NEXTVALUE call. Just return
the nextval() value already stored in the shared entry.
2c. If local < shared: apparently we missed being called during the last
cycle. Advance local counter to equal shared, and return the
already-stored nextval() value.
I suppose you could also argue for raising an error in case 2c; this
would suggest that the user is violating one of the constraints in the
spec about where he can put NEXT VALUE FOR (like putting it inside a
CASE where it may not get evaluated on every query cycle). But I think
that would be overly anal retentive, given that we're not making a
complete attempt to enforce those spec restrictions.
This all assumes that associating the NEXTVALUE state with an EState
is a good approximation of the spec's statements about how often to
advance the sequence counter. I think it is all right for simple cases
but I'm not totally convinced it is right for complicated queries.
(Also, should NEXTVALUE calls inside a function track those outside?
I think the way the spec is worded, they shouldn't, but someone else
should read it too.)
regards, tom lane
> This all assumes that associating the NEXTVALUE state with an EState > is a good approximation of the spec's statements about how often to > advance the sequence counter. I think it is all right for simple cases That is an awful lot of work to do in order to avoid implementing IDENTITIES and GENERATORS. Alas... For the amount of shuffling I seem to do between DB2 and PostgreSQL (not that I understand DB2 by any means) it is probably worth trying.
Patch withdrawn by author. --------------------------------------------------------------------------- Rod Taylor wrote: > It would appear the spec was approved of before we got foo.nextval, so > here it is again. > > NEXT VALUE FOR and CURRENT VALUE FOR where CURRENT is an unreserved > keyword and VALUE is not reserved in any way (ident with comparison to > "value"). > > This allows the default of a table to depend on a sequence, CASCADE drop > of the sequence removes removes references to it. > > CURRENT VALUE FOR is an extension of the spec. > > > This gives us almost everything required for the Sequence feature (T176) > (as per the draft). > > We're missing the datatype specification on the sequence. > > CREATE SEQUENCE t AS numeric(130); > > > Rod Taylor <rbt ( at ) rbt ( dot ) ca> writes: > > Are you ok with the DB2 and draft-spec syntax of NEXT VALUE FOR (where > > value is not a reserved word)? Or should I hold onto that until the > > spec has gone through the final draft / release? > > By that time we'll have done the Oracle-style foo.nextval, and it'll > become kind of a moot point ;-) > > regards, tom lane [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- 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