With after triggers the crash does't reproduce because they don't create reentrancy in RI_Fkey_cascade_del. There are use cases in which a before delete trigger could want to delete other rows, for example if you have a function that accesses the row by id to free some external resource or mark something in other tables, you need the row to be alive, that means it can't be an after trigger, and nothing prevents you for using before triggers to do cleanup. Also, you don't normally delete rows recursively from a self referential table like the naive example in the test case, you normally want to do something like table inheritance, where 2 tables inherit from a parent table, and one references the other one, it is self-referential with respect to the parent.
If BEFORE DELETE triggers should not delete rows, i think postgres should explicitly throw a clean exception, something like "ERROR: Cannot delete rows in before delete trigger". Else, we should fix this bug. Ultimately, I think the postgres team should decide which of the 2 approaches to take. I can give time and work to fix the segfault. Thanks, El vie, 29 may 2026 a las 18:41, Álvaro Herrera (<[email protected]>) escribió: > Hi Luqui, > > On 2026-May-29, Lucas Jeffrey wrote: > > > We found a bug where executing a DELETE on a self-referential table that > > fires triggers can cause a segmentation fault. This is due to a > > *use-after-free* of a Postgres plan generated by the referential > integrity > > module (ri_triggers.c, RI_FKey_cascade_del). The crash occurs if the > > Postgres plancache is invalidated (ResetPlanCache) during the execution > of > > a reentrant RI trigger. > > I confirm that this causes a crash. I'm really surprised that this kind > of behavior has survived this long. > > However, while I'm not saying that we absolutely shouldn't fix this, I > think it's wrong usage to have BEFORE DELETE triggers delete other rows. > It's generally possible to have other BEFORE triggers after those ones, > that modify the row being deleted in a way that would make that other > trigger delete some _other_ row instead. As I recall, BEFORE triggers > are supposed to change row-local state only; any external state > (deletions or updates in other rows or other tables) should occur in > AFTER triggers, once the state from BEFORE triggers has quiesced. > > The docs have this text: > > Typically, row-level BEFORE triggers are used for checking or > modifying the data that will be inserted or updated. For example, a > BEFORE trigger might be used to insert the current time into a > timestamp column, or to check that two elements of the row are > consistent. Row-level AFTER triggers are most sensibly used to > propagate the updates to other tables, or make consistency checks > against other tables. The reason for this division of labor is that > an AFTER trigger can be certain it is seeing the final value of the > row, while a BEFORE trigger cannot; there might be other BEFORE > triggers firing after it. [...] > https://www.postgresql.org/docs/18/trigger-definition.html > > > Would changing you row-deleting BEFORE DELETE triggers to AFTER DELETE > triggers also fix your issue? > > Thanks, > > -- > Álvaro Herrera 48°01'N 7°57'E — > https://www.EnterpriseDB.com/ >
