So, why shouldn't SET CONSTRAINTS set a transaction snapshot? - Mailing list pgsql-hackers

In connection with fixing
http://archives.postgresql.org/pgsql-hackers/2008-12/msg00620.php
I decided to insert "Assert(ActiveSnapshotSet())" into pg_plan_query,
since any path reaching that function should already have provided
a snapshot.  I was bemused to find that this resulted in an assert
failure in the regression tests, with backtrace

#4  0x480b74 in ExceptionalCondition (   conditionName=0x169630 "!(ActiveSnapshotSet())",    errorType=0x169648
"FailedAssertion",fileName=0x169564 "postgres.c",    lineNumber=689) at assert.c:57
 
#5  0x3a5a4c in pg_plan_query (querytree=0x4017d288, cursorOptions=1565885,    boundParams=0x7b033ec8) at
postgres.c:689
#6  0x3a5b98 in pg_plan_queries (querytrees=0x7b011c28, cursorOptions=32,    boundParams=0x7f, needSnapshot=-24 '�') at
postgres.c:772
#7  0x2eac74 in _SPI_prepare_plan (src=0x1 <Address 0x1 out of bounds>,    plan=0x17e4bd, boundParams=0x7b03cf5a) at
spi.c:1609
#8  0x2e8654 in SPI_prepare_cursor (   src=0x40172468 "SELECT 1 FROM ONLY \"public\".\"pktable\" x WHERE \"id\"
OPERATOR(pg_catalog.=)$1 FOR SHARE OF x", nargs=1, argtypes=0x7b03c300,    cursorOptions=0) at spi.c:499
 
#9  0x2e8590 in SPI_prepare (src=0x7b011c28 "", nargs=2063845116,    argtypes=0x7b011cf0) at spi.c:473
#10 0x44a024 in ri_PlanCheck (   querystr=0x40172468 "SELECT 1 FROM ONLY \"public\".\"pktable\" x WHERE \"id\"
OPERATOR(pg_catalog.=)$1 FOR SHARE OF x", nargs=1, argtypes=0x7b03c300,    qkey=0x7b03be40, fk_rel=0x4014ba78,
pk_rel=0x40142b38,cache_plan=1)   at ri_triggers.c:3225
 
#11 0x4456fc in RI_FKey_check (fcinfo=0x7b011c28) at ri_triggers.c:486
#12 0x4457e4 in RI_FKey_check_ins (fcinfo=0x7b011c28) at ri_triggers.c:518
#13 0x2a3f70 in ExecCallTriggerFunc (trigdata=0x7b03b868, tgindx=0,    finfo=0x7b03bb30, instr=0x0,
per_tuple_context=0x401eaf20)  at trigger.c:1591
 
#14 0x2a5a74 in AfterTriggerExecute (event=0x40174890, rel=0x4014ba78,    trigdesc=0x7b011cf0, finfo=0x40127948,
instr=0x0,   per_tuple_context=0x401eaf20) at trigger.c:2777
 
#15 0x2a5de0 in afterTriggerInvokeEvents (events=0x40174c70, firing_id=1,    estate=0x401276b8, delete_ok=1 '\001') at
trigger.c:2956
#16 0x2a6f4c in AfterTriggerSetState (stmt=0x400cd9f8) at trigger.c:3729
#17 0x3aef54 in ProcessUtility (parsetree=0x400cd9f8,    queryString=0x400cd1a8 "SET CONSTRAINTS ALL IMMEDIATE;",
params=0x0,   isTopLevel=1 '\001', dest=0x400cdbe0, completionTag=0x7b03b348 "")   at utility.c:1036
 
#18 0x3ac9f8 in PortalRunUtility (portal=0x4011c438, utilityStmt=0x400cd9f8,    isTopLevel=1 '\001', dest=0x400cdbe0,
completionTag=0x7b03b348"")   at pquery.c:1183
 
#19 0x3acba8 in PortalRunMulti (portal=0x4011c438, isTopLevel=1 '\001',    dest=0x400cdbe0, altdest=0x400cdbe0,
completionTag=0x7b03b348"")   at pquery.c:1288
 
#20 0x3ac224 in PortalRun (portal=0x4011c438, count=2147483647,    isTopLevel=1 '\001', dest=0x400cdbe0,
altdest=0x400cdbe0,   completionTag=0x7b03b348 "") at pquery.c:815
 
#21 0x3a5fe4 in exec_simple_query (   query_string=0x400cd1a8 "SET CONSTRAINTS ALL IMMEDIATE;")   at postgres.c:1011

The problem here is that PortalRunUtility() intentionally does not set
a snapshot for ConstraintsSetStmt; so if SET CONSTRAINTS IMMEDIATE
results in any triggers getting fired, those triggers are run with
no snapshot available.  Boo hiss.

The comment in PortalRunUtility asserts loudly that ConstraintsSetStmt
MUST NOT have a transaction snapshot set before it is executed, but
I confess that I don't see why not at the moment.  We certainly can't
have it not set a snap if it has any triggers to fire.  Comments?
        regards, tom lane