Обсуждение: Guidance on INSERT RETURNING order

Hello list,

I have a few clarification questions regarding using insert with returning.
The use case is SQLAlchemy development, where the orm wants to insert
a list of rows,
get back the generated ids, defaults, etc, and match the returned values with
the original list of orm objects.

The following assumes a table like this

    CREATE TABLE t(
        id SERIAL,
        data TEXT -- type here can be anything
    )

On PostgreSQL, since SQLAlchemy version 1.4 (Mar 2021) this operation has used

  INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING id

but we were recently made aware that there is no guarantee on the
order of the returned columns.
Looking at the documentation there is no mention of the order of the
RETURNING clause, but searching
past discussion there are multiple indication that the order is not
guaranteed, like
https://www.postgresql.org/message-id/19445.1350482182%40sss.pgh.pa.us
. I think the docs
should mention this, similar to what the sqlite docs do at
https://www.sqlite.org/lang_returning.html#limitations_and_caveats

Searching the archive seems that a using the INSERT SELECT ORDER BY
form should be a better solution,
so the above insert should be rewritten as

    INSERT INTO t(data)
    SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
num) ORDER BY num
    RETURNING id

to ensure that the id are created in the order specified by num. The
returned id can again be in
arbitrary order, but sorting them should enable correctly matching the
orm object so that they can
be properly updated.
    Is this correct?
The documentation does not say anything about this, and looking at the
archive it seems that
it's mostly correct but not 100% guaranteed, as stated here
https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us .
The MSSQL docs, for example, clearly state that this is the case

https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16#limitations-and-restrictions
,
so it would be helpful if something similar were mentioned in the
PostgreSQL docs.

The above insert form (INSERT SELECT ORDER BY) can be used when the
primary key is an auto incrementing value,
in case it isn't (such as when it's an UUID), another solution must be used.
Since there does not seem to be any way of getting the position of the
original row inside
the VALUES clause with RETURNING, the solution SQLAlchemy is
implementing is to either degrade to
inserts with a single value or to optionally allow the user to add a
"sentinel" column to the table,
so that a sequential value can be inserted into it and then returned
allowing the ordering of the
RETURNING clause rows:

    ALTER TABLE t ADD COLUMN sentinel SMALLINT

    INSERT INTO t(data, sentinel) VALUES ('a', 1), ('b', 2), ('c', 3)
RETURNING id, sentinel

Is there any better solution to achieve this? (For reference this
feature is tracked in SQLAlchemy by
https://github.com/sqlalchemy/sqlalchemy/issues/9618)

From an ORM standpoint it would be very useful having a way of forcing
the order of RETURNING
to be the same as the one in VALUES, maybe with an additional keyword.
Alternatively having a system column
or other function that can be placed into the returning clause to
return the output row position wrt the
input values list, similar to what the sentinel column above does.

At the very least I think the documentation could do a better job at
mentioning that RETURNING order is
arbitrary, and documenting that INSERT SELECT ORDER BY precesses the
default in select order
(if that's indeed the case)

Sorry for the long email,
Thanks

    Federico

On 4/11/23 12:47, Federico wrote:
> Hello list,
> 
> I have a few clarification questions regarding using insert with returning.
> The use case is SQLAlchemy development, where the orm wants to insert
> a list of rows,
> get back the generated ids, defaults, etc, and match the returned values with
> the original list of orm objects.
> 
> The following assumes a table like this
> 
>      CREATE TABLE t(
>          id SERIAL,
>          data TEXT -- type here can be anything
>      )
> 
> On PostgreSQL, since SQLAlchemy version 1.4 (Mar 2021) this operation has used
> 
>    INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING id
> 
> but we were recently made aware that there is no guarantee on the
> order of the returned columns.

1) Because returned data in SQL is inherently unordered.

2) What would you order by, id or data or both?



> Sorry for the long email,
> Thanks
> 
>      Federico
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/11/23 12:47, Federico wrote:
> Hello list,
> 
https://www.sqlite.org/lang_returning.html#limitations_and_caveats
> 
> Searching the archive seems that a using the INSERT SELECT ORDER BY
> form should be a better solution,
> so the above insert should be rewritten as
> 
>      INSERT INTO t(data)
>      SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
> num) ORDER BY num
>      RETURNING id

Or

with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3) 
returning id)
select i.id from i order by id;

> Sorry for the long email,
> Thanks
> 
>      Federico
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Στις 11/4/23 23:06, ο/η Adrian Klaver έγραψε:
> On 4/11/23 12:47, Federico wrote:
>> Hello list,
>>
> https://www.sqlite.org/lang_returning.html#limitations_and_caveats
>>
>> Searching the archive seems that a using the INSERT SELECT ORDER BY
>> form should be a better solution,
>> so the above insert should be rewritten as
>>
>>      INSERT INTO t(data)
>>      SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
>> num) ORDER BY num
>>      RETURNING id
>
> Or
>
> with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3) 
> returning id)
> select i.id from i order by id;
+1 for this version!
>
>> Sorry for the long email,
>> Thanks
>>
>>      Federico
>>
>>
>
-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

