Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbeda481

Browse files
committed
plpgsql does exceptions.
There are still some things that need refinement; in particular I fearthat the recognized set of error condition names probably has little incommon with what Oracle recognizes. But it's a start.
1 parentb5d2821 commitbeda481

File tree

11 files changed

+764
-107
lines changed

11 files changed

+764
-107
lines changed

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 104 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.41 2004/07/11 23:26:51 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.42 2004/07/31 07:39:17 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -1796,6 +1796,101 @@ END LOOP;
17961796
rather than the simple syntax error one might expect to get.
17971797
</para>
17981798
</note>
1799+
</sect2>
1800+
1801+
<sect2 id="plpgsql-error-trapping">
1802+
<title>Trapping Errors</title>
1803+
1804+
<para>
1805+
By default, any error occurring in a <application>PL/pgSQL</>
1806+
function aborts execution of the function, and indeed of the
1807+
surrounding transaction as well. You can trap errors and recover
1808+
from them by using a <command>BEGIN</> block with an
1809+
<literal>EXCEPTION</> clause. The syntax is an extension of the
1810+
normal syntax for a <command>BEGIN</> block:
1811+
1812+
<synopsis>
1813+
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
1814+
<optional> DECLARE
1815+
<replaceable>declarations</replaceable> </optional>
1816+
BEGIN
1817+
<replaceable>statements</replaceable>
1818+
EXCEPTION
1819+
WHEN <replaceable>condition</replaceable> THEN
1820+
<replaceable>handler_statements</replaceable>
1821+
<optional> WHEN <replaceable>condition</replaceable> THEN
1822+
<replaceable>handler_statements</replaceable>
1823+
...
1824+
</optional>
1825+
END;
1826+
</synopsis>
1827+
</para>
1828+
1829+
<para>
1830+
If no error occurs, this form of block simply executes all the
1831+
<replaceable>statements</replaceable>, and then control passes
1832+
to the next statement after <literal>END</>. But if an error
1833+
occurs within the <replaceable>statements</replaceable>, further
1834+
processing of the <replaceable>statements</replaceable> is
1835+
abandoned, and control passes to the <literal>EXCEPTION</> list.
1836+
The list is searched for the first <replaceable>condition</replaceable>
1837+
matching the error that occurred. If a match is found, the
1838+
corresponding <replaceable>handler_statements</replaceable> are
1839+
executed, and then control passes to the next statement after
1840+
<literal>END</>. If no match is found, the error propagates out
1841+
as though the <literal>EXCEPTION</> clause were not there at all:
1842+
the error can be caught by an enclosing block with
1843+
<literal>EXCEPTION</>, or if there is none it aborts processing
1844+
of the function. The special condition name <literal>OTHERS</>
1845+
matches every error type except <literal>QUERY_CANCELED</>.
1846+
(It is possible, but usually not a good idea, to trap
1847+
<literal>QUERY_CANCELED</> by name.)
1848+
</para>
1849+
1850+
<para>
1851+
If a new error occurs within the selected
1852+
<replaceable>handler_statements</replaceable>, it cannot be caught
1853+
by this <literal>EXCEPTION</> clause, but is propagated out.
1854+
A surrounding <literal>EXCEPTION</> clause could catch it.
1855+
</para>
1856+
1857+
<para>
1858+
When an error is caught by an <literal>EXCEPTION</> clause,
1859+
the local variables of the <application>PL/pgSQL</> function
1860+
remain as they were when the error occurred, but all changes
1861+
to persistent database state within the block are rolled back.
1862+
As an example, consider this fragment:
1863+
1864+
<programlisting>
1865+
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
1866+
BEGIN
1867+
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
1868+
x := x + 1;
1869+
y := x / 0;
1870+
EXCEPTION
1871+
WHEN division_by_zero THEN
1872+
RAISE NOTICE 'caught division_by_zero';
1873+
RETURN x;
1874+
END;
1875+
</programlisting>
1876+
1877+
When control reaches the assignment to <literal>y</>, it will
1878+
fail with a <literal>division_by_zero</> error. This will be caught by
1879+
the <literal>EXCEPTION</> clause. The value returned in the
1880+
<command>RETURN</> statement will be the incremented value of
1881+
<literal>x</>, but the effects of the <command>UPDATE</> command will
1882+
have been rolled back. The <command>INSERT</> command is not rolled
1883+
back, however, so the end result is that the database contains
1884+
<literal>Tom Jones</> not <literal>Joe Jones</>.
1885+
</para>
1886+
1887+
<tip>
1888+
<para>
1889+
A block containing an <literal>EXCEPTION</> clause is significantly
1890+
more expensive to enter and exit than a block without one. Therefore,
1891+
don't use <literal>EXCEPTION</> without need.
1892+
</para>
1893+
</tip>
17991894
</sect2>
18001895
</sect1>
18011896

