Re: COPY and Volatile default expressions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: COPY and Volatile default expressions
Date: 2013-04-15 20:21:31
Message-ID: 7870.1366057291@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Apr 15, 2013 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jaime Casanova 2013-04-15 20:27:07 Re: Query not using index for user defined type
Previous Message Robert Haas 2013-04-15 20:18:27 Re: [PATCH] pg_regress and non-default unix socket path