Federico <cfederico87@gmail.com> writes:
> Searching the archive seems that a using the INSERT SELECT ORDER BY
> form should be a better solution,
> so the above insert should be rewritten as

>     INSERT INTO t(data)
>     SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
> num) ORDER BY num
>     RETURNING id

> to ensure that the id are created in the order specified by num. The
> returned id can again be in
> arbitrary order, but sorting them should enable correctly matching the
> orm object so that they can
> be properly updated.
>     Is this correct?

No.  Sadly, adding that ORDER BY is just voodoo programming, because
it applies to the result of the SELECT while promising nothing about
the order in which INSERT/RETURNING will act on those rows.

Re-reading that 2012 thread, the main new observation I'd make today
is that parallel operation is a thing now, and it's not hard to foresee
that sometime soon we'll want to parallelize INSERTs.  Which'd make it
*really* hard to promise anything about the order of RETURNING output.

I think if you want to use RETURNING with multi-row inserts, the
thing to do is more like

  INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id

and then explicitly match up the returned "data" values rather than
presuming they appear in the same order you wrote them in in VALUES.
Admittedly this might be problematic if some of the VALUES rows
are identical, but how much should you care?

            regards, tom lane

Thanks for the ansers

> 2) What would you order by, id or data or both?

by values order, (that incidentally seems to be what PG does)

> with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3)
> returning id)
> select i.id from i order by id;

The problem here is not having the auto increment id in a particular
order, is that there
is apparently no correlation with the position of an element in the
values clause with the
id generated. That's the reason for using the sentinel column in the
general solution in the previous message.

The extend on the use case, SQLAlchemy has 3 objects T that have
T(data='a'), T(data='b'), T(data='c') but no
value for the id column. The objective is to insert the 3 data values,
get back the ids and correctly match them with
the correct 3 objects.

> No.  Sadly, adding that ORDER BY is just voodoo programming, because
> it applies to the result of the SELECT while promising nothing about
> the order in which INSERT/RETURNING will act on those rows.

I wasn't probably clear, it's fine if INSERT/RETURNING order is
arbitrary, what matters is that the
autoincementing values is executed in the same order as select, like
mentioned in this
previous message
https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us

Is that not the case?

> Re-reading that 2012 thread, the main new observation I'd make today
> is that parallel operation is a thing now, and it's not hard to foresee
> that sometime soon we'll want to parallelize INSERTs.  Which'd make it
> *really* hard to promise anything about the order of RETURNING output.

I think it's fine not promising anything about the order of RETURNING, but
it would be very helpful having a way of tracking what input row
generated a particular
output row. Basically the sentinel case in the original post,
without actually having to insert the sentinel into the table.

> I think if you want to use RETURNING with multi-row inserts, the
> thing to do is more like
>
>   INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id
>
> and then explicitly match up the returned "data" values rather than
> presuming they appear in the same order you wrote them in in VALUES.
> Admittedly this might be problematic if some of the VALUES rows
> are identical, but how much should you care?

Well, the example is very easy, but it's hard to generalize when
inserting multiple columns
with possible complex values in them, since it would mean matching on
possibly large json values,
arrays, etc. So definitely not ideal

Thanks,
Federico

On Tue, 11 Apr 2023 at 22:06, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 4/11/23 12:47, Federico wrote:
> > Hello list,
> >
> https://www.sqlite.org/lang_returning.html#limitations_and_caveats
> >
> > Searching the archive seems that a using the INSERT SELECT ORDER BY
> > form should be a better solution,
> > so the above insert should be rewritten as
> >
> >      INSERT INTO t(data)
> >      SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
> > num) ORDER BY num
> >      RETURNING id
>
> Or
>
> with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3)
> returning id)
> select i.id from i order by id;
>
> > Sorry for the long email,
> > Thanks
> >
> >      Federico
> >
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

On Tue, 11 Apr 2023, Federico wrote:

>The problem here is not having the auto increment id in a particular

The id might not even be auto-increment but UUID or something…
(I am surprised you would even try to insert multiple rows at once.)

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

Federico <cfederico87@gmail.com> writes:

> Searching the archive seems that a using the INSERT SELECT ORDER BY

> form should be a better solution,

> so the above insert should be rewritten as

>     INSERT INTO t(data)

>     SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,

> num) ORDER BY num

>     RETURNING id

> to ensure that the id are created in the order specified by num. The

> returned id can again be in

> arbitrary order, but sorting them should enable correctly matching the

> orm object so that they can

> be properly updated.

>     Is this correct?

