[5.x]: GC on PostgreSQL can leave all FK triggers disabled and deletes stop cascading
メンテナーはふだん 1 日以内に返信
評価
- 難易度
- 4/5
- 見積もり時間
- 3〜5日
- 初心者へのやさしさ
- 48/100
調査の方向性
Start with src/services/Gc.php, especially _deleteOrphanedFkRows() and the checkIntegrity(false) call; compare its behavior with Yii's PostgreSQL QueryBuilder described in the issue. Reproduce on PostgreSQL by interrupting GC after triggers are disabled, then check pg_trigger for disabled triggers and verify cascading deletes. Done means GC cannot leave FK enforcement disabled after failure and does not disable it for other connections.
索引モデルが issue の本文から書いたものです。
説明
What happened?
Description
On PostgreSQL, Gc::_deleteOrphanedFkRows() disables integrity checks with checkIntegrity(false) (https://github.com/craftcms/cms/blob/5.x/src/services/Gc.php#L684), which Yii's pgsql QueryBuilder builds as ALTER TABLE ... DISABLE TRIGGER ALL for every table (https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/QueryBuilder.php#L223). The change is a schema change, not a session setting, so it applies to every connection. Integrity checks are only turned back on by the last statement of the method, not in a finally.
This causes two problems:
- If GC dies between those two points, every FK trigger in the database stays disabled indefinitely. GC runs on web requests (
probability), so a request that times out, hits a memory limit or throws partway through leaves the database with no FK enforcement. Deletes stop cascading, and every element, draft, revision, structure or plugin row deleted afterwards leaves orphans in its child tables. - Even when GC completes, every other request's deletes skip cascades while the loop runs, because the triggers are disabled for all connections, not just GC's.
On MySQL this doesn't happen: SET FOREIGN_KEY_CHECKS = 0 only lasts for GC's own connection and ends with it.
We found a production site where all 736 FK triggers (184 FKs × 4) were disabled. It had built up orphans in 27 FK relationships: elements_sites, entries, elements_owners, structureelements, relations, changedfields, Neo blocks, Blitz cache tables and searchindexqueue_fields. Most of them appeared after September 2026. Nothing errored on the site itself; it only showed up when:
- a
pg_dumpbackup failed to restore 27 of its FKs (the dump's data violates constraints that the source database still defines), and - a plugin migration that reads
elements_sites.content(Hyper 3's relation rebuild) failed on content rows whose element no longer exists.
You can check whether a site is affected with SQL:
SELECT count(*) FROM pg_trigger WHERE tgenabled = 'D';
Or with a Craft CLI:
php craft exec "Craft::\$app->db->createCommand(\"SELECT count(*) FROM pg_trigger WHERE tgenabled = 'D'\")->queryScalar()"
The garbage clean code that introduced the behaviour was 80d5c39 and has been unchanged since 032d500.
Our workaround is to set 'gc' => ['probability' => 0] in config/app.php and run the craft gc via a cron job.
Steps to reproduce
- Use Craft 5 on PostgreSQL, with a DB user that owns the tables so
DISABLE TRIGGER ALLsucceeds. - Make GC fail after the disable step. For example, run
craft gcand kill the process during "deleting orphaned foreign key rows", or throw from inside the FK loop. - Run
SELECT count(*) FROM pg_trigger WHERE tgenabled = 'D';. It's non-zero (every FK trigger). - Delete an entry with nested entries or Neo blocks: its
elements_sites,entries,elements_ownersetc. rows are left behind.
Expected behavior
- An error or timeout during GC should never leave FK enforcement switched off for the whole database.
- GC shouldn't turn off FK enforcement for other connections at all.
Actual behavior
The foreign keys are all disabled and there are orphaned rows left in the database which can cause problems for plugin migrations that fetch the raw row data.
Suggestions
- Re-enable in a
finallyblock, so an exception can't skip it. That doesn't help if the PHP process is killed (e.g. a timeout), though. - Have GC or
craft db/repairwarn whenpg_triggerhas disabled RI triggers, so affected sites can find out.
Craft CMS version
5.11.5.1
PHP version
8.4
Operating system and version
Ubuntu 22.04
Database type and version
PostgreSQL 15.19
Image driver and version
No response
Installed plugins and versions
Neo, Blitz, Formie, Hyper, Navigation, SEOmatic, Retour (the orphans span core and plugin tables)
- 主要言語
- PHP
- スター
- 3.6k
- フォーク
- 712
- 平均マージ
- 13時間 8分
- マージ済み PR（30日）
- 366
環境構築
- Dockerfile・Docker Compose ファイルなし
- プルリクエストのテンプレートなし
- コントリビューションガイドを読む
はじめの一歩
- issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
- 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
- リポジトリをフォークし、ブランチを切って変更します。
- issue 番号を参照したプルリクエストを送ります。
craftcms/cms のほかの issue
-
bug repo:cms
難易度 2/5 1〜3時間 初心者へのやさしさ 70/100
craftcms/cms#20205 · コメント 1 件 ·
メンテナーはふだん 1 日以内に返信
-
bug repo:cms
難易度 2/5 1〜3時間 初心者へのやさしさ 64/100
craftcms/cms#20203 · コメント 1 件 ·
メンテナーはふだん 1 日以内に返信
-
[5.x] MCP: `configuration.update` fails for Matrix fields with TypeError on `$propagationMethod`オープンbug repo:cms
難易度 4/5 1〜2日 初心者へのやさしさ 55/100
craftcms/cms#20406 · コメント 1 件 ·
メンテナーはふだん 1 日以内に返信
-
repo:cms
難易度 3/5 1〜2日 初心者へのやさしさ 73/100
craftcms/cms#20405 · コメント 1 件 ·
メンテナーはふだん 1 日以内に返信
-
[5.x]: MCP returns `[]` for CKEditor field values対応中かも @i-just が今日担当しました。 オープンrepo:cms
craftcms/cms#20404 · コメント 1 件 · 担当者 1 名 ·
メンテナーはふだん 1 日以内に返信
似ている issue
-
難易度 2/5 1〜3時間 初心者へのやさしさ 64/100
FinanceFlash/unvibecode#577 · コメント 1 件 ·
メンテナーはふだん 1 日以内に返信
-
Administration: required plugin configuration field stays invalid after restoring its saved valueオープンneeds-triage Platform(Default)
難易度 2/5 1〜3時間 初心者へのやさしさ 74/100
メンテナーはふだん 1 日以内に返信
-
good first issue priority:low type:docs
難易度 1/5 1〜3時間 初心者へのやさしさ 95/100
crazy-goat/rabbit-stream#969 ·
メンテナーはふだん 1 日以内に返信
-
難易度 2/5 1〜3時間 初心者へのやさしさ 68/100
opensourcepos/opensourcepos#4801 ·
メンテナーはふだん 1 日以内に返信
-
backend docs
難易度 2/5 1〜3時間 初心者へのやさしさ 67/100
Open-Locker/Open-Locker#368 ·
メンテナーはふだん 1 日以内に返信