Avoid passing arbitrary values to the serial column on INSERT

Problem/Motivation

This will not be easy at all, but I think it would be good to at least have a discussion.

Passing an arbitrary value to a serial (autoincrement) column has very different outcomes on different database engines.

Supposing the max ID in the db is 1, and the arbitrary value of ID in INSERT is 1000:

• MySql and SQLite: the INSERT succeeds with ID=1000, and next record will have an ID=1001
• Postgres: the INSERT succeeds with ID=1000, and next record will have an ID=2 (Drupal currently works around this by adjusting the internal sequence to 1000, but it's rather expensive, as it needs introspecting the table's columns)
• DB2: the INSERT succeeds with ID=1000, and next record will have an ID=2
• Oracle: does not support autoincrement columns natively, but triggers can be built to emulate the behaviour
• MS SQL: the INSERT fails

More info: https://github.com/doctrine/dbal/issues/4725

Proposed resolution

• Consider prohibiting to pass an arbitrary value to a serial column.
• Evaluate where this is currently needed and find alternatives for each case.
• Find a way to fail an INSERT operation if an arbitrary value is passed in for the serial column (i.e. align to the strictest behavior, which is MS Sql's).

Remaining tasks

User interface changes

API changes

Data model changes

Release notes snippet