No.  Sadly, adding that ORDER BY is just voodoo programming, because

it applies to the result of the SELECT while promising nothing about

the order in which INSERT/RETURNING will act on those rows.

Re-reading that 2012 thread, the main new observation I'd make today

is that parallel operation is a thing now, and it's not hard to foresee

that sometime soon we'll want to parallelize INSERTs.  Which'd make it

*really* hard to promise anything about the order of RETURNING output.

I think if you want to use RETURNING with multi-row inserts, the

thing to do is more like

  INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id

and then explicitly match up the returned "data" values rather than

presuming they appear in the same order you wrote them in in VALUES.

Admittedly this might be problematic if some of the VALUES rows

are identical, but how much should you care?

regards, tom lane

On Tue, 11 Apr 2023 at 22:59, Thorsten Glaser <tg@evolvis.org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >The problem here is not having the auto increment id in a particular
>
> The id might not even be auto-increment but UUID or something…
> (I am surprised you would even try to insert multiple rows at once.)

Well the documentation makes no mention of any limitation on returning
and the observed behaviour has consistently been that returning is in
values order.
Again, that was SQLAlchemy's fault for assuming this (but the docs
surely did not help).

Also re-reading my reply, I've made a typo there, sorry. What it
should have read is:
  The problem here is not having the returned ids in a particular
order, is that there is apparently no correlation with the position of
an element in the values clause with the id generated.

Of course sorting the returned ids is only viable when using a serial
or identity column, that's why in the general case I've mentioned the
insert with sentinel column to ask if there are better or alternative
solutions.

Thanks for the reply, best
    Federico

>
> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>

On Tue, 11 Apr 2023, Federico wrote:

>Of course sorting the returned ids is only viable when using a serial

Yes, which is why I pointed out it doesn’t have to be.

>or identity column, that's why in the general case I've mentioned the
>insert with sentinel column

But it was pointed out that that’s not guaranteed either, unless you add
that sentinel column to the table itself…

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

On 4/11/23 14:37, Federico wrote:
>
> The problem here is not having the auto increment id in a particular
> order, is that there
> is apparently no correlation with the position of an element in the
> values clause with the
> id generated. That's the reason for using the sentinel column in the
> general solution in the previous message.
>
> The extend on the use case, SQLAlchemy has 3 objects T that have
> T(data='a'), T(data='b'), T(data='c') but no
> value for the id column. The objective is to insert the 3 data values,
> get back the ids and correctly match them with
> the correct 3 objects.
>
>> No.  Sadly, adding that ORDER BY is just voodoo programming, because
>> it applies to the result of the SELECT while promising nothing about
>> the order in which INSERT/RETURNING will act on those rows.
> I wasn't probably clear, it's fine if INSERT/RETURNING order is
> arbitrary, what matters is that the
> autoincementing values is executed in the same order as select, like
> mentioned in this
> previous message
> https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us
>
> Is that not the case?
>
>> Re-reading that 2012 thread, the main new observation I'd make today
>> is that parallel operation is a thing now, and it's not hard to foresee
>> that sometime soon we'll want to parallelize INSERTs.  Which'd make it
>> *really* hard to promise anything about the order of RETURNING output.
> I think it's fine not promising anything about the order of RETURNING, but
> it would be very helpful having a way of tracking what input row
> generated a particular
> output row. Basically the sentinel case in the original post,
> without actually having to insert the sentinel into the table.
>
>> I think if you want to use RETURNING with multi-row inserts, the
>> thing to do is more like
>>
>>    INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id
>>
>> and then explicitly match up the returned "data" values rather than
>> presuming they appear in the same order you wrote them in in VALUES.
>> Admittedly this might be problematic if some of the VALUES rows
>> are identical, but how much should you care?
> Well, the example is very easy, but it's hard to generalize when
> inserting multiple columns
> with possible complex values in them, since it would mean matching on
> possibly large json values,
> arrays, etc. So definitely not ideal
>
> Thanks,
> Federico
>
Can your client retain a hashmap of md5,data pairings, allowing the 
lookup on the way back using the returned data and supplied id?

On Tue, 11 Apr 2023 at 23:22, Thorsten Glaser <tg@evolvis.org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >Of course sorting the returned ids is only viable when using a serial
>
> Yes, which is why I pointed out it doesn’t have to be.
>
> >or identity column, that's why in the general case I've mentioned the
> >insert with sentinel column
>
> But it was pointed out that that’s not guaranteed either, unless you add
> that sentinel column to the table itself…

I was under the impression that when using INSERT SELECT ORDER BY the sequence
ids were generated using the select order.
That has been mentioned in multiple other previous messages, like
https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us

The above does not cover all cases, but in practice serial or identity
are very common,
so it would be nice if at least in these cases a sentinel is not needed

Thanks,
  Federico

> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>

