Re: Guidance on INSERT RETURNING order

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

> 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