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...)