On Tue, 11 Apr 2023 at 23:31, Rob Sargent <robjsargent@gmail.com> wrote:
>
> On 4/11/23 14:37, Federico wrote:
> >
> > The problem here is not having the auto increment id in a particular
> > order, is that there
> > is apparently no correlation with the position of an element in the
> > values clause with the
> > id generated. That's the reason for using the sentinel column in the
> > general solution in the previous message.
> >
> > The extend on the use case, SQLAlchemy has 3 objects T that have
> > T(data='a'), T(data='b'), T(data='c') but no
> > value for the id column. The objective is to insert the 3 data values,
> > get back the ids and correctly match them with
> > the correct 3 objects.
> >
> >> No.  Sadly, adding that ORDER BY is just voodoo programming, because
> >> it applies to the result of the SELECT while promising nothing about
> >> the order in which INSERT/RETURNING will act on those rows.
> > I wasn't probably clear, it's fine if INSERT/RETURNING order is
> > arbitrary, what matters is that the
> > autoincementing values is executed in the same order as select, like
> > mentioned in this
> > previous message
> > https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us
> >
> > Is that not the case?
> >
> >> Re-reading that 2012 thread, the main new observation I'd make today
> >> is that parallel operation is a thing now, and it's not hard to foresee
> >> that sometime soon we'll want to parallelize INSERTs.  Which'd make it
> >> *really* hard to promise anything about the order of RETURNING output.
> > I think it's fine not promising anything about the order of RETURNING, but
> > it would be very helpful having a way of tracking what input row
> > generated a particular
> > output row. Basically the sentinel case in the original post,
> > without actually having to insert the sentinel into the table.
> >
> >> I think if you want to use RETURNING with multi-row inserts, the
> >> thing to do is more like
> >>
> >>    INSERT INTO t(data) VALUES ('a'), ('b'), ('c') RETURNING data, id
> >>
> >> and then explicitly match up the returned "data" values rather than
> >> presuming they appear in the same order you wrote them in in VALUES.
> >> Admittedly this might be problematic if some of the VALUES rows
> >> are identical, but how much should you care?
> > Well, the example is very easy, but it's hard to generalize when
> > inserting multiple columns
> > with possible complex values in them, since it would mean matching on
> > possibly large json values,
> > arrays, etc. So definitely not ideal
> >
> > Thanks,
> > Federico
> >
> Can your client retain a hashmap of md5,data pairings, allowing the
> lookup on the way back using the returned data and supplied id?
>

When using unique columns or similar, that's something that is done,
but if there are no unique columns in the value no match can be done
reliably with the source data, since sqlalchemy is a library that
allows arbitrary schemas to be generated.

Thanks for the reply,
  Federico

On Tue, 11 Apr 2023, Federico wrote:

>I was under the impression that when using INSERT SELECT ORDER BY the sequence
>ids were generated using the select order.

But someone said that’s not guaranteed, especially when INSERT will
be parallelised later.

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

>> Can your client retain a hashmap of md5,data pairings, allowing the
>> lookup on the way back using the returned data and supplied id?
>>
> When using unique columns or similar, that's something that is done,
> but if there are no unique columns in the value no match can be done
> reliably with the source data, since sqlalchemy is a library that
> allows arbitrary schemas to be generated.
>
> Thanks for the reply,
>    Federico
So you're returned data is not what was sent to the server? Otherwise it 
should generate the same md5, as I understand it. Identical data would 
of course be a problem.

On Tue, 11 Apr 2023 at 23:44, Thorsten Glaser <tg@evolvis.org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >I was under the impression that when using INSERT SELECT ORDER BY the sequence
> >ids were generated using the select order.
>
> But someone said that’s not guaranteed, especially when INSERT will
> be parallelised later.

It was Tom Lane's message that said
> Re-reading that 2012 thread, the main new observation I'd make today
> is that parallel operation is a thing now, and it's not hard to foresee
> that sometime soon we'll want to parallelize INSERTs.  Which'd make it
> *really* hard to promise anything about the order of RETURNING output.

My reading of it is that we are talking about RETURNING, not about the order
in which the serial ids are generated. My understanding was that they
are generated
in select order, then the rows are inserted in any arbitrary order the
planner may choose
and returned again in any arbitrary order.

If my understanding is incorrect, would this alternative guarantee the above
(that nextval is called in the order set by ORDER BY),
again re-using the table in the original message?

    INSERT INTO t(id, data)
    SELECT nextval(pg_get_serial_sequence('t', 'id')) data
    FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
    ORDER BY num
    RETURNING id

best,
  Federico


> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>

