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