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