On Tue, 11 Apr 2023 at 23:46, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> >> Can your client retain a hashmap of md5,data pairings, allowing the
> >> lookup on the way back using the returned data and supplied id?
> >>
> > When using unique columns or similar, that's something that is done,
> > but if there are no unique columns in the value no match can be done
> > reliably with the source data, since sqlalchemy is a library that
> > allows arbitrary schemas to be generated.
> >
> > Thanks for the reply,
> >    Federico
> So you're returned data is not what was sent to the server? Otherwise it
> should generate the same md5, as I understand it. Identical data would
> of course be a problem.
>

That should be the case, yes.
If a table has a non-nullable unique key, it should be possible to use
a hashmap and perform that lockup. We are planning on implementing
something like this to cover the cases where it can be used.

Thanks for the reply,
  Federico

On Tue, 11 Apr 2023, Federico wrote:

>My understanding was that they are generated in select order

But are they? (I don’t know, but I’d not assume that.)

>If my understanding is incorrect, would this alternative guarantee the above

>    INSERT INTO t(id, data)
>    SELECT nextval(pg_get_serial_sequence('t', 'id')) data
>    FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
>    ORDER BY num
>    RETURNING id

Wouldn’t, at that point, it be better to just send multiple
individual INSERT statements? The overhead (on both sides)
for all mentioned… workarounds… surely is larger than that?

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

On Tue, 11 Apr 2023 at 22:59, Thorsten Glaser <tg@evolvis.org> wrote:

>

> On Tue, 11 Apr 2023, Federico wrote:

>

> >The problem here is not having the auto increment id in a particular

>

> The id might not even be auto-increment but UUID or something…

> (I am surprised you would even try to insert multiple rows at once.)

Well the documentation makes no mention of any limitation on returning

and the observed behaviour has consistently been that returning is in

values order.

Again, that was SQLAlchemy's fault for assuming this (but the docs

surely did not help).

Also re-reading my reply, I've made a typo there, sorry. What it

should have read is:

  The problem here is not having the returned ids in a particular

order, is that there is apparently no correlation with the position of

an element in the values clause with the id generated.

Of course sorting the returned ids is only viable when using a serial

or identity column, that's why in the general case I've mentioned the

insert with sentinel column to ask if there are better or alternative

solutions.

Thanks for the reply, best

    Federico

>

> bye,

> //mirabilos

> --

> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

>

>

>

>

On Wed, 12 Apr 2023 at 11:46, Thorsten Glaser <tg@evolvis.org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >My understanding was that they are generated in select order
>
> But are they? (I don’t know, but I’d not assume that.)

That's kind of the point for this question, to see if that's correct or not.

> >If my understanding is incorrect, would this alternative guarantee the above
>
> >    INSERT INTO t(id, data)
> >    SELECT nextval(pg_get_serial_sequence('t', 'id')) data
> >    FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
> >    ORDER BY num
> >    RETURNING id
>
> Wouldn’t, at that point, it be better to just send multiple
> individual INSERT statements? The overhead (on both sides)
> for all mentioned… workarounds… surely is larger than that?

No, not by a long shot. Sending thousands of single inserts
sequentially over the network requires a lot more time even when doing
that on localhost.
Using a single statement is many times faster.

Federico

> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>

On 4/12/23 2:35 PM, Kirk Wolak wrote:
> On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87@gmail.com 

> 
> A couple of comments.  For the more generic, I prefer RETURNING *
> you get back all the columns for matching.  To me, this solves the 
> problem in a very generic way.

 From what I gather from the conversation RETURNING is the red herring.

The request is that for:

INSERT INTO some_table(char_fld) VALUES('a'), ('b'), ('c')

where some_table has an auto increment field that the values created for 
said field will always be done in the order that VALUES data was 
presented so:

SELECT id, char_fld from some_table will always return:

(1, 'a')
(2, 'b')
(3, 'c')

> But SQL (and SET THEORY) basically imply you cannot trust the sequencing 
> of a set of transactions.  Parallel execution is just a great simple 
> example.
> 
> Secondarily, many frameworks I've worked with (and custom ones 
> developed) would actually call the SEQUENCE.NEXTVAL, and assign the IDs, 
> in memory, accepting that we would have gaping holes if some 
> transactions were never actually sent to the server.  We did this a lot 
> in master-detail GUI type stuff.  It's just easier.  The children knew 
> their parent ID, and all the children ID's were effectively known before 
> committing.  It made for simple code that never failed.
> (for large datasets we would want one query that returned a set of IDs, 
> we could order that.  And apply it to the records we were about to 
> insert). [Be Careful with GENERATED ALWAYS pks to OVERRIDE]
> 
> HTH


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 12 Apr 2023 at 23:49, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87@gmail.com
>
> >
> > A couple of comments.  For the more generic, I prefer RETURNING *
> > you get back all the columns for matching.  To me, this solves the
> > problem in a very generic way.
>
>  From what I gather from the conversation RETURNING is the red herring.
>
> The request is that for:
>
> INSERT INTO some_table(char_fld) VALUES('a'), ('b'), ('c')
>
> where some_table has an auto increment field that the values created for
> said field will always be done in the order that VALUES data was
> presented so:
>
> SELECT id, char_fld from some_table will always return:
>
> (1, 'a')
> (2, 'b')
> (3, 'c')

