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

Cerrado
  • ¿Algo no está bien?
#20,386 1 comentario 0 reacciones 1 asignado Ver en GitHub

Los mantenedores suelen responder en 1 día

@brandonkelly ya está trabajando en esto.

Desde el 5/11/2026.

  • #20395 de @brandonkelly — fusionado

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
48/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
php, postgresql
Área
backend, database

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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)

Lenguaje dominante
PHP
Estrellas
3.6k
Forks
712
Merge medio
13 h 13 min
PR fusionados (30 d)
372

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de craftcms/cms

Todos los issues de craftcms/cms

Issues similares

Más issues de PHP

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.