Skip to content

Commit b339d1f

Browse files
tglsfdctglsfdc
committed
Fire non-deferred AFTER triggers immediately upon query completion,
rather than when returning to the idle loop. This makes no particular difference for interactively-issued queries, but it makes a big difference for queries issued within functions: trigger execution now occurs before the calling function is allowed to proceed. This responds to numerous complaints about nonintuitive behavior of foreign key checking, such as http://archives.postgresql.org/pgsql-bugs/2004-09/msg00020.php, and appears to be required by the SQL99 spec. Also take the opportunity to simplify the data structures used for the pending-trigger list, rename them for more clarity, and squeeze out a bit of space.
1 parent 856d1fa commit b339d1f

File tree

17 files changed

+954
-618
lines changed

17 files changed

+954
-618
lines changed

‎doc/src/sgml/ref/set_constraints.sgml

Lines changed: 15 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.11 2004/09/08 20:47:37 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.12 2004/09/10 18:39:53 tgl Exp $ -->
22
<refentry id="SQL-SET-CONSTRAINTS">
33
<refmeta>
44
<refentrytitle id="SQL-SET-CONSTRAINTS-title">SET CONSTRAINTS</refentrytitle>
@@ -34,13 +34,13 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
3434

3535
<para>
3636
Upon creation, a constraint is given one of three
37-
characteristics: <literal>INITIALLY DEFERRED</literal>,
38-
<literal>INITIALLY IMMEDIATE DEFERRABLE</literal>, or
39-
<literal>INITIALLY IMMEDIATE NOT DEFERRABLE</literal>. The third
40-
class is not affected by the <command>SET CONSTRAINTS</command>
41-
command. The first two classes start every transaction in the
42-
indicated mode, but their behavior can be changed within a transaction
43-
by <command>SET CONSTRAINTS</command>.
37+
characteristics: <literal>DEFERRABLE INITIALLY DEFERRED</literal>,
38+
<literal>DEFERRABLE INITIALLY IMMEDIATE</literal>, or
39+
<literal>NOT DEFERRABLE</literal>. The third
40+
class is always <literal>IMMEDIATE</literal> and is not affected by the
41+
<command>SET CONSTRAINTS</command> command. The first two classes start
42+
every transaction in the indicated mode, but their behavior can be changed
43+
within a transaction by <command>SET CONSTRAINTS</command>.
4444
</para>
4545

4646
<para>
@@ -52,19 +52,22 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
5252
</para>
5353

5454
<para>
55-
When you change the mode of a constraint from <literal>DEFERRED</literal>
55+
When <command>SET CONSTRAINTS</command> changes the mode of a constraint
56+
from <literal>DEFERRED</literal>
5657
to <literal>IMMEDIATE</literal>, the new mode takes effect
5758
retroactively: any outstanding data modifications that would have
5859
been checked at the end of the transaction are instead checked during the
5960
execution of the <command>SET CONSTRAINTS</command> command.
6061
If any such constraint is violated, the <command>SET CONSTRAINTS</command>
61-
fails (and does not change the constraint mode).
62+
fails (and does not change the constraint mode). Thus, <command>SET
63+
CONSTRAINTS</command> can be used to force checking of constraints to
64+
occur at a specific point in a transaction.
6265
</para>
6366

6467
<para>
6568
Currently, only foreign key constraints are affected by this
6669
setting. Check and unique constraints are always effectively
67-
initially immediate not deferrable.
70+
not deferrable.
6871
</para>
6972
</refsect1>
7073

@@ -76,11 +79,7 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
7679
current transaction. Thus, if you execute this command outside of a
7780
transaction block
7881
(<command>BEGIN</command>/<command>COMMIT</command> pair), it will
79-
not appear to have any effect. If you wish to change the behavior
80-
of a constraint without needing to issue a <command>SET
81-
CONSTRAINTS</command> command in every transaction, specify
82-
<literal>INITIALLY DEFERRED</literal> or <literal>INITIALLY
83-
IMMEDIATE</literal> when you create the constraint.
82+
not appear to have any effect.
8483
</para>
8584
</refsect1>
8685