@@ -2120,11 +2215,11 @@ RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="pa
21202215
</synopsis>
21212216

21222217
Possible levels are <literal>DEBUG</literal>,
2123-
<literal>LOG</literal>,
2218+
<literal>LOG</literal>, <literal>INFO</literal>,
21242219
<literal>NOTICE</literal>, <literal>WARNING</literal>,
21252220
and <literal>EXCEPTION</literal>.
2126-
<literal>EXCEPTION</literal> raises an errorandaborts the current
2127-
transaction; the other levels only generate messages of different
2221+
<literal>EXCEPTION</literal> raises an error(which normallyaborts the
2222+
currenttransaction); the other levels only generate messages of different
21282223
priority levels.
21292224
Whether messages of a particular priority are reported to the client,
21302225
written to the server log, or both is controlled by the
@@ -2164,28 +2259,11 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
21642259
</para>
21652260

21662261
<para>
2167-
<productname>PostgreSQL</productname> does not have a very smart
2168-
exception handling model. Whenever the parser, planner/optimizer
2169-
or executor decide that a statement cannot be processed any longer,
2170-
the whole transaction gets aborted and the system jumps back
2171-
into the main loop to get the next command from the client application.
2172-
</para>
2173-
2174-
<para>
2175-
It is possible to hook into the error mechanism to notice that this
2176-
happens. But currently it is impossible to tell what really
2177-
caused the abort (data type format error, floating-point
2178-
error, parse error, etc.). And it is possible that the database server
2179-
is in an inconsistent state at this point so returning to the upper
2180-
executor or issuing more commands might corrupt the whole database.
2181-
</para>
2182-
2183-
<para>
2184-
Thus, the only thing <application>PL/pgSQL</application>
2185-
currently does when it encounters an abort during execution of a
2186-
function or trigger procedure is to add some fields to the message
2187-
telling in which function and where (line number and type of statement)
2188-
the error happened. The error always stops execution of the function.
2262+
<command>RAISE EXCEPTION</command> presently always generates
2263+
the same SQLSTATE code, <literal>P0001</>, no matter what message
2264+
it is invoked with. It is possible to trap this exception with
2265+
<literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
2266+
is no way to tell one <command>RAISE</> from another.
21892267
</para>
21902268
</sect1>
21912269

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

Lines changed: 123 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.173 2004/07/28 14:23:27 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.174 2004/07/31 07:39:18 tgl Exp $
1212
*
1313
* NOTES
1414
*Transaction aborts can now occur two ways:
@@ -1589,7 +1589,8 @@ CleanupTransaction(void)
15891589
* State should still be TRANS_ABORT from AbortTransaction().
15901590
*/
15911591
if (s->state!=TRANS_ABORT)
1592-
elog(FATAL,"CleanupTransaction and not in abort state");
1592+
elog(FATAL,"CleanupTransaction: unexpected state %s",
1593+
TransStateAsString(s->state));
15931594

15941595
/*
15951596
* do abort cleanup processing
@@ -1773,7 +1774,7 @@ CommitTransactionCommand(void)
17731774

17741775
/*
17751776
* We were just issued a SAVEPOINT inside a transaction block.
1776-
* Start a subtransaction. (BeginTransactionBlock already
1777+
* Start a subtransaction. (DefineSavepoint already
17771778
* did PushTransaction, so as to have someplace to put the
17781779
* SUBBEGIN state.)
17791780
*/
@@ -1853,6 +1854,7 @@ CleanupAbortedSubTransactions(bool returnName)
18531854
AssertState(PointerIsValid(s->parent));
18541855
Assert(s->parent->blockState==TBLOCK_SUBINPROGRESS||
18551856
s->parent->blockState==TBLOCK_INPROGRESS||
1857+
s->parent->blockState==TBLOCK_STARTED||
18561858
s->parent->blockState==TBLOCK_SUBABORT_PENDING);
18571859

