pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Bayer <mike_mp@zzzcomputing.com>
To: John Howroyd <jdhowroyd@googlemail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Guidance on INSERT RETURNING order
Date: Thu, 13 Apr 2023 09:32:37 -0400
Message-ID: <74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com> (raw)
In-Reply-To: <CAAGaYBxFNMK0xS6w8uM_z2M93hg539GHnBqdgDaYEQrbpT7inQ@mail.gmail.com>
References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com>
	<95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com>
	<CAN19dyed0o5eCUYSEq30q39SpFmeZ7WSEHBfR042YYTVoipHcg@mail.gmail.com>
	<CACLU5mT5x-76uy_Rh821-WfnD3Z2iuMsZL4y1dsSBK2ypERcmA@mail.gmail.com>
	<c876553a-18f7-db6e-4972-76961f43a4ce@aklaver.com>
	<CACLU5mQYZY0ZwdTDUE6NTfn=7SbO_JJ5qhttED5xaqkps27kng@mail.gmail.com>
	<c422c4ef-a284-e45e-b968-27bff221e993@evolvis.org>
	<be108555-da2a-4abc-a46b-acbe8b55bd25@app.fastmail.com>
	<84746f13-fc7f-9126-12f3-ae5fbd5e6b79@evolvis.org>
	<86f9eae7-acd6-45e7-b8a6-2a24b42fe306@app.fastmail.com>
	<CAAGaYBxXcjUewV2kH=fjZCRoXSCDZPsvkSGz10LCvnW71wCj5A@mail.gmail.com>
	<CAAGaYBxzi+Vv4x5-GD9rfFRpkkBgq_xnYXErR7Yk2c5a3wmS7w@mail.gmail.com>
	<cb9152e8-11b8-46fc-9b1a-15da0d78091d@app.fastmail.com>
	<CAAGaYBxFNMK0xS6w8uM_z2M93hg539GHnBqdgDaYEQrbpT7inQ@mail.gmail.com>



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





view thread (56+ messages)  latest in thread

Message-ID: <74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com>
Permalink:  ../74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com/
Also on:    postgresql.org/message-id/74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: mike_mp@zzzcomputing.com, jdhowroyd@googlemail.com
  Subject: Re: Guidance on INSERT RETURNING order
  In-Reply-To: <74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com>
  References: <CAN19dycS0oUorLOGgjxMX8NZq-hOA1RJrzwC4L3FxpMf4BQbBQ@mail.gmail.com> <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> <CAN19dyed0o5eCUYSEq30q39SpFmeZ7WSEHBfR042YYTVoipHcg@mail.gmail.com> <CACLU5mT5x-76uy_Rh821-WfnD3Z2iuMsZL4y1dsSBK2ypERcmA@mail.gmail.com> <c876553a-18f7-db6e-4972-76961f43a4ce@aklaver.com> <CACLU5mQYZY0ZwdTDUE6NTfn=7SbO_JJ5qhttED5xaqkps27kng@mail.gmail.com> <c422c4ef-a284-e45e-b968-27bff221e993@evolvis.org> <be108555-da2a-4abc-a46b-acbe8b55bd25@app.fastmail.com> <84746f13-fc7f-9126-12f3-ae5fbd5e6b79@evolvis.org> <86f9eae7-acd6-45e7-b8a6-2a24b42fe306@app.fastmail.com> <CAAGaYBxXcjUewV2kH=fjZCRoXSCDZPsvkSGz10LCvnW71wCj5A@mail.gmail.com> <CAAGaYBxzi+Vv4x5-GD9rfFRpkkBgq_xnYXErR7Yk2c5a3wmS7w@mail.gmail.com> <cb9152e8-11b8-46fc-9b1a-15da0d78091d@app.fastmail.com> <CAAGaYBxFNMK0xS6w8uM_z2M93hg539GHnBqdgDaYEQrbpT7inQ@mail.gmail.com> <74debd57-5073-4465-9bd7-2123f0d94880@app.fastmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox