Re: NEXT VALUE FOR...

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