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

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Abhijit Menon-Sen
> Sent: Sunday, October 21, 2012 5:45 AM
> To: Tom Lane
> Cc: P. Christeas; pgsql-hackers(at)postgresql(dot)org
> Subject: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves
> the order of multi rows
>
> At 2012-10-17 09:56:22 -0400, tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
> >
> > > Clarify that in the documentation, and also write a test case that
> > > will prevent us from breaking the rule in the future.
> >
> > I don't believe this is a good idea in the slightest. Yeah, the
> > current implementation happens to act like that, but there is no
> > reason that we should make it guaranteed behavior.
>
> I always thought it *was* guaranteed, and I've encountered code written by
> other people who were obviously under the same impression: take some
> strings (e.g. flag names), use "insert … returning id", map the ids back to the
> names, and use the values in further inserts into other tables ("flag_id
> foreign key references flags").
>
> I know one could say "returning id, name", but there's certainly code out
> there that doesn't do this.
>
> I personally think the return order should be guaranteed; and if not, then the
> documentation urgently needs some prominent warnings to tell people that
> they should not assume this (for any variant of RETURNING).
>
> -- Abhijit
>

Order is never guaranteed unless an ORDER BY clause is involved in processing the data immediately prior to its use.

I could see this being in a "Rules that you must always remember" listing but to include it in every location where people might be inclined to rely upon ordering is just going to clutter the documentation.

That said, I'm not personally opposed to this documentation suggestion. But while the idea is acceptable the actual changes proposed by someone's patch is what needs to be approved and applied.

As to the order of RETURNING I do not see an overly compelling reason to enforce such a limitation; and in general implicit guarantees like this are undesirable since there is no way to turn them off. For sorting in particular the action itself can be expensive and not always needed. While we are not talking strictly sorting here (just maintained order) the concept still applies.

David J.