Re: Guidance on INSERT RETURNING order

On Thu, Apr 13, 2023, at 8:26 AM, John Howroyd wrote:
> 
> However, the marshalling is presumably already happening (except any tie to ordering to the original declared
tuples);otherwise I really don't understand how the with syntax (I think first suggested by Adrian Klaver) would work.
@SQLAlchemy,if you wanted to off load reordering to database side a combination solution might be considered:
 
> 
> with rslt as (
>   INSERT INTO mytable (a, b) 
>   SELECT p1, p2 FROM (VALUES (10, 11, 1), (12, 13, 2)) AS sel(p1, p2, num) ORDER BY num 
>   RETURNING mytable.id, mytable.other_auto_gen
> )
> select * from rslt
> order by id
> ;
> 
> should work (not yet tested); and could be modified to the "sentinel" columns.  

the solution I propose is essentially doing the same SQL as above, except we deliver the RETURNING in any order and do
thefinal sort of "id" client side; we are only sending out batches of a few thousand rows at a time.    The "order" is
notactually what's important, it's that we can relate server generated values to each tuple inside VALUES.
 


> @Mike Bayer: Isn't the order guarantee sufficient (wasn't that the original assumption in the 2.0 code)?  I do quite
understandthe wish for having one solution that fits all without dialectic dependencies.  However, in my opinion, this
oneis going to run for some time and is heavily dependent on internals.  With mssql, this seems to me to be deep down
inthe insert internals and unlikely to change any time soon (at least until they start losing market share because
otherDBMSs do it better).  Here (PostgreSQL) the actual mechanisms required can probably be achieved much more readily
(giventhe will).  But the fundamental issue of matching code side objects with database records (with side effects)
remainsand is only going to become more prominent.  @PostgreSQL, isn't this your opportunity to lead the way again!-).
 

oh like I did for database URLs, right ? :)  which we then got in trouble for because we weren't using *pgs* format,
whichwas inspired by ours in the first place...
 



(switching mail client to plain text, haven't used old school mailing lists in a long time...)