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@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