Re: Delete cascade with three levels bug ?

Marcelo Costa <marcelojscosta(at)gmail(dot)com> writes:
> [ trying to defer RI_FKey_cascade_del trigger crashes the backend ]

I looked at this a bit more and think that there's more to it than pilot
error. The crash occurs because we queue a deferred trigger here:

#0 AfterTriggerSaveEvent (relinfo=0x40181778, event=1, row_trigger=1 '\001', oldtup=0x4018c1b8, newtup=0x0, recheckIndexes=0x0, modifiedCols=0x0) at trigger.c:3890
#1 0x2db9c0 in ExecARDeleteTriggers (estate=0x40181778, relinfo=0x40181778, tupleid=0x4018c1b8) at trigger.c:1896
#2 0x317124 in ExecDelete (tupleid=0x7b03c388, planSlot=0x4018b2c0, epqstate=0x4018b664, estate=0x401816a8) at nodeModifyTable.c:368
#3 0x317894 in ExecModifyTable (node=0x4018b618) at nodeModifyTable.c:738
#4 0x2fe01c in ExecProcNode (node=0x4018b618) at execProcnode.c:359
#5 0x2fc034 in ExecutePlan (estate=0x401816a8, planstate=0x4018b618, operation=CMD_DELETE, sendTuples=0 '\000', numberTuples=0, direction=ForwardScanDirection, dest=0x40003a80) at execMain.c:1188
#6 0x2fadf0 in standard_ExecutorRun (queryDesc=0x4017e2a0, direction=ForwardScanDirection, count=0) at execMain.c:278
#7 0x2fac94 in ExecutorRun (queryDesc=0x40181778, direction=ForwardScanDirection, count=1) at execMain.c:227
#8 0x3268c8 in _SPI_pquery (queryDesc=0x4017e2a0, fire_triggers=0 '\000', tcount=0) at spi.c:2009
#9 0x326520 in _SPI_execute_plan (plan=0x40180288, paramLI=0x4017e280, snapshot=0x0, crosscheck_snapshot=0x0, read_only=0, fire_triggers=0 '\000', tcount=0) at spi.c:1831
#10 0x32383c in SPI_execute_snapshot (plan=0x40180288, Values=0x7b03be88, Nulls=0x7b03bf88 " ", snapshot=0x0, crosscheck_snapshot=0x0, read_only=0, fire_triggers=0, tcount=0) at spi.c:441
#11 0x493d08 in ri_PerformCheck (qkey=0x7b03bb20, qplan=0x40180288, fk_rel=0x40133600, pk_rel=0x401262f0, old_tuple=0x7b03b5a8, new_tuple=0x0, detectNewRows=1, expect_OK=8, constrname=0x7b03b8cc "fk_son_father") at ri_triggers.c:3350
#12 0x4901d4 in RI_FKey_cascade_del (fcinfo=0x40181778) at ri_triggers.c:1166
#13 0x2db140 in ExecCallTriggerFunc (trigdata=0x7b03b588, tgindx=0, finfo=0x7b03b850, instr=0x0, per_tuple_context=0x40153b60) at trigger.c:1631
#14 0x2dccc8 in AfterTriggerExecute (event=0x40170678, rel=0x401262f0, trigdesc=0x1, finfo=0x4016a0d0, instr=0x0, per_tuple_context=0x40153b60) at trigger.c:2830
#15 0x2dd038 in afterTriggerInvokeEvents (events=0x40170a58, firing_id=1, estate=0x40169e40, delete_ok=1 '\001') at trigger.c:3009
#16 0x2dd5ac in AfterTriggerFireDeferred () at trigger.c:3247
#17 0x1fd230 in CommitTransaction () at xact.c:1581
#18 0x1fdfe8 in CommitTransactionCommand () at xact.c:2323
#19 0x3ed0e8 in finish_xact_command () at postgres.c:2372
#20 0x3eae20 in exec_simple_query (query_string=0x400625b8 "DELETE FROM father WHERE co_father = 1;") at postgres.c:1039
#21 0x3ef344 in PostgresMain (argc=2, argv=0x51, username=0x4005f9d8 "postgres") at postgres.c:3573

when we are not inside any AfterTriggerBeginQuery/AfterTriggerEndQuery
pair. Normally _SPI_pquery() would take care of that detail, but it's
been specifically told not to by the RI trigger code (notice the
fire_triggers=0 arguments). It is not immediately obvious that an
RI trigger query could never cause AFTER triggers to be queued, so
I think this is at least a latent bug, even if this particular symptom
involves intentional misconfiguration of standard triggers.

The two reasonable fixes seem to be to change our minds about not
letting SPI fire deferred triggers in this usage, or to add code to
AfterTriggerFireDeferred to invoke AfterTriggerBegin/EndQuery.
I do not recall what the reasoning was for deferring triggers during
ri_triggers calls, and am afraid to monkey with that behavior unless
someone can lay out an argument why it's okay to change. So modifying
AfterTriggerFireDeferred seems like the right solution.

regards, tom lane