Improve support of native MERGE with RETURNING merge_action()

Problem/Motivation

PostgreSQL 15 added the native MERGE statement. The Merge query of the Database API does not use it. The generic \Drupal\Core\Database\Query\Merge emulates a merge with a SELECT followed by an INSERT or an UPDATE. That is two round trips per merge, and the two-step approach is open to race conditions between the check and the write.

The minimum requuired version for Drupal 12 is PostgreSQL 18, so the driver can rely on MERGE (PostgreSQL 15) and RETURNING merge_action() (PostgreSQL 17) being available.

Core runs merge queries in three places: \Drupal\Core\KeyValueStore\DatabaseStorage::setIfNotExists() on the key_value table, \Drupal\locale\StringDatabaseStorage::updateLocation() on the locales_location table when translatable strings are saved with new locations, and TaxonomyHooks::buildNodeIndex() on the taxonomy_index table, once per term, when a published node is saved. Contrib modules also use the Merge query for insert-or-update writes, such as counters and log records.

Proposed resolution

Add a driver-specific merge implementation to the PostgreSQL driver:

• New class \Drupal\pgsql\Driver\Database\pgsql\Merge, returned by the driver's Connection::merge(). It executes the merge as a single native MERGE statement with a constant single-row source relation (USING (SELECT 1)).
• The statement ends with RETURNING merge_action(). From that value, execute() returns Merge::STATUS_INSERT, Merge::STATUS_UPDATE, or NULL when the row existed and no update was requested.
• The generic emulation of the parent class is kept for queries that a native MERGE cannot express or would not serve:
  • the condition targets a table other than the merge table, so no ON clause can be built;
  • there are no fields to insert, so no WHEN NOT MATCHED THEN INSERT clause can be generated;
  • values are inserted into serial fields, because the generic insert path synchronizes the sequences afterwards.

Performance

Benchmark with a standalone Database API script: 9 rounds of 5000 merge queries on a table with a varchar primary key and two data columns, median round time. Environment: PostgreSQL 18.4 and PHP 8.5 in separate ddev (Docker) containers on one machine. The native MERGE runs one statement per merge, the generic emulation runs two (SELECT plus INSERT or UPDATE). A smaller run (7 rounds of 1000 merges) gave the same picture: 16% to 20% less time per query.

These numbers are from a local, low-latency connection between web and database containers. The saving is one network round trip per merge, so the gain grows with the latency between the web server and the database server.

Remaining tasks

• Code review of the merge request.
• Decide if the CI change stays in this issue or moves to its own issue.
• Benchmark on a setup with network latency between the web and database servers.

User interface changes

None.

API changes

None. The new Merge class is internal to the PostgreSQL driver and keeps the return values of \Drupal\Core\Database\Query\Merge::execute().

Data model changes

None.

Release notes snippet

On PostgreSQL, merge queries now execute as a single native MERGE statement instead of a SELECT followed by an INSERT or an UPDATE.

For the committer

The changes to the .gitlab-ci.yml file need to be removed before merging!