18581860
/*
@@ -1878,7 +1880,8 @@ CleanupAbortedSubTransactions(bool returnName)
18781880
}
18791881

18801882
AssertState(s->blockState==TBLOCK_SUBINPROGRESS||
1881-
s->blockState==TBLOCK_INPROGRESS);
1883+
s->blockState==TBLOCK_INPROGRESS||
1884+
s->blockState==TBLOCK_STARTED);
18821885

18831886
returnname;
18841887
}
@@ -2468,7 +2471,7 @@ DefineSavepoint(char *name)
24682471
caseTBLOCK_SUBABORT_PENDING:
24692472
caseTBLOCK_SUBENDABORT_RELEASE:
24702473
caseTBLOCK_SUBEND:
2471-
elog(FATAL,"BeginTransactionBlock: unexpected state %s",
2474+
elog(FATAL,"DefineSavepoint: unexpected state %s",
24722475
BlockStateAsString(s->blockState));
24732476
break;
24742477
}
@@ -2657,20 +2660,126 @@ RollbackToSavepoint(List *options)
26572660
}
26582661

26592662
/*
2660-
* RollbackAndReleaseSavepoint
2663+
* BeginInternalSubTransaction
2664+
*This is the same as DefineSavepoint except it allows TBLOCK_STARTED
2665+
*state, and therefore it can safely be used in a function that might
2666+
*be called when not inside a BEGIN block. Also, we automatically
2667+
*cycle through CommitTransactionCommand/StartTransactionCommand
2668+
*instead of expecting the caller to do it.
2669+
*
2670+
* Optionally, name can be NULL to create an unnamed savepoint.
2671+
*/
2672+
void
2673+
BeginInternalSubTransaction(char*name)
2674+
{
2675+
TransactionStates=CurrentTransactionState;
2676+
2677+
switch (s->blockState)
2678+
{
2679+
caseTBLOCK_STARTED:
2680+
caseTBLOCK_INPROGRESS:
2681+
caseTBLOCK_SUBINPROGRESS:
2682+
/* Normal subtransaction start */
2683+
PushTransaction();
2684+
s=CurrentTransactionState;/* changed by push */
2685+
/*
2686+
* Note that we are allocating the savepoint name in the
2687+
* parent transaction's CurTransactionContext, since we
2688+
* don't yet have a transaction context for the new guy.
2689+
*/
2690+
if (name)
2691+
s->name=MemoryContextStrdup(CurTransactionContext,name);
2692+
s->blockState=TBLOCK_SUBBEGIN;
2693+
break;
2694+
2695+
/* These cases are invalid. Reject them altogether. */
2696+
caseTBLOCK_DEFAULT:
2697+
caseTBLOCK_BEGIN:
2698+
caseTBLOCK_SUBBEGIN:
2699+
caseTBLOCK_ABORT:
2700+
caseTBLOCK_SUBABORT:
2701+
caseTBLOCK_ENDABORT:
2702+
caseTBLOCK_END:
2703+
caseTBLOCK_SUBENDABORT_ALL:
2704+
caseTBLOCK_SUBENDABORT:
2705+
caseTBLOCK_SUBABORT_PENDING:
2706+
caseTBLOCK_SUBENDABORT_RELEASE:
2707+
caseTBLOCK_SUBEND:
2708+
elog(FATAL,"BeginInternalSubTransaction: unexpected state %s",
2709+
BlockStateAsString(s->blockState));
2710+
break;
2711+
}
2712+
2713+
CommitTransactionCommand();
2714+
StartTransactionCommand();
2715+
}
2716+
2717+
/*
2718+
* ReleaseCurrentSubTransaction
2719+
*
2720+
* RELEASE (ie, commit) the innermost subtransaction, regardless of its
2721+
* savepoint name (if any).
2722+
* NB: do NOT use CommitTransactionCommand/StartTransactionCommand with this.
2723+
*/
2724+
void
2725+
ReleaseCurrentSubTransaction(void)
2726+
{
2727+
TransactionStates=CurrentTransactionState;
2728+
2729+
if (s->blockState!=TBLOCK_SUBINPROGRESS)
2730+
elog(ERROR,"ReleaseCurrentSubTransaction: unexpected state %s",
2731+
BlockStateAsString(s->blockState));
2732+
MemoryContextSwitchTo(CurTransactionContext);
2733+
CommitTransactionToLevel(GetCurrentTransactionNestLevel());
2734+
}
2735+
2736+
/*
2737+
* RollbackAndReleaseCurrentSubTransaction
26612738
*
2662-
* Executes a ROLLBACK TO command, immediately followed by a RELEASE
2663-
* of the same savepoint.
2739+
* ROLLBACK and RELEASE (ie, abort) the innermost subtransaction, regardless
2740+
* of its savepoint name (if any).
2741+
* NB: do NOT use CommitTransactionCommand/StartTransactionCommand with this.
26642742
*/
26652743
void
2666-
RollbackAndReleaseSavepoint(List*options)
2744+
RollbackAndReleaseCurrentSubTransaction(void)
26672745
{
2668-
TransactionStates;
2746+
TransactionStates=CurrentTransactionState;
26692747

2670-
RollbackToSavepoint(options);
2671-
s=CurrentTransactionState;
2672-
Assert(s->blockState==TBLOCK_SUBENDABORT);
2748+
switch (s->blockState)
2749+
{
2750+
/* Must be in a subtransaction */
2751+
caseTBLOCK_SUBABORT:
2752+
caseTBLOCK_SUBINPROGRESS:
2753+
break;
2754+
2755+
/* these cases are invalid. */
2756+
caseTBLOCK_DEFAULT:
2757+
caseTBLOCK_STARTED:
2758+
caseTBLOCK_ABORT:
2759+
caseTBLOCK_INPROGRESS:
2760+
caseTBLOCK_BEGIN:
2761+
caseTBLOCK_END:
2762+
caseTBLOCK_ENDABORT:
2763+
caseTBLOCK_SUBEND:
2764+
caseTBLOCK_SUBENDABORT_ALL:
2765+
caseTBLOCK_SUBENDABORT:
2766+
caseTBLOCK_SUBABORT_PENDING:
2767+
caseTBLOCK_SUBENDABORT_RELEASE:
2768+
caseTBLOCK_SUBBEGIN:
2769+
elog(FATAL,"RollbackAndReleaseCurrentSubTransaction: unexpected state %s",
2770+
BlockStateAsString(s->blockState));
2771+
break;
2772+
}
2773+
2774+
/*
2775+
* Abort the current subtransaction, if needed.
2776+
*/
2777+
if (s->blockState==TBLOCK_SUBINPROGRESS)
2778+
AbortSubTransaction();
26732779
s->blockState=TBLOCK_SUBENDABORT_RELEASE;
2780+
2781+
/* And clean it up, too */
2782+
CleanupAbortedSubTransactions(false);
26742783
}
26752784

