Re: COPY and Volatile default expressions

On Mon, Apr 15, 2013 at 4:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I think plenty of people would be upset if row serial numbers assigned
> with nextval() were not assigned in the order of the incoming rows.
> The argument that you can get gaps in the sequence in some corner cases
> (none of which apply within a single COPY operation, btw) doesn't
> entitle us to violate the POLA to that extent.

I agree.

> After looking again at the code Simon is concerned about, though,
> whether we are willing to allow volatile function calls to be reordered
> has approximately nothing to do with this COPY optimization. Rather,
> the thing that makes it safe is that nextval() isn't going to look at
> the COPY target table, and thus whether or not the previous rows have
> been physically inserted isn't important. The same goes for the UUID
> example. So I think he's done himself a disservice by talking about
> reordering and bringing up the question of parallel queries. What we
> ought to be thinking about is how we can be certain that a function call
> isn't going to look at the uncommitted table rows; no more and no less.

Yep.

> In this context, I think we could do a lot worse than to special-case
> nextval(), because it's hard to see a really principled function
> attribute definition that would admit it here. It does look at, and
> even modify, uncommitted database state. We know it's safe because a
> sequence relation couldn't be the target of COPY ... but that reasoning
> doesn't fit nicely into anything I think we'd want to expose to users.
>
> OTOH, the notion that a UUID generator doesn't touch *any* database
> state seems like it might be worth treating as a general function
> property: it's simple to understand and applies to a lot of other
> volatile functions such as random() and clock_timestamp().

I think that's right; and I also think that's something that could be
useful in other contexts. It also has the advantage of being a
*checkable* property. That is, if a function is marked as changing no
database state, we can set a flag on entry into that context and clear
the flag on exit. If in the middle any attempt is made to change the
database state, then we can throw an error. This would be pretty good
insurance against the possibility that future optimizations based
around that flag would cause behavioral differences.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company