Re: NEXT VALUE FOR...

Rod Taylor <pg(at)rbt(dot)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