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 the final sort of "id" client side; we are only sending out batches of a few thousand rows at a time. The "order" is not actually 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 understand the wish for having one solution that fits all without dialectic dependencies. However, in my opinion, this one is going to run for some time and is heavily dependent on internals. With mssql, this seems to me to be deep down in the insert internals and unlikely to change any time soon (at least until they start losing market share because other DBMSs do it better). Here (PostgreSQL) the actual mechanisms required can probably be achieved much more readily (given the will). But the fundamental issue of matching code side objects with database records (with side effects) remains and 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, which was inspired by ours in the first place... (switching mail client to plain text, haven't used old school mailing lists in a long time...)
