Using SQLAlchemy with SQLite, Oracle, and PostgreSQL backends, write a Python snippet that bulk-inserts into a table with an autoincrement field and returns to Python the values of the autoincrement field. This operation must be safe in the present of concurrent inserts into the same table.
My understanding is that:
PostgreSQL can do this via INSERT ... RETURNING, and SQLAlchemy already supports that syntax.
Oracle can do this via INSERT ... RETURNING, but the way those results are returned is different from how the PostgreSQL native Python driver does it, and SQLAlchemy does not support that syntax, at least for bulk inserts.
SQLite cannot do this for bulk inserts at all, unless you either:
rely on exclusive locking and do arithmetic on the last inserted ID and the number of rows (scary, to say the least - and I'd really like to stop using exclusive locking)
select from the sequence in advance and then use those values explicitly in the insert (at least, I think this ought to work - but it definitely needs to be tested).
I don't think this operation needs to also include the conflict-resolution options of DM-21201, but it's possible we'll identify a need for that in the future.
I do think we'll want a guarantee that the order of returned IDs match the order of the rows provided, or have some other way to connect them if that's not true.
DM-21231Refactor Registry handling of dataset and associated tables