Uncertainty about INSERT order of VALUES
There have been recently a discussion on PostgreSQL mailing list about the order of RETURNING.
13624e0fa4a2 relies on the idea that the returned ids correspond to the same order as the values. But this is an false assumption (even if for now this is the PostgreSQL implementation) and it may change in the future if parallel insert is implemented.
Tryton have an extra requirement compare to SQLAlchemy which is that the ORM is expecting that the IDs of the create tuple follow the same order as the inert order. This is because sequence order is optimized to not been set if the IDs order is already respected. So for example when the client creates the lines of a sale order, it does not fill the sequence because it is expected that the order of the lines will still be the same (based on IDs) as the order used to create them.
From the discussed solution, I think the best one (that should work without relying on any implementation detail) is to fetch IDs first and set them in the VALUES
. This will have the inconvenient to add an extra query for each grouped INSERT
but this stays still a good performance optimization if we group at least 3 tuples.
So the idea is to extend Database.nextid
to take as parameter a number of ids to return (and yield them). And the multirow insert will be available only for backend that implements Database.nextid
.
!448 will have to wait until python-sql#85 supports OVERRIDING
clause: