Re: COPY and Volatile default expressions

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Apr 15, 2013 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I don't believe that it's a good idea to consider nextval() to be
>> reorderable, so I'm not convinced by your argument here.

> Why not?

> I admit that I can't convince myself that it's safe.  But I can't
> think of a concrete example where it breaks anything, either.

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.

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.

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().
        regards, tom lane