aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/commands/trigger.c53
-rw-r--r--src/backend/executor/spi.c37
-rw-r--r--src/backend/utils/adt/ri_triggers.c6
-rw-r--r--src/include/executor/spi.h4
-rw-r--r--src/test/regress/expected/foreign_key.out69
-rw-r--r--src/test/regress/sql/foreign_key.sql42
6 files changed, 169 insertions, 42 deletions
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 739777100c2..9510f849006 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.195.2.3 2007/07/17 17:45:48 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.195.2.4 2007/08/15 19:16:03 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2277,6 +2277,7 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
AfterTriggerEvent event,
prev_event;
MemoryContext per_tuple_context;
+ bool locally_opened = false;
Relation rel = NULL;
TriggerDesc *trigdesc = NULL;
FmgrInfo *finfo = NULL;
@@ -2309,6 +2310,19 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
*/
if (rel == NULL || rel->rd_id != event->ate_relid)
{
+ if (locally_opened)
+ {
+ /* close prior rel if any */
+ if (rel)
+ heap_close(rel, NoLock);
+ if (trigdesc)
+ FreeTriggerDesc(trigdesc);
+ if (finfo)
+ pfree(finfo);
+ Assert(instr == NULL); /* never used in this case */
+ }
+ locally_opened = true;
+
if (estate)
{
/* Find target relation among estate's result rels */
@@ -2320,28 +2334,22 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
while (nr > 0)
{
if (rInfo->ri_RelationDesc->rd_id == event->ate_relid)
+ {
+ rel = rInfo->ri_RelationDesc;
+ trigdesc = rInfo->ri_TrigDesc;
+ finfo = rInfo->ri_TrigFunctions;
+ instr = rInfo->ri_TrigInstrument;
+ locally_opened = false;
break;
+ }
rInfo++;
nr--;
}
- if (nr <= 0) /* should not happen */
- elog(ERROR, "could not find relation %u among query result relations",
- event->ate_relid);
- rel = rInfo->ri_RelationDesc;
- trigdesc = rInfo->ri_TrigDesc;
- finfo = rInfo->ri_TrigFunctions;
- instr = rInfo->ri_TrigInstrument;
}
- else
+
+ if (locally_opened)
{
- /* Hard way: we manage the resources for ourselves */
- if (rel)
- heap_close(rel, NoLock);
- if (trigdesc)
- FreeTriggerDesc(trigdesc);
- if (finfo)
- pfree(finfo);
- Assert(instr == NULL); /* never used in this case */
+ /* Hard way: open target relation for ourselves */
/*
* We assume that an appropriate lock is still held by the
@@ -2366,6 +2374,7 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
palloc0(trigdesc->numtriggers * sizeof(FmgrInfo));
/* Never any EXPLAIN info in this case */
+ instr = NULL;
}
}
@@ -2416,7 +2425,7 @@ afterTriggerInvokeEvents(AfterTriggerEventList *events,
events->tail = prev_event;
/* Release working resources */
- if (!estate)
+ if (locally_opened)
{
if (rel)
heap_close(rel, NoLock);
@@ -2541,11 +2550,13 @@ AfterTriggerEndQuery(EState *estate)
* IMMEDIATE: all events we have decided to defer will be available for it
* to fire.
*
+ * We loop in case a trigger queues more events.
+ *
* If we find no firable events, we don't have to increment
* firing_counter.
*/
events = &afterTriggers->query_stack[afterTriggers->query_depth];
- if (afterTriggerMarkEvents(events, &afterTriggers->events, true))
+ while (afterTriggerMarkEvents(events, &afterTriggers->events, true))
{
CommandId firing_id = afterTriggers->firing_counter++;
@@ -2589,7 +2600,7 @@ AfterTriggerFireDeferred(void)
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
/*
- * Run all the remaining triggers. Loop until they are all gone, just in
+ * Run all the remaining triggers. Loop until they are all gone, in
* case some trigger queues more for us to do.
*/
while (afterTriggerMarkEvents(events, NULL, false))
@@ -3036,7 +3047,7 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
{
AfterTriggerEventList *events = &afterTriggers->events;
- if (afterTriggerMarkEvents(events, NULL, true))
+ while (afterTriggerMarkEvents(events, NULL, true))
{
CommandId firing_id = afterTriggers->firing_counter++;
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 61dd6a6d0a7..5999418ccc1 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.144.2.2 2007/03/17 03:15:55 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.144.2.3 2007/08/15 19:16:04 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -40,9 +40,9 @@ static void _SPI_prepare_plan(const char *src, _SPI_plan *plan);
static int _SPI_execute_plan(_SPI_plan *plan,
Datum *Values, const char *Nulls,
Snapshot snapshot, Snapshot crosscheck_snapshot,
- bool read_only, long tcount);
+ bool read_only, bool fire_triggers, long tcount);
-static int _SPI_pquery(QueryDesc *queryDesc, long tcount);
+static int _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, long tcount);
static void _SPI_error_callback(void *arg);
@@ -303,7 +303,7 @@ SPI_execute(const char *src, bool read_only, long tcount)
res = _SPI_execute_plan(&plan, NULL, NULL,
InvalidSnapshot, InvalidSnapshot,
- read_only, tcount);
+ read_only, true, tcount);
_SPI_end_call(true);
return res;
@@ -336,7 +336,7 @@ SPI_execute_plan(void *plan, Datum *Values, const char *Nulls,
res = _SPI_execute_plan((_SPI_plan *) plan,
Values, Nulls,
InvalidSnapshot, InvalidSnapshot,
- read_only, tcount);
+ read_only, true, tcount);
_SPI_end_call(true);
return res;
@@ -351,9 +351,12 @@ SPI_execp(void *plan, Datum *Values, const char *Nulls, long tcount)
/*
* SPI_execute_snapshot -- identical to SPI_execute_plan, except that we allow
- * the caller to specify exactly which snapshots to use. This is currently
- * not documented in spi.sgml because it is only intended for use by RI
- * triggers.
+ * the caller to specify exactly which snapshots to use. Also, the caller
+ * may specify that AFTER triggers should be queued as part of the outer
+ * query rather than being fired immediately at the end of the command.
+ *
+ * This is currently not documented in spi.sgml because it is only intended
+ * for use by RI triggers.
*
* Passing snapshot == InvalidSnapshot will select the normal behavior of
* fetching a new snapshot for each query.
@@ -362,7 +365,7 @@ int
SPI_execute_snapshot(void *plan,
Datum *Values, const char *Nulls,
Snapshot snapshot, Snapshot crosscheck_snapshot,
- bool read_only, long tcount)
+ bool read_only, bool fire_triggers, long tcount)
{
int res;
@@ -379,7 +382,7 @@ SPI_execute_snapshot(void *plan,
res = _SPI_execute_plan((_SPI_plan *) plan,
Values, Nulls,
snapshot, crosscheck_snapshot,
- read_only, tcount);
+ read_only, fire_triggers, tcount);
_SPI_end_call(true);
return res;
@@ -1316,12 +1319,14 @@ _SPI_prepare_plan(const char *src, _SPI_plan *plan)
* behavior of taking a new snapshot for each query.
* crosscheck_snapshot: for RI use, all others pass InvalidSnapshot
* read_only: TRUE for read-only execution (no CommandCounterIncrement)
+ * fire_triggers: TRUE to fire AFTER triggers at end of query (normal case);
+ * FALSE means any AFTER triggers are postponed to end of outer query
* tcount: execution tuple-count limit, or 0 for none
*/
static int
_SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
Snapshot snapshot, Snapshot crosscheck_snapshot,
- bool read_only, long tcount)
+ bool read_only, bool fire_triggers, long tcount)
{
volatile int res = 0;
volatile uint32 my_processed = 0;
@@ -1467,7 +1472,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
crosscheck_snapshot,
dest,
paramLI, false);
- res = _SPI_pquery(qdesc,
+ res = _SPI_pquery(qdesc, fire_triggers,
queryTree->canSetTag ? tcount : 0);
FreeQueryDesc(qdesc);
}
@@ -1523,7 +1528,7 @@ fail:
}
static int
-_SPI_pquery(QueryDesc *queryDesc, long tcount)
+_SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, long tcount)
{
int operation = queryDesc->operation;
int res;
@@ -1561,7 +1566,8 @@ _SPI_pquery(QueryDesc *queryDesc, long tcount)
ResetUsage();
#endif
- AfterTriggerBeginQuery();
+ if (fire_triggers)
+ AfterTriggerBeginQuery();
ExecutorStart(queryDesc, false);
@@ -1577,7 +1583,8 @@ _SPI_pquery(QueryDesc *queryDesc, long tcount)
}
/* Take care of any queued AFTER triggers */
- AfterTriggerEndQuery(queryDesc->estate);
+ if (fire_triggers)
+ AfterTriggerEndQuery(queryDesc->estate);
ExecutorEnd(queryDesc);
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 0b925f36478..e7e2ff36675 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -17,7 +17,7 @@
*
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
*
- * $PostgreSQL: pgsql/src/backend/utils/adt/ri_triggers.c,v 1.82.2.1 2005/11/22 18:23:21 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/ri_triggers.c,v 1.82.2.2 2007/08/15 19:16:04 tgl Exp $
*
* ----------
*/
@@ -2712,7 +2712,7 @@ RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel)
NULL, NULL,
CopySnapshot(GetLatestSnapshot()),
InvalidSnapshot,
- true, 1);
+ true, false, 1);
/* Check result */
if (spi_result != SPI_OK_SELECT)
@@ -3141,7 +3141,7 @@ ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
spi_result = SPI_execute_snapshot(qplan,
vals, nulls,
test_snapshot, crosscheck_snapshot,
- false, limit);
+ false, false, limit);
/* Restore UID */
SetUserId(save_uid);
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index 9f5e9316401..39b4f88db3a 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -2,7 +2,7 @@
*
* spi.h
*
- * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.53 2005/10/15 02:49:44 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.53.2.1 2007/08/15 19:16:04 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -92,7 +92,7 @@ extern int SPI_execute_snapshot(void *plan,
Datum *Values, const char *Nulls,
Snapshot snapshot,
Snapshot crosscheck_snapshot,
- bool read_only, long tcount);
+ bool read_only, bool fire_triggers, long tcount);
extern void *SPI_prepare(const char *src, int nargs, Oid *argtypes);
extern void *SPI_saveplan(void *plan);
extern int SPI_freeplan(void *plan);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 5ad23acaf93..1e854397c1a 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -646,7 +646,6 @@ SELECT * from FKTABLE;
UPDATE PKTABLE set ptest2=5 where ptest2=2;
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
DETAIL: Key (ftest1,ftest2,ftest3)=(1,-1,3) is not present in table "pktable".
-CONTEXT: SQL statement "UPDATE ONLY "public"."fktable" SET "ftest2" = DEFAULT WHERE "ftest1" = $1 AND "ftest2" = $2 AND "ftest3" = $3"
-- Try to update something that will set default
UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
UPDATE PKTABLE set ptest2=10 where ptest2=4;
@@ -1209,3 +1208,71 @@ ROLLBACK TO savept1;
COMMIT;
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
DETAIL: Key (fk)=(20) is not present in table "pktable".
+-- test order of firing of FK triggers when several RI-induced changes need to
+-- be made to the same row. This was broken by subtransaction-related
+-- changes in 8.0.
+CREATE TEMP TABLE users (
+ id INT PRIMARY KEY,
+ name VARCHAR NOT NULL
+);
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
+INSERT INTO users VALUES (1, 'Jozko');
+INSERT INTO users VALUES (2, 'Ferko');
+INSERT INTO users VALUES (3, 'Samko');
+CREATE TEMP TABLE tasks (
+ id INT PRIMARY KEY,
+ owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
+ worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
+ checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
+);
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tasks_pkey" for table "tasks"
+INSERT INTO tasks VALUES (1,1,NULL,NULL);
+INSERT INTO tasks VALUES (2,2,2,NULL);
+INSERT INTO tasks VALUES (3,3,3,3);
+SELECT * FROM tasks;
+ id | owner | worker | checked_by
+----+-------+--------+------------
+ 1 | 1 | |
+ 2 | 2 | 2 |
+ 3 | 3 | 3 | 3
+(3 rows)
+
+UPDATE users SET id = 4 WHERE id = 3;
+SELECT * FROM tasks;
+ id | owner | worker | checked_by
+----+-------+--------+------------
+ 1 | 1 | |
+ 2 | 2 | 2 |
+ 3 | 4 | 4 | 4
+(3 rows)
+
+DELETE FROM users WHERE id = 4;
+SELECT * FROM tasks;
+ id | owner | worker | checked_by
+----+-------+--------+------------
+ 1 | 1 | |
+ 2 | 2 | 2 |
+ 3 | | |
+(3 rows)
+
+-- could fail with only 2 changes to make, if row was already updated
+BEGIN;
+UPDATE tasks set id=id WHERE id=2;
+SELECT * FROM tasks;
+ id | owner | worker | checked_by
+----+-------+--------+------------
+ 1 | 1 | |
+ 3 | | |
+ 2 | 2 | 2 |
+(3 rows)
+
+DELETE FROM users WHERE id = 2;
+SELECT * FROM tasks;
+ id | owner | worker | checked_by
+----+-------+--------+------------
+ 1 | 1 | |
+ 3 | | |
+ 2 | | |
+(3 rows)
+
+COMMIT;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index cc3f9abfaaf..8b84413a67a 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -857,3 +857,45 @@ ROLLBACK TO savept1;
-- should catch error from initial INSERT
COMMIT;
+
+-- test order of firing of FK triggers when several RI-induced changes need to
+-- be made to the same row. This was broken by subtransaction-related
+-- changes in 8.0.
+
+CREATE TEMP TABLE users (
+ id INT PRIMARY KEY,
+ name VARCHAR NOT NULL
+);
+
+INSERT INTO users VALUES (1, 'Jozko');
+INSERT INTO users VALUES (2, 'Ferko');
+INSERT INTO users VALUES (3, 'Samko');
+
+CREATE TEMP TABLE tasks (
+ id INT PRIMARY KEY,
+ owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
+ worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
+ checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+INSERT INTO tasks VALUES (1,1,NULL,NULL);
+INSERT INTO tasks VALUES (2,2,2,NULL);
+INSERT INTO tasks VALUES (3,3,3,3);
+
+SELECT * FROM tasks;
+
+UPDATE users SET id = 4 WHERE id = 3;
+
+SELECT * FROM tasks;
+
+DELETE FROM users WHERE id = 4;
+
+SELECT * FROM tasks;
+
+-- could fail with only 2 changes to make, if row was already updated
+BEGIN;
+UPDATE tasks set id=id WHERE id=2;
+SELECT * FROM tasks;
+DELETE FROM users WHERE id = 2;
+SELECT * FROM tasks;
+COMMIT;
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-02 20:31:28 +0000