Re: Guidance on INSERT RETURNING order

To clarify, the work around (for SQLAlchemy) does not require the RETURNING
values to be ordered; they will be reordered code side. Longer term
matching the order up would be an added bonus!

On Thu, 13 Apr 2023 at 04:02, John Howroyd <jdhowroyd(at)googlemail(dot)com> wrote:

> The ideal solution would be that the RETURNING values are in the same
> order as the declared rows in the table literal "FROM (VALUES (10, 11, 1),
> (12, 13, 2)) AS sel(p1, p2, num)". But before any of you jump down my
> throat about about sets and a bar to parallel inserts, I think it's a small
> matter of some internal smoke and mirrors: simply marshal the RETURNING
> results through and in memory/temporary table maintaining the order from
> the SELECT and return these to the client upon commit. At this moment, I
> believe the folk at SQLAlchemy are looking for a work around that can work
> for most use cases with either an identity column or a fake "sentinel"
> column for matching back to the ORM objects. There may be an upper limit
> of 1000 to the number rows in a single insert (this arises in MS SQL
> Server, who absolutely do not guarantee the order of their OUTPUT clause --
> it's tied to the physical row insert rather than marshalled).
>
> My feeling is that PostgreSQL may already do this. See the commit:
>
> https://github.com/postgres/postgres/commit/9118d03a8cca3d97327c56bf89a72e328e454e63
> and the original message thread
>
> https://www.postgresql.org/message-id/flat/CADBwkHv1nUx5bqxqRLp26k8iUWSnY3uW-1_0EMYxKns0t2JnUA%40mail.gmail.com
> and references to undesired behaviour prior to PostgreSQL 9.6 such as in
> https://www.postgresql.org/docs/current/sql-select.html.
>
> The test cases in the above commit use:
> SELECT nextval('sequence'), datavals FROM ... ORDER BY something;
> type syntax. And I presume that's exactly what's happening internally
> when there's a serial identity column (via query rewrite rules or another
> mechanism).
>
> So really, that just needs confirming. What happens with triggers?
> Presumably the same as long as query rewrite rules are used, but this is
> presumably getting deeper into the code for actual inserts after the
> initial select. The jump to the output being ordered, is just a guess but
> there's some bits and pieces that seem to suggest that there may indeed be
> a marshalling process going on in the background (whether or not that is
> linked to the original order is another matter).
>
> I have set up a PostgreSQL server to test if I can break this hypothesis
> and see what query explains can allude to. Does anyone have a test case
> where the order is not preserved?
>
> Might I also point out that if the implementation of parallel inserts does
> create a bar then doing so may end up with the programmatic interfaces
> (such as SQLAlchemy) not being able to use that feature (possibly reverting
> back to single inserts). Ur, so what would be the problem being solved
> with parallel inserts?
>
> On Thu, 13 Apr 2023 at 02:27, Mike Bayer <mike_mp(at)zzzcomputing(dot)com> wrote:
>
>> We do something different for uuids. These are usually created client
>> side anyway or are very easy to create client side if not and then we also
>> have a separate sentinel column option.
>>
>> Overall the whole thing is an optimization that can be turned off for
>> uncommon cases. We just can't go back to having insert of 1000 rows be
>> 3-5x slower for all tables w server generated primary keys which is what
>> statement at a time gets us. Surrogate integer Pks w sequence or identity
>> is the big case.
>>
>> On Wed, Apr 12, 2023, at 8:58 PM, Thorsten Glaser wrote:
>> > On Wed, 12 Apr 2023, Mike Bayer wrote:
>> >
>> >>ascending values for "mytable_id_seq" will correspond to the ascending
>> >>values for "num".
>> >
>> > But, again, what if it’s uuid or something instead of a sequence?
>> >
>> > bye,
>> > //mirabilos
>> > --
>> > 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for
>> helloworld :-)
>>
>>
>>