Handle serial ID reset consistently

Thanks for the feedback! I'm a bit concerned about BC: if this wasn't reported before, couldn't people be relying on the current non-MySQL behaviors?

Maybe a change record would be enough, if we only merge this in D12.

Pushed an update resetting serial IDs by default, unless within a transaction or reset skipped explicitly.

So we do not need a CR?

I'd say we do need a CR anyway, because we are adding a new option. There we can mention the behavior change, to ensure it's not missed.

We also need an issue summary update, but I'd like to get some feedback on the MR before working on those.

Improved readability and run the pipeline all the supported DBs.

@catch:

Most of the new logic in the MR is about supporting serial ID reset in the SQLite driver. Supporting the option itself is just a couple of lines for each driver. If we get rid of the option, we are preventing PgSQL from clearing tables efficiently without resetting serial IDs, which is what it's being doing so far and seems a valuable feature to retain, although maybe not the default behavior. The option is meant to capture the idea that some DB drivers are able to TRUNCATE tables without resetting serial IDs, while other ones need to rely on DELETE for that.

currently it looks like that's dependent on transaction behaviour?

Yes, core currently falls back to a DELETE inside a transaction, to prevent a TRUNCATE from causing an implicit commit. PgSql does not need this. The option just lets you get an explicit and predictable behavior with each driver.

@mondrake

No strong opinions, but whatever we choose we are going to break someone's workflows, because PgSQL/SQLite have been behaving differently from MySQL so far.

Regardless of the default behavior we pick, I think the use the case mentioned by @mondrake is another argument for keeping the option: both emptying a table without resetting its serial ID and fully truncating it are two valid and common use cases that require driver-specific logic to be implemented properly. I think we should support both. If the option is deemed too complex, we could go back to having different methods as in the initial iterations, but I don't think the complexity of the implementation would change, under the hood.

<?php
\Drupal::database()->truncate() // always reset serial IDs unless inside a transaction
\Drupal::database()->truncateWithSerial() // option = TRUE, always reset serial IDs
\Drupal::database()->truncateWithSerial(FALSE) // option = FALSE, never reset serial IDs

Or

<?php
\Drupal::database()->truncate() // never reset serial IDs
\Drupal::database()->truncateWithSerial() // always reset serial IDs