It's actually for an insert query like this

INSERT INTO t(data)
    SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
num) ORDER BY num

Or even (but the above would be nicer)

    INSERT INTO t(id, data)
    SELECT nextval(pg_get_serial_sequence('t', 'id')) data
    FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
    ORDER BY num

  Federico
>
> > But SQL (and SET THEORY) basically imply you cannot trust the sequencing
> > of a set of transactions.  Parallel execution is just a great simple
> > example.
> >
> > Secondarily, many frameworks I've worked with (and custom ones
> > developed) would actually call the SEQUENCE.NEXTVAL, and assign the IDs,
> > in memory, accepting that we would have gaping holes if some
> > transactions were never actually sent to the server.  We did this a lot
> > in master-detail GUI type stuff.  It's just easier.  The children knew
> > their parent ID, and all the children ID's were effectively known before
> > committing.  It made for simple code that never failed.
> > (for large datasets we would want one query that returned a set of IDs,
> > we could order that.  And apply it to the records we were about to
> > insert). [Be Careful with GENERATED ALWAYS pks to OVERRIDE]
> >
> > HTH
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On Wed, 12 Apr 2023, Kirk Wolak wrote:

>The solution exists.  Pre-fetch the IDs, assign them and insert them with
>the IDs.  Then you have 100% control.
>SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, <total_needed>);

This would be a nice solution… but the PK could be always generated,
or not even sequential (UUIDs for example). If you’re developing
schema-first the application would not even (need to) know about how
the IDs are generated as it’s the DB that generates them.

AIUI the OP’s an SQLAlchemy developer, which is an ORM. Therefore he
needs a generic solution.

bye,
//mirabilos
--
15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)

> This would be a nice solution… but the PK could be always generated,
> or not even sequential (UUIDs for example). If you’re developing
> schema-first the application would not even (need to) know about how
> the IDs are generated as it’s the DB that generates them.
>
> AIUI the OP’s an SQLAlchemy developer, which is an ORM. Therefore he
> needs a generic solution.

Or a different dev env. :)

On Wed, 12 Apr 2023, Kirk Wolak wrote:

>The solution exists.  Pre-fetch the IDs, assign them and insert them with

>the IDs.  Then you have 100% control.

>SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, <total_needed>);

This would be a nice solution… but the PK could be always generated,

or not even sequential (UUIDs for example). If you’re developing

schema-first the application would not even (need to) know about how

the IDs are generated as it’s the DB that generates them.

AIUI the OP’s an SQLAlchemy developer, which is an ORM. Therefore he

needs a generic solution.

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

On 4/12/23 17:58, 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?

This for an ORM that will be using a sequence.
> 
> bye,
> //mirabilos

-- 
Adrian Klaver
adrian.klaver@aklaver.com

We do something different for uuids.  These are usually created client side anyway or are very easy to create client
sideif 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
insertof 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 :-)

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@googlemail.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@zzzcomputing.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 :-)

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

On 2023-04-11 15:45:59 -0600, Rob Sargent wrote:
> > > Can your client retain a hashmap of md5,data pairings, allowing the
> > > lookup on the way back using the returned data and supplied id?
> > >
> > When using unique columns or similar, that's something that is done,
> > but if there are no unique columns in the value no match can be done
> > reliably with the source data, since sqlalchemy is a library that
> > allows arbitrary schemas to be generated.

> So you're returned data is not what was sent to the server? Otherwise it
> should generate the same md5, as I understand it.

That's not necessarily the case. There are quite a few data types where
the input value is truncated, rounded or otherwise normalized. So I
don't think you can generally expect to read back exactly the same value
you inserted.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Fri, 14 Apr 2023 at 21:37, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Fri, Apr 14, 2023 at 11:42 AM John Howroyd <jdhowroyd@googlemail.com> wrote:
>>
>> @PostgreSQL:  Might I ask if this is still being actively considered or should we repost to another mailing list
(perhapspgsql-hackers or any other you might suggest)? 
>
>
> This is the right place for such a discussion.  Unless you think you've come up with a design that you want to
proposeand that, if accepted, you would then write up a patch for.  For that you'd probably want to move the discussion
to-hackers with an outline of the patch you plan to write. 
>
> David J.
>

Would something like what was proposed by Mike Bayer be considered?

> A new token called "tuple_order" or something
>
>  INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order
>
> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the each row delivered by RETURNING to
eachentry in the VALUES clause, in the order they were stated in that VALUES clause, that is entry (1, 2, 3) would be
tuple_order1, entry (4, 5, 6) would be tuple order 2, etc. 

