Re: Guidance on INSERT RETURNING order

On Wed, Apr 12, 2023 at 5:49 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87(at)gmail(dot)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')
>

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>);
// Update your structure, then insert, using these values. SINCE the
intention is to update your structure anyways.
// This simply changes the order of operation and requires nothing to work
in many environments

Or, with RETURNING *, assign them into your structure based on how the
system assigned the IDs
Clearly this is harder than the first suggestion. But it works, without
changing anything.

But I find the recommendation to make a DB adhere to ordering "non-ordered"
sets, especially when, as stated,
it would not allow for parallelism. I would much rather have parallelism
in my INSERTs than some arbitrary commitment
that the slew of data I throw at the DB be processed in an order for some
"edge case" that really doesn't simplify the coding.

>
> > 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(dot)klaver(at)aklaver(dot)com
>