Add support for unique / primary key constraints composed of multiple fields for Upsert querries, and start using it for config and key/value tables

Created on 7 August 2015, over 9 years ago

Updated 19 July 2020, over 4 years ago

Problem/Motivation

In #2542776: Add an Upsert class → , we added an UPSERT query to the database system which only worked on tables with one unique index / primary key, composed of a single field. This limitation was imposed by the minimum SQLite version supported by Drupal at the time, which lacked proper support for UPSERT queries. In the meantime, SQLite added a real UPSERT in 3.24.

Proposed resolution

For Drupal 9.x we increased the minimum version of SQLite to 8.26 in #3107155: Discuss lowering SQLite version requirement from 3.26 to 3.22 in Drupal 9 → , and one of the main motivations was the improved UPSERT capability, per #3107155-15: Discuss lowering SQLite version requirement from 3.26 to 3.22 in Drupal 9 → .

Now that all three core database drivers support UPSERT queries on unique/primary key constraints composed of multiple fields, we should take advantage of it and replace core's usage of "Merge" queries where possible.

The benefit is that a Merge query is potentially composed of 3 separate queries (SELECT, INSERT and UPDATE), while UPSERTs can do the same thing (in most situations) in a single query.

Remaining tasks

Review.

User interface changes

Nope.

API changes

API addition, TBD.

Data model changes

Nope.

Release notes snippet

TBD.

📌 Task

Status

Needs work

Version

11.0 🔥

Component

Database →

Last updated about 5 hours ago

Maintained by
🇳🇱Netherlands @daffie

Created by

🇷🇴Romania amateescu

Live updates comments and jobs are added and updated live.

sqlite

PostgreSQL
Particularly affects sites running on the PostgreSQL database.

Performance
It affects performance. It is often combined with the Needs profiling tag.

Incomplete comments

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Open in Jenkins → Open on Drupal.org →
Environment: PHP 8.1 & MySQL 5.7
last update over 1 year ago
Patch Failed to Apply
Open in Jenkins → Open on Drupal.org →
Environment: PHP 8.1 & MySQL 8
last update over 1 year ago
Patch Failed to Apply
Open in Jenkins → Open on Drupal.org →
Environment: PHP 8.1 & MySQL 8
last update over 1 year ago
Patch Failed to Apply
Comment over 1 year ago →
🇺🇸United States JonMcL Brooklyn, NY
@amateescu: Thank you for your work on this.

Unfortunately it no longer applies to 9.5.x or 10.x. Any chance you have rerolled this patch recently?
Comment 29 days ago →
🇺🇸United States mlncn Minneapolis, MN, USA
Stripped down, MySQL only, no tests, try at an updated patch. Sorry will try to do more. Though i do think the adding the upsert multivalue capability and using elsewhere in Drupal can and should be separated issues.

contrib.social Blog FAQ Discussions

Production build 0.71.5 2024