This would allow easy reordering of the RETURNING clause, either
client side or moving the INSERT into a CTE and ordering the outside
select. I also don't think it would have any impact on parallel
processing of the INSERT, since RETURNING could output rows in any
arbitrary order.

Best,
    Federico

Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:

> The problem is that SQLAlchemy is an ORM [...]
...
> [...] as the majority of the python world will use this ORM for
> their database needs.

I wouldn't be so sure on this count ...

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On 4/14/23 15:44, Karsten Hilbert wrote:
> Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:
> 
>> The problem is that SQLAlchemy is an ORM [...]
> ...
>> [...] as the majority of the python world will use this ORM for
>> their database needs.
> 
> I wouldn't be so sure on this count ...

+1
> 
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Federico <cfederico87@gmail.com> writes:
> Would something like what was proposed by Mike Bayer be considered?

>> A new token called "tuple_order" or something
>>
>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order
>>
>> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the each row delivered by RETURNING to
eachentry in the VALUES clause, in the order they were stated in that VALUES clause, that is entry (1, 2, 3) would be
tuple_order1, entry (4, 5, 6) would be tuple order 2, etc. 

As proposed, I don't think so.  Something over in the RETURNING clause has
exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
and there are several VALUES clauses down inside the SELECT?

There is some prior art in this area, though.  See the more-or-less
SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
which would give you a rock-solid connection between the VALUES rows and
the ordinality-column values, and then you could include that column in
RETURNING.

            regards, tom lane

On Sat, 15 Apr 2023 at 05:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Federico <cfederico87@gmail.com> writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order
> >>
> >> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the each row delivered by RETURNING to
eachentry in the VALUES clause, in the order they were stated in that VALUES clause, that is entry (1, 2, 3) would be
tuple_order1, entry (4, 5, 6) would be tuple order 2, etc. 
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?

I think the tuple order would not be connected to the values, but be
determined by the input order of the rows in the insert. So when using
INSERT ... SELECT the tuple_order value would be determined by the
output of the select, using the same logic as ROW_NUMBER.

> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.

Well if returning could output rows that are not in the inserted
table, like mentioned also by Devid Johnston, it would open to
alternatives to tuple_order, like using

  INSERT INTO tbl(data)
  SELECT v.d FROM (VALUES ('x', 1), ('y', 2)) AS v(d, num)
  RETURNING tbl.id, v.num

>                         regards, tom lane

I'm not familiar with the internals of Postgresql, so I don't know
what would be more complex to implement in this case. I think both
solutions would work equally well from the client point of view.

Thanks for the reply,
     Federico

Federico <cfederico87@gmail.com> writes:
> I think the tuple order would not be connected to the values, but be
> determined by the input order of the rows in the insert. So when using
> INSERT ... SELECT the tuple_order value would be determined by the
> output of the select, using the same logic as ROW_NUMBER.

We already rejected the idea that INSERT must preserve the order of
the incoming tuples.  Please don't re-propose it with different
wording.

            regards, tom lane

On Sat, 15 Apr 2023 at 15:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Federico <cfederico87@gmail.com> writes:
> > I think the tuple order would not be connected to the values, but be
> > determined by the input order of the rows in the insert. So when using
> > INSERT ... SELECT the tuple_order value would be determined by the
> > output of the select, using the same logic as ROW_NUMBER.
>
> We already rejected the idea that INSERT must preserve the order of
> the incoming tuples.  Please don't re-propose it with different
> wording.
>
>                         regards, tom lane

That was not my intention, what I meant is that the hypothetical
inserted.tuple_order in case of insert .. select would be determined
by the output order of the select. Insert would be free to insert the
row as it pleases.
Sorry if it wasn't clear.

Best
  Federico

On Fri, Apr 14, 2023, at 11:17 PM, Tom Lane wrote:
> Federico <cfederico87@gmail.com> writes:
>> Would something like what was proposed by Mike Bayer be considered?
>
>>> A new token called "tuple_order" or something
>>> 
>>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING table.id, inserted.tuple_order
>>> 
>>> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the each row delivered by RETURNING to
eachentry in the VALUES clause, in the order they were stated in that VALUES clause, that is entry (1, 2, 3) would be
tuple_order1, entry (4, 5, 6) would be tuple order 2, etc.
 
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?

in my "plan", the token would not be supported and an error would be raised.   


>
> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.

I appreciate this idea!    Any kind of keyword / syntax that frees us from having to round-trip additional data to the
databaseand/or generate more complex syntaxes for certain kinds of default generation schemes would simplify the
approach.    
 