‎doc/src/sgml/release.sgml

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.294 2004/08/30 00:47:31 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.295 2004/09/10 18:39:54 tgl Exp $
33
-->
44

55
<appendix id="release">
@@ -336,6 +336,16 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.294 2004/08/30 00:47:31 tgl Exp
336336
whitespace (which has always been ignored).
337337
</para>
338338
</listitem>
339+
340+
<listitem>
341+
<para>
342+
Non-deferred AFTER triggers are now fired immediately after completion
343+
of the triggering query, rather than upon finishing the current
344+
interactive command. This makes a difference when the triggering query
345+
occurred within a function: the trigger is invoked before the function
346+
proceeds to its next operation.
347+
</para>
348+
</listitem>
339349
</itemizedlist>
340350
</para>
341351
</sect2>
@@ -1424,6 +1434,18 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.294 2004/08/30 00:47:31 tgl Exp
14241434
<title>Server-Side Language Changes</title>
14251435
<itemizedlist>
14261436

1437+
<listitem>
1438+
<para>
1439+
Non-deferred AFTER triggers are now fired immediately after completion
1440+
of the triggering query, rather than upon finishing the current
1441+
interactive command. This makes a difference when the triggering query
1442+
occurred within a function: the trigger is invoked before the function
1443+
proceeds to its next operation. For example, if a function inserts
1444+
a new row into a table, any non-deferred foreign key checks occur
1445+
before proceeding with the function.
1446+
</para>
1447+
</listitem>
1448+
14271449
<listitem>
14281450
<para>
14291451
Allow function parameters to be declared with names (Dennis Bjorklund)
@@ -1483,7 +1505,7 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.294 2004/08/30 00:47:31 tgl Exp
14831505

14841506
<listitem>
14851507
<para>
1486-
New plperl server-side language (Command Prompt, Andrew Dunstan)
1508+
Major overhaul of plperl server-side language (Command Prompt, Andrew Dunstan)
14871509
</para>
14881510
</listitem>
14891511

‎src/backend/access/transam/xact.c

Lines changed: 39 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.186 2004/09/06 17:56:04 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.187 2004/09/10 18:39:55 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -138,7 +138,6 @@ static void CleanupSubTransaction(void);
138138
static void StartAbortedSubTransaction(void);
139139
static void PushTransaction(void);
140140
static void PopTransaction(void);
141-
static void CommitTransactionToLevel(int level);
142141
static char *CleanupAbortedSubTransactions(bool returnName);
143142

144143
static void AtSubAbort_Memory(void);
@@ -1219,7 +1218,7 @@ StartTransaction(void)
12191218
*/
12201219
AtStart_Inval();
12211220
AtStart_Cache();
1222-
DeferredTriggerBeginXact();
1221+
AfterTriggerBeginXact();
12231222

12241223
/*
12251224
* done with start processing, set current transaction state to "in
@@ -1253,7 +1252,7 @@ CommitTransaction(void)
12531252
* committed. He'll invoke all trigger deferred until XACT before we
12541253
* really start on committing the transaction.
12551254
*/
1256-
DeferredTriggerEndXact();
1255+
AfterTriggerEndXact();
12571256

12581257
/*
12591258
* Similarly, let ON COMMIT management do its thing before we start to
@@ -1454,7 +1453,7 @@ AbortTransaction(void)
14541453
/*
14551454
* do abort processing
14561455
*/
1457-
DeferredTriggerAbortXact();
1456+
AfterTriggerAbortXact();
14581457
AtAbort_Portals();
14591458
AtEOXact_LargeObject(false); /* 'false' means it's abort */
14601459
AtAbort_Notify();
@@ -1672,12 +1671,6 @@ CommitTransactionCommand(void)
16721671
* default state.
16731672
*/
16741673
case TBLOCK_END:
1675-
/* commit all open subtransactions */
1676-
if (s->nestingLevel > 1)
1677-
CommitTransactionToLevel(2);
1678-
s = CurrentTransactionState;
1679-
Assert(s->parent == NULL);
1680-
/* and now the outer transaction */
16811674
CommitTransaction();
16821675
s->blockState = TBLOCK_DEFAULT;
16831676
break;
@@ -1732,11 +1725,10 @@ CommitTransactionCommand(void)
17321725
break;
17331726

