what is the processing order for cursor.executemany() and how does this relate to RETURNING into OUT parameters

[zzzeek]

hey all -

For SQLAlchemy, in 2.0 we released a fast insertmanyvalues feature that integrates RETURNING. The ORM needs to be able to INSERT lots of rows fast and get back server-generated primary key values.

however, the wrinkle we are now in the process of ironing out, our mistake, is that the ORM has an existing set of data records up front that it matches to these RETURNING rows. Meaning, it assumes the order that rows were sent to the database is the same order that comes back in RETURNING. It turns out no database we've tried so far actually guarantees this for a single INSERT statement with many values encoded into it which is then run via cursor.execute(), and MS SQL Server actually has cases where the order isn't maintained right now, hence we have realized we really screwed up with this assumption across the board.

Things are slightly different for Oracle, as we are actually using cursor.executemany(), as this driver has the unique ability to append to a set of OUT parameters for each row inserted (@anthony-tuininga you had given me this tip in maybe an email thread somewhere, it works great).

But we would then need to know if Python-oracledb (and cx_oracle) do in fact maintain the order given for rows passed to executemany(). the docs for this use case seem to be at https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html#dml-returning and while it seems to strongly imply we can use this ordering, it's not explicitly stated. Note this is also not in pep-249 and when I just asked on DB SIG, they were glad to proclaim no way, we can't assume that at all, we'll update the spec.

from my POV it seems completely insane that any of these databases would want to reorder the rows given and no longer match for RETURNING, but we've now gotten confirmation from DB-SIG, Postgresql, SQLite, MS SQL Server, that nope, RETURNING is basically random order for a single statement of any kind, assume nothing.

Can python-oracledb/cx_oracle confirm (and maybe document) that cursor.executemany() is guaranteed to process the records given in the order given, and that the use of RETURNING will return records in the same order as the corresponding input records were given?