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

Open
  • Is something wrong?
#20,386 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
php, postgresql
Domain
backend, database

Research direction

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.

Written by the indexing model from the issue text.

Description

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)

Dominant language
PHP
Stars
3.6k
Forks
712
Avg merge
12h 44m
Merged PRs (30d)
381

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from craftcms/cms

All issues in craftcms/cms

Similar issues

More PHP issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.