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

On Sunday, October 21, 2012 08:45:31 PM Andrew Dunstan wrote:
> On 10/21/2012 01:40 PM, Andres Freund wrote:
> > 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.
>
> I see. Sorry, I should not have joined the thread late in the piece
> while I'm multitasking.
>
> I guess in such a case I'd be inclined to precompute the id values and
> then supply them in the values clause. That means two round trips rather
> than one.

Which will fail should we get upsert one day...

Andres

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