[5.x]: GC on PostgreSQL can leave all FK triggers disabled and deletes stop cascading

Đang mở
  • Có gì không ổn?
#20,386 1 bình luận 0 reaction 1 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

@brandonkelly đang làm issue này rồi.

Từ ngày 5/11/2026.

  • #20395 của @brandonkelly — đang mở

Đánh giá

Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức phù hợp với người mới
48/100
Loại issue
Lỗi
Độ rõ ràng
Khá rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
php, postgresql
Lĩnh vực
backend, database

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

bug repo:cms
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:

  1. 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.
  2. 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_dump backup 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
  1. Use Craft 5 on PostgreSQL, with a DB user that owns the tables so DISABLE TRIGGER ALL succeeds.
  2. Make GC fail after the disable step. For example, run craft gc and kill the process during "deleting orphaned foreign key rows", or throw from inside the FK loop.
  3. Run SELECT count(*) FROM pg_trigger WHERE tgenabled = 'D';. It's non-zero (every FK trigger).
  4. Delete an entry with nested entries or Neo blocks: its elements_sites, entries, elements_owners etc. 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 finally block, 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/repair warn when pg_trigger has 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)

Ngôn ngữ chính
PHP
Star
3.6k
Fork
712
Merge trung bình
13 giờ 8 phút
Pull request đã merge (30 ngày)
366

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của craftcms/cms

Tất cả issue của craftcms/cms

Issue tương tự

Thêm issue về PHP

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.