17341727
/*
1735-
* We were issued a RELEASE command, so we end the current
1736-
* subtransaction and return to the parent transaction.
1737-
*
1738-
* Since RELEASE can exit multiple levels of subtransaction, we
1739-
* must loop here until we get out of all SUBEND'ed levels.
1728+
* We were issued a COMMIT or RELEASE command, so we end the
1729+
* current subtransaction and return to the parent transaction.
1730+
* Lather, rinse, and repeat until we get out of all SUBEND'ed
1731+
* subtransaction levels.
17401732
*/
17411733
case TBLOCK_SUBEND:
17421734
do
@@ -1745,6 +1737,13 @@ CommitTransactionCommand(void)
17451737
PopTransaction();
17461738
s = CurrentTransactionState; /* changed by pop */
17471739
} while (s->blockState == TBLOCK_SUBEND);
1740+
/* If we had a COMMIT command, finish off the main xact too */
1741+
if (s->blockState == TBLOCK_END)
1742+
{
1743+
Assert(s->parent == NULL);
1744+
CommitTransaction();
1745+
s->blockState = TBLOCK_DEFAULT;
1746+
}
17481747
break;
17491748

17501749
/*
@@ -2238,7 +2237,6 @@ EndTransactionBlock(void)
22382237
* the default state.
22392238
*/
22402239
case TBLOCK_INPROGRESS:
2241-
case TBLOCK_SUBINPROGRESS:
22422240
s->blockState = TBLOCK_END;
22432241
result = true;
22442242
break;
@@ -2254,6 +2252,22 @@ EndTransactionBlock(void)
22542252
s->blockState = TBLOCK_ENDABORT;
22552253
break;
22562254

2255+
/*
2256+
* We are in a live subtransaction block. Set up to subcommit
2257+
* all open subtransactions and then commit the main transaction.
2258+
*/
2259+
case TBLOCK_SUBINPROGRESS:
2260+
while (s->parent != NULL)
2261+
{
2262+
Assert(s->blockState == TBLOCK_SUBINPROGRESS);
2263+
s->blockState = TBLOCK_SUBEND;
2264+
s = s->parent;
2265+
}
2266+
Assert(s->blockState == TBLOCK_INPROGRESS);
2267+
s->blockState = TBLOCK_END;
2268+
result = true;
2269+
break;
2270+
22572271
/*
22582272
* Here we are inside an aborted subtransaction. Go to the
22592273
* "abort the whole tree" state so that
@@ -2699,8 +2713,12 @@ ReleaseCurrentSubTransaction(void)
26992713
if (s->blockState != TBLOCK_SUBINPROGRESS)
27002714
elog(ERROR, "ReleaseCurrentSubTransaction: unexpected state %s",
27012715
BlockStateAsString(s->blockState));
2716+
Assert(s->state == TRANS_INPROGRESS);
27022717
MemoryContextSwitchTo(CurTransactionContext);
2703-
CommitTransactionToLevel(GetCurrentTransactionNestLevel());
2718+
CommitSubTransaction();
2719+
PopTransaction();
2720+
s = CurrentTransactionState; /* changed by pop */
2721+
Assert(s->state == TRANS_INPROGRESS);
27042722
}
27052723

27062724
/*
@@ -2827,28 +2845,6 @@ AbortOutOfAnyTransaction(void)
28272845
Assert(s->parent == NULL);
28282846
}
28292847

2830-
/*
2831-
* CommitTransactionToLevel
2832-
*
2833-
* Commit everything from the current transaction level
2834-
* up to the specified level (inclusive).
2835-
*/
2836-
static void
2837-
CommitTransactionToLevel(int level)
2838-
{
2839-
TransactionState s = CurrentTransactionState;
2840-
2841-
Assert(s->state == TRANS_INPROGRESS);
2842-
2843-
while (s->nestingLevel >= level)
2844-
{
2845-
CommitSubTransaction();
2846-
PopTransaction();
2847-
s = CurrentTransactionState; /* changed by pop */
2848-
Assert(s->state == TRANS_INPROGRESS);
2849-
}
2850-
}
2851-
28522848
/*
28532849
* IsTransactionBlock --- are we within a transaction block?
28542850
*/
@@ -2975,7 +2971,7 @@ StartSubTransaction(void)
29752971
*/
29762972
AtSubStart_Inval();
29772973
AtSubStart_Notify();
2978-
DeferredTriggerBeginSubXact();
2974+
AfterTriggerBeginSubXact();
29792975

