Re: Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote:
>> Note: "INSERT ... RETURNING" doesn't accept an ORDER BY clause.

> No, but you can wrap the INSERT .. RETURNING in a CTE and order that.

This is all a lot more dangerous than it looks, though. Whether or not
you believe that a VALUES clause is guaranteed to return its rows in
source order (which in practice it probably is), any such guarantee must
vanish the moment those rows undergo any further processing. For
instance if you join the VALUES with anything else, we are absolutely
not going to promise a thing about the ordering of the join result.
So the question here boils down to whether INSERT...RETURNING represents
sufficient "further processing" to void that guarantee.

In general I've got big reservations about promising anything about
the ordering of DML operations. We have had serious discussions for
instance about trying to do large UPDATE/DELETE operations in ctid
order to reduce buffer thrashing. That argument doesn't apply so much
to INSERTs --- but if the insert is affected by say a rule, it's not
obvious that there might not be good performance reasons for sticking
a sort step in there somewhere.

So, while we could maybe promise something for the *exact* case of
INSERT INTO foo VALUES ... RETURNING, I think it'd be bad policy.
The main practical effect would probably be to encourage people to
make assumptions about related but not in fact guaranteed behaviors.

IMO it'd be far better to maintain the public posture that "row order
is never guaranteed without an ORDER BY", because (a) that rule is
simple enough that people can actually remember it, and (b) it's not
going to constrain future optimization efforts.

(BTW, one reason I find the proposed regression test laughable is that
it's only testing the behavior for a small number of rows. If we ever
did want to mess with the output order of VALUES, it'd likely be because
somebody had found a way to make it a bit faster for many thousands
of rows, by sticking them into a hash table or some such. There is
basically no case where the planner's behavior for a trivial number of
rows is a reliable guide to what it will do for larger problems,
anyway.)

regards, tom lane