Re: Guidance on INSERT RETURNING order

Hi All,

The fundamental question here is:
What does "insert into object(col1, ... , coln) select sel1, ... , seln
from ... order by ord1, ... ordm returning val1, ..., valk" mean?

In particular, what does the "order by" do in "insert ... select ... order
by"? Will this be honoured with parallel inserts? Is that really too much
to ask?

Is a DBMS just a black box archive of data (throw it in and forget about
it)? In my opinion, it's a system for the consistent and efficient storage
and *retrieval* of data, to be used in the context of other software
systems. Programmatically, that means matching up what's in the database
with what's stored in memory.

And yes, a DBMS needs to give guarantees! ACID compliance is one such
guarantee, but it is not the only one required.

Finally, please don't read this badly. It is meant purely to question
where we want to go, I strongly support PostgreSQL and believe it has made
many major contributions to the development of DBMS over the years.

Many thanks,
John.

On Wed, 12 Apr 2023 at 11:10, Federico <cfederico87(at)gmail(dot)com> wrote:

> On Wed, 12 Apr 2023 at 11:46, Thorsten Glaser <tg(at)evolvis(dot)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
> :-)
> >
> >
> >
> >
>
>
>
>
>