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

On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote:
> On 10/21/2012 12:36 PM, Andres Freund wrote:
> > On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote:
> >> On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote:
> >>> At 2012-10-21 11:49:26 -0400, cbbrowne(at)gmail(dot)com wrote:
> >>>> If there is a natural sequence (e.g. - a value assigned by nextval()),
> >>>> that offers a natural place to apply the usual order-imposing ORDER BY
> >>>> that we are expected to use elsewhere.
> >>>
> >>> Note: "INSERT … RETURNING" doesn't accept an ORDER BY clause.
> >>
> >> No, but you can wrap the INSERT .. RETURNING in a CTE and order that.
> >
> > Personally I find that a not very practical suggestion. It means you need
> > the ability to sort the data equivalently on the clientside which isn't
> > always easy if you consider platform/locale and whatever differences.
>
> Er, what?
>
> with orig_inserts as
> (
> insert into table_1
> ...
> returning *
> ),
> ordered_inserts as
> (
> select * from orig_inserts
> order by ...
> )
> insert into table_2
> select * from ordered_inserts ...;

I am not sure I get the point of this.

> why does the client have to be involved, exactly?

Suppose you have something like

CREATE TABLE positionlog(
id serial primary key,
timestamp timestamptz DEFAULT NOW(),
position geometry
);

And you want to insert multiple values in one roundtrip *and* know their ids
in your application.

INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;

If you want to correlate re returned ids with data in your application without
relying on the ordering of INSERT ... VALUES... RETURNING you would need to
sort a postgis type in the same way the server does it.
Am I missing something here?

Greetings,

Andres

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services