On Wed, Apr 12, 2023 at 5:49 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 4/12/23 2:35 PM, Kirk Wolak wrote:
> On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87@xxxxxxxxx
>
> 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 setoftransactions. 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@xxxxxxxxxxx