29802976
s->state = TRANS_INPROGRESS;
29812977

@@ -3011,7 +3007,7 @@ CommitSubTransaction(void)
30113007
AtSubCommit_childXids();
30123008

30133009
/* Post-commit cleanup */
3014-
DeferredTriggerEndSubXact(true);
3010+
AfterTriggerEndSubXact(true);
30153011
AtSubCommit_Portals(s->parent->transactionIdData,
30163012
s->parent->curTransactionOwner);
30173013
AtEOSubXact_LargeObject(true, s->transactionIdData,
@@ -3101,7 +3097,7 @@ AbortSubTransaction(void)
31013097
*/
31023098
AtSubAbort_Memory();
31033099

3104-
DeferredTriggerEndSubXact(false);
3100+
AfterTriggerEndSubXact(false);
31053101
AtSubAbort_Portals(s->parent->transactionIdData,
31063102
s->parent->curTransactionOwner);
31073103
AtEOSubXact_LargeObject(false, s->transactionIdData,

‎src/backend/commands/copy.c

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.230 2004/08/29 05:06:41 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.231 2004/09/10 18:39:56 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1610,6 +1610,11 @@ CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
16101610
}
16111611
}
16121612

1613+
/*
1614+
* Prepare to catch AFTER triggers.
1615+
*/
1616+
AfterTriggerBeginQuery();
1617+
16131618
/*
16141619
* Check BEFORE STATEMENT insertion triggers. It's debateable whether
16151620
* we should do this for COPY, since it's not really an "INSERT"
@@ -1974,6 +1979,11 @@ CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
19741979
*/
19751980
ExecASInsertTriggers(estate, resultRelInfo);
19761981

1982+
/*
1983+
* Handle queued AFTER triggers
1984+
*/
1985+
AfterTriggerEndQuery();
1986+
19771987
pfree(values);
19781988
pfree(nulls);
19791989

‎src/backend/commands/explain.c

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994-5, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.124 2004/08/29 05:06:41 momjian Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.125 2004/09/10 18:39:56 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -18,6 +18,7 @@
1818
#include "catalog/pg_type.h"
1919
#include "commands/explain.h"
2020
#include "commands/prepare.h"
21+
#include "commands/trigger.h"
2122
#include "executor/executor.h"
2223
#include "executor/instrument.h"
2324
#include "lib/stringinfo.h"
@@ -206,6 +207,10 @@ ExplainOnePlan(QueryDesc *queryDesc, ExplainStmt *stmt,
206207

207208
gettimeofday(&starttime, NULL);
208209

210+
/* If analyzing, we need to cope with queued triggers */
211+
if (stmt->analyze)
212+
AfterTriggerBeginQuery();
213+
209214
/* call ExecutorStart to prepare the plan for execution */
210215
ExecutorStart(queryDesc, false, !stmt->analyze);
211216

@@ -255,12 +260,16 @@ ExplainOnePlan(QueryDesc *queryDesc, ExplainStmt *stmt,
255260
}
256261

257262
/*
258-
* Close down the query and free resources. Include time for this in
259-
* the total runtime.
263+
* Close down the query and free resources; also run any queued
264+
* AFTER triggers. Include time for this in the total runtime.
260265
*/
261266
gettimeofday(&starttime, NULL);
262267

263268
ExecutorEnd(queryDesc);
269+
270+
if (stmt->analyze)
271+
AfterTriggerEndQuery();
272+
264273
FreeQueryDesc(queryDesc);
265274

266275
CommandCounterIncrement();

0 commit comments

Comments
 (0)