[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
- 派生
- 714
- 平均合并
- 13 小时 9 分钟
- 30 天内合并 PR
- 378
环境准备
- 没有 Dockerfile 或 Docker Compose 文件
- 没有 Pull Request 模板
- 阅读贡献指南
从这里开始
- 先读完整个 Issue，再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库，在一个分支上完成修改。
- 提交 Pull Request，并在描述里引用这个 Issue 编号。
craftcms/cms 的其他 Issue
-
bug repo:cms
难度 2/5 1-3 小时 新手友好度 70/100
维护者通常 1 天内回复
-
bug repo:cms
难度 2/5 1-3 小时 新手友好度 64/100
维护者通常 1 天内回复
-
[5.x]: MCP returns `[]` for CKEditor field values可能已有人在做 @i-just 于 3 天前认领。 未关闭repo:cms
craftcms/cms#20404 · 2 条评论 · 已指派 1 人 ·
维护者通常 1 天内回复
-
[6.x]: Entry edit page takes ~0.4s longer to first paint for every Matrix block可能已有人在做 @riasvdv 于 3 天前认领。 未关闭bug repo:cms
craftcms/cms#20401 · 3 条评论 · 已指派 1 人 ·
维护者通常 1 天内回复
-
[6.x]: Custom entry source with a single-section condition shows another source's entries可能已有人在做 @brandonkelly 于 4 天前认领。 未关闭bug
craftcms/cms#20388 · 1 条评论 · 已指派 1 人 ·
维护者通常 1 天内回复
相似的 Issue
-
难度 2/5 1-3 小时 新手友好度 65/100
krayin/laravel-crm#2767 ·
维护者通常 1 天内回复
-
14.3.x
难度 2/5 1-3 小时 新手友好度 68/100
-
area/accessibility component/storefront domain/discovery Platform(Default)
难度 2/5 1-3 小时 新手友好度 68/100
维护者通常 1 天内回复
-
sync-en
难度 1/5 1-3 小时 新手友好度 78/100
维护者通常 1 天内回复
-
sync-en
难度 1/5 1-3 小时 新手友好度 72/100
维护者通常 1 天内回复