On Tue, 18 Apr 2023 at 00:21, John Howroyd <jdhowroyd@googlemail.com> wrote:
>
> May I clarify the ideas being discussed so far, perhaps with a view to make a relevant proposal.  My apologies if I
getanything wrong or go too far. 
>
> As I understand it the proposal is to supplement the syntax to something like:
>
> INSERT INTO table (a, b, c)
> VALUES ((1,2,3), (4,5,6), ...)
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning of which is to adjoin an ordinality column to the output reflecting the declaration order in the values
clause. So an output of (not necessarily in any order): 
> (1001, 1)
> (1003, 2)
> means that table.id = 1001 was assigned to the inserted row from tuple (1,2,3) (from VALUES, because that table.id is
associatedto ordinality = 1) and table.id = 1003 was assigned to the inserted row  from tuple (4,5,6).  The output
beingordered as determined by the internals of query execution (not necessarily the one shown). 
>
> Is that correct?

That would work as syntax for the task of tracking what id or other
server default is generated by a value clause tuple.

> I presume (although, not quite so clear) that one would have:
>
> INSERT INTO table (a, b, c)
> SELECT a_val, b_val, c_val
> FROM joined_tables
> WHERE some_condition
> ORDER BY something_relevant
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning being very much as before replacing 'declaration order' by 'row order of the SELECT statement as defined
bythe ORDER BY clause'; so pretty much like a row_number() but in the output of the RETURNING clause (and without an
OVERmodification).  I added the ORDER BY clause as I don't really see what this would mean without it; but this
(presumably)does not affect output order only the order of the incoming rows (and hence the generation of the
ordinalityoutput). 
>
> Is that correct?

This would not be needed if the syntax with VALUES WITH ORDINALITY is
added in sqlalchemy. So fine either way.
If "WITH ORDINALITY" is a feature of VALUES this syntax would not be
allowed though. I'm personally ok limiting WITH ORDINALITY only to
VALUES.

> Might there be a natural syntax to label the 'ordinality' output column?  Perhaps something like:
>
> ...
> WITH ORDINALITY (col_name)
> RETURNING table.id, col_name
> ;
>
> I don't want to clash with the syntax for Table Functions.
>
> Is it a step too far to propose allowing an additional ORDER BY clause after the RETURNING clause (a specific
declarationfor the query execution to assign cpu cycles; especially if the WITH ORDINALITY is not tied to output
order)?
>
> Personally, I didn't see Frederico's comment as anything to do with order; just how one could output additional
valuesin the RETURNING clause (namely, v.num from a subexpression of the SELECT but in whatever order it comes).  On
theother hand, that seems a lot more complicated to me because it is not an expression in the overall SELECT feeding
theINSERT, whereas the WITH ORDINALITY is a specific declaration to match input order with output order by inserting a
counter.

I didn't mean to suggest any particular order should be kept by insert
or by returning. I was merely commenting on the David G. Johnston
reply

     I suppose breaking the restriction that only columns present on
the insertion-table can be returned is a possible option that also
solves another infrequent request.

> Apologies, if I have misunderstood or invented something that's not possible!

Thanks for the recap. I'm hoping this can become a proposal.

Best,
  Federico

On Tue, 18 Apr 2023 at 11:53, John Howroyd <jdhowroyd@googlemail.com> wrote:
>
> Sorry, I may have jumped to a conclusion that's not quite correct.

Not at all, thanks for moving this along

> On Mon, 17 Apr 2023 at 23:58, Federico <cfederico87@gmail.com> wrote:
>>
>> On Tue, 18 Apr 2023 at 00:21, John Howroyd <jdhowroyd@googlemail.com> wrote:
>> > ...
>> >
>> > Personally, I didn't see Frederico's comment as anything to do with order; just how one could output additional
valuesin the RETURNING clause (namely, v.num from a subexpression of the SELECT but in whatever order it comes).  On
theother hand, that seems a lot more complicated to me because it is not an expression in the overall SELECT feeding
theINSERT, whereas the WITH ORDINALITY is a specific declaration to match input order with output order by inserting a
counter.
>>
>> I didn't mean to suggest any particular order should be kept by insert
>> or by returning. I was merely commenting on the David G. Johnston
>> reply
>>
>>      I suppose breaking the restriction that only columns present on
>> the insertion-table can be returned is a possible option that also
>> solves another infrequent request.
>>
>>
>> ...
>> Best,
>>   Federico
>
>
> This might be a possibility.  The v.num (from the original example) is accessible in the outer select, so one can a
envisagea system to handle this, but at (presumably) much greater expense: preparation of the SELECT, orchestration of
theoutput rows (some fields for INSERT some for RETURNING) and (presumably) a whole load of mem copies to RETURNING.
Isthis something to consider (perhaps just for feasibility while writing an initial patch based on WITH ORDINALITY)? 
>
> To put it another way, v.num is a valid expression in the overall SELECT, but still a lot more complicated (and
presumablyexpensive). 

I think it's probably best to focus only on INSERT VALUES WITH
ORDINALITY. Further enhancements can be added later if they seem
useful.

Best,
  Federico