26762785
/*
@@ -2748,7 +2857,7 @@ AbortOutOfAnyTransaction(void)
27482857
* Commit everything from the current transaction level
27492858
* up to the specified level (inclusive).
27502859
*/
2751-
void
2860+
staticvoid
27522861
CommitTransactionToLevel(intlevel)
27532862
{
27542863
TransactionStates=CurrentTransactionState;

‎src/include/access/xact.h‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/access/xact.h,v 1.67 2004/07/27 05:11:24 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/access/xact.h,v 1.68 2004/07/31 07:39:19 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -107,7 +107,9 @@ extern void UserAbortTransactionBlock(void);
107107
externvoidReleaseSavepoint(List*options);
108108
externvoidDefineSavepoint(char*name);
109109
externvoidRollbackToSavepoint(List*options);
110-
externvoidRollbackAndReleaseSavepoint(List*options);
110+
externvoidBeginInternalSubTransaction(char*name);
111+
externvoidReleaseCurrentSubTransaction(void);
112+
externvoidRollbackAndReleaseCurrentSubTransaction(void);
111113
externboolIsSubTransaction(void);
112114
externboolIsTransactionBlock(void);
113115
externboolIsTransactionOrTransactionBlock(void);

‎src/include/utils/errcodes.h‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
* Copyright (c) 2003, PostgreSQL Global Development Group
1313
*
14-
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.13 2004/07/27 05:11:35 tgl Exp $
14+
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.14 2004/07/31 07:39:20 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -326,6 +326,10 @@
326326
#defineERRCODE_CONFIG_FILE_ERRORMAKE_SQLSTATE('F','0', '0','0','0')
327327
#defineERRCODE_LOCK_FILE_EXISTSMAKE_SQLSTATE('F','0', '0','0','1')
328328

329+
/* Class P0 - PL/pgSQL Error (PostgreSQL-specific error class) */
330+
#defineERRCODE_PLPGSQL_ERRORMAKE_SQLSTATE('P','0', '0','0','0')
331+
#defineERRCODE_RAISE_EXCEPTIONMAKE_SQLSTATE('P','0', '0','0','1')
332+
329333
/* Class XX - Internal Error (PostgreSQL-specific error class) */
330334
/* (this is for "can't-happen" conditions and software bugs) */
331335
#defineERRCODE_INTERNAL_ERRORMAKE_SQLSTATE('X','X', '0','0','0')

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp