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

Commit8561e48

Browse files
committed
Transaction control in PL procedures
In each of the supplied procedural languages (PL/pgSQL, PL/Perl,PL/Python, PL/Tcl), add language-specific commit and rollbackfunctions/commands to control transactions in procedures in thatlanguage. Add similar underlying functions to SPI. Some additionalcleanup so that transaction commit or abort doesn't blow away datastructures still used by the procedure call. Add execution contexttracking to CALL and DO statements so that transaction control commandscan only be issued in top-level procedure and block calls, not functioncalls or other procedure or block calls.- SPIAdd a new function SPI_connect_ext() that is like SPI_connect() butallows passing option flags. The only option flag right now isSPI_OPT_NONATOMIC. A nonatomic SPI connection can execute transactioncontrol commands, otherwise it's not allowed. This is meant to bepassed down from CALL and DO statements which themselves know in whichcontext they are called. A nonatomic SPI connection uses differentmemory management. A normal SPI connection allocates its memory inTopTransactionContext. For nonatomic connections we use PortalContextinstead. As the comment in SPI_connect_ext() (previously SPI_connect())indicates, one could potentially use PortalContext in all cases, but itseems safest to leave the existing uses alone, because this stuff iscomplicated enough already.SPI also gets new functions SPI_start_transaction(), SPI_commit(), andSPI_rollback(), which can be used by PLs to implement their transactioncontrol logic.- portalmem.cSome adjustments were made in the code that cleans up portals attransaction abort. The portal code could already handle a command*committing* a transaction and continuing (e.g., VACUUM), but it was notquite prepared for a command *aborting* a transaction and continuing.In AtAbort_Portals(), remove the code that marks an active portal asfailed. As the comment there already predicted, this doesn't work ifthe running command wants to keep running after transaction abort. Andit's actually not necessary, because pquery.c is careful to run allportal code in a PG_TRY block and explicitly runs MarkPortalFailed() ifthere is an exception. So the code in AtAbort_Portals() is never usedanyway.In AtAbort_Portals() and AtCleanup_Portals(), we need to be careful notto clean up active portals too much. This mirrors similar code inPreCommit_Portals().- PL/PerlGets new functions spi_commit() and spi_rollback()- PL/pgSQLGets new commands COMMIT and ROLLBACK.Update the PL/SQL porting example in the documentation to reflect thattransactions are now possible in procedures.- PL/PythonGets new functions plpy.commit and plpy.rollback.- PL/TclGets new commands commit and rollback.Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
1 parentb9ff79b commit8561e48

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

43 files changed

+2149
-96
lines changed

‎doc/src/sgml/plperl.sgml

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -661,6 +661,60 @@ SELECT release_hosts_query();
661661
</para>
662662
</listitem>
663663
</varlistentry>
664+
665+
<varlistentry>
666+
<term>
667+
<literal><function>spi_commit()</function></literal>
668+
<indexterm>
669+
<primary>spi_commit</primary>
670+
<secondary>in PL/Perl</secondary>
671+
</indexterm>
672+
</term>
673+
<term>
674+
<literal><function>spi_rollback()</function></literal>
675+
<indexterm>
676+
<primary>spi_rollback</primary>
677+
<secondary>in PL/Perl</secondary>
678+
</indexterm>
679+
</term>
680+
<listitem>
681+
<para>
682+
Commit or roll back the current transaction. This can only be called
683+
in a procedure or anonymous code block (<command>DO</command> command)
684+
called from the top level. (Note that it is not possible to run the
685+
SQL commands <command>COMMIT</command> or <command>ROLLBACK</command>
686+
via <function>spi_exec_query</function> or similar. It has to be done
687+
using these functions.) After a transaction is ended, a new
688+
transaction is automatically started, so there is no separate function
689+
for that.
690+
</para>
691+
692+
<para>
693+
Here is an example:
694+
<programlisting>
695+
CREATE PROCEDURE transaction_test1()
696+
LANGUAGE plperl
697+
AS $$
698+
foreach my $i (0..9) {
699+
spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
700+
if ($i % 2 == 0) {
701+
spi_commit();
702+
} else {
703+
spi_rollback();
704+
}
705+
}
706+
$$;
707+
708+
CALL transaction_test1();
709+
</programlisting>
710+
</para>
711+
712+
<para>
713+
Transactions cannot be ended when a cursor created by
714+
<function>spi_query</function> is open.
715+
</para>
716+
</listitem>
717+
</varlistentry>
664718
</variablelist>
665719
</sect2>
666720

‎doc/src/sgml/plpgsql.sgml

Lines changed: 47 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -3449,6 +3449,48 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
34493449

34503450
</sect1>
34513451

3452+
<sect1 id="plpgsql-transactions">
3453+
<title>Transaction Management</title>
3454+
3455+
<para>
3456+
In procedures invoked by the <command>CALL</command> command from the top
3457+
level as well as in anonymous code blocks (<command>DO</command> command)
3458+
called from the top level, it is possible to end transactions using the
3459+
commands <command>COMMIT</command> and <command>ROLLBACK</command>. A new
3460+
transaction is started automatically after a transaction is ended using
3461+
these commands, so there is no separate <command>START
3462+
TRANSACTION</command> command. (Note that <command>BEGIN</command> and
3463+
<command>END</command> have different meanings in PL/pgSQL.)
3464+
</para>
3465+
3466+
<para>
3467+
Here is a simple example:
3468+
<programlisting>
3469+
CREATE PROCEDURE transaction_test1()
3470+
LANGUAGE plpgsql
3471+
AS $$
3472+
BEGIN
3473+
FOR i IN 0..9 LOOP
3474+
INSERT INTO test1 (a) VALUES (i);
3475+
IF i % 2 = 0 THEN
3476+
COMMIT;
3477+
ELSE
3478+
ROLLBACK;
3479+
END IF;
3480+
END LOOP;
3481+
END
3482+
$$;
3483+
3484+
CALL transaction_test1();
3485+
</programlisting>
3486+
</para>
3487+
3488+
<para>
3489+
A transaction cannot be ended inside a loop over a query result, nor
3490+
inside a block with exception handlers.
3491+
</para>
3492+
</sect1>
3493+
34523494
<sect1 id="plpgsql-errors-and-messages">
34533495
<title>Errors and Messages</title>
34543496

@@ -5432,14 +5474,13 @@ SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
54325474
<programlisting>
54335475
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
54345476
a_running_job_count INTEGER;
5435-
PRAGMA AUTONOMOUS_TRANSACTION; -- <co id="co.plpgsql-porting-pragma"/>
54365477
BEGIN
5437-
LOCK TABLE cs_jobs IN EXCLUSIVE MODE; -- <co id="co.plpgsql-porting-locktable"/>
5478+
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
54385479

54395480
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
54405481

54415482
IF a_running_job_count &gt; 0 THEN
5442-
COMMIT; -- free lock <co id="co.plpgsql-porting-commit"/>
5483+
COMMIT; -- free lock
54435484
raise_application_error(-20000,
54445485
'Unable to create a new job: a job is currently running.');
54455486
END IF;
@@ -5459,45 +5500,11 @@ show errors
54595500
</programlisting>
54605501
</para>
54615502

5462-
<para>
5463-
Procedures like this can easily be converted into <productname>PostgreSQL</productname>
5464-
functions returning <type>void</type>. This procedure in
5465-
particular is interesting because it can teach us some things:
5466-
5467-
<calloutlist>
5468-
<callout arearefs="co.plpgsql-porting-pragma">
5469-
<para>
5470-
There is no <literal>PRAGMA</literal> statement in <productname>PostgreSQL</productname>.
5471-
</para>
5472-
</callout>
5473-
5474-
<callout arearefs="co.plpgsql-porting-locktable">
5475-
<para>
5476-
If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</application>,
5477-
the lock will not be released until the calling transaction is
5478-
finished.
5479-
</para>
5480-
</callout>
5481-
5482-
<callout arearefs="co.plpgsql-porting-commit">
5483-
<para>
5484-
You cannot issue <command>COMMIT</command> in a
5485-
<application>PL/pgSQL</application> function. The function is
5486-
running within some outer transaction and so <command>COMMIT</command>
5487-
would imply terminating the function's execution. However, in
5488-
this particular case it is not necessary anyway, because the lock
5489-
obtained by the <command>LOCK TABLE</command> will be released when
5490-
we raise an error.
5491-
</para>
5492-
</callout>
5493-
</calloutlist>
5494-
</para>
5495-
54965503
<para>
54975504
This is how we could port this procedure to <application>PL/pgSQL</application>:
54985505

54995506
<programlisting>
5500-
CREATE OR REPLACEFUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
5507+
CREATE OR REPLACEPROCEDURE cs_create_job(v_job_id integer) AS $$
55015508
DECLARE
55025509
a_running_job_count integer;
55035510
BEGIN
@@ -5506,6 +5513,7 @@ BEGIN
55065513
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
55075514

55085515
IF a_running_job_count &gt; 0 THEN
5516+
COMMIT; -- free lock
55095517
RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <co id="co.plpgsql-porting-raise"/>
55105518
END IF;
55115519

@@ -5518,6 +5526,7 @@ BEGIN
55185526
WHEN unique_violation THEN -- <co id="co.plpgsql-porting-exception"/>
55195527
-- don't worry if it already exists
55205528
END;
5529+
COMMIT;
55215530
END;
55225531
$$ LANGUAGE plpgsql;
55235532
</programlisting>
@@ -5541,12 +5550,6 @@ $$ LANGUAGE plpgsql;
55415550
</para>
55425551
</callout>
55435552
</calloutlist>
5544-
5545-
The main functional difference between this procedure and the
5546-
Oracle equivalent is that the exclusive lock on the <literal>cs_jobs</literal>
5547-
table will be held until the calling transaction completes. Also, if
5548-
the caller later aborts (for example due to an error), the effects of
5549-
this procedure will be rolled back.
55505553
</para>
55515554
</example>
55525555
</sect2>

‎doc/src/sgml/plpython.sgml

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1370,6 +1370,47 @@ $$ LANGUAGE plpythonu;
13701370
</sect2>
13711371
</sect1>
13721372

1373+
<sect1 id="plpython-transactions">
1374+
<title>Transaction Management</title>
1375+
1376+
<para>
1377+
In a procedure called from the top level or an anonymous code block
1378+
(<command>DO</command> command) called from the top level it is possible to
1379+
control transactions. To commit the current transaction, call
1380+
<literal>plpy.commit()</literal>. To roll back the current transaction,
1381+
call <literal>plpy.rollback()</literal>. (Note that it is not possible to
1382+
run the SQL commands <command>COMMIT</command> or
1383+
<command>ROLLBACK</command> via <function>plpy.execute</function> or
1384+
similar. It has to be done using these functions.) After a transaction is
1385+
ended, a new transaction is automatically started, so there is no separate
1386+
function for that.
1387+
</para>
1388+
1389+
<para>
1390+
Here is an example:
1391+
<programlisting>
1392+
CREATE PROCEDURE transaction_test1()
1393+
LANGUAGE plpythonu
1394+
AS $$
1395+
for i in range(0, 10):
1396+
plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
1397+
if i % 2 == 0:
1398+
plpy.commit()
1399+
else:
1400+
plpy.rollback()
1401+
$$;
1402+
1403+
CALL transaction_test1();
1404+
</programlisting>
1405+
</para>
1406+
1407+
<para>
1408+
Transactions cannot be ended when a cursor created by
1409+
<literal>plpy.cursor</literal> is open or when an explicit subtransaction
1410+
is active.
1411+
</para>
1412+
</sect1>
1413+
13731414
<sect1 id="plpython-util">
13741415
<title>Utility Functions</title>
13751416
<para>

‎doc/src/sgml/pltcl.sgml

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1002,6 +1002,47 @@ $$ LANGUAGE pltcl;
10021002
</para>
10031003
</sect1>
10041004

1005+
<sect1 id="pltcl-transactions">
1006+
<title>Transaction Management</title>
1007+
1008+
<para>
1009+
In a procedure called from the top level or an anonymous code block
1010+
(<command>DO</command> command) called from the top level it is possible
1011+
to control transactions. To commit the current transaction, call the
1012+
<literal>commit</literal> command. To roll back the current transaction,
1013+
call the <literal>rollback</literal> command. (Note that it is not
1014+
possible to run the SQL commands <command>COMMIT</command> or
1015+
<command>ROLLBACK</command> via <function>spi_exec</function> or similar.
1016+
It has to be done using these functions.) After a transaction is ended,
1017+
a new transaction is automatically started, so there is no separate
1018+
command for that.
1019+
</para>
1020+
1021+
<para>
1022+
Here is an example:
1023+
<programlisting>
1024+
CREATE PROCEDURE transaction_test1()
1025+
LANGUAGE pltcl
1026+
AS $$
1027+
for {set i 0} {$i &lt; 10} {incr i} {
1028+
spi_exec "INSERT INTO test1 (a) VALUES ($i)"
1029+
if {$i % 2 == 0} {
1030+
commit
1031+
} else {
1032+
rollback
1033+
}
1034+
}
1035+
$$;
1036+
1037+
CALL transaction_test1();
1038+
</programlisting>
1039+
</para>
1040+
1041+
<para>
1042+
Transactions cannot be ended when an explicit subtransaction is active.
1043+
</para>
1044+
</sect1>
1045+
10051046
<sect1 id="pltcl-config">
10061047
<title>PL/Tcl Configuration</title>
10071048

‎doc/src/sgml/ref/call.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,13 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
7070
<para>
7171
To call a function (not a procedure), use <command>SELECT</command> instead.
7272
</para>
73+
74+
<para>
75+
If <command>CALL</command> is executed in a transaction block, then the
76+
called procedure cannot execute transaction control statements.
77+
Transaction control statements are only allowed if <command>CALL</command>
78+
is executed in its own transaction.
79+
</para>
7380
</refsect1>
7481

7582
<refsect1>

‎doc/src/sgml/ref/create_procedure.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -228,6 +228,13 @@ CREATE [ OR REPLACE ] PROCEDURE
228228
procedure exit, unless the current transaction is rolled back.
229229
</para>
230230

231+
<para>
232+
If a <literal>SET</literal> clause is attached to a procedure, then
233+
that procedure cannot execute transaction control statements (for
234+
example, <command>COMMIT</command> and <command>ROLLBACK</command>,
235+
depending on the language).
236+
</para>
237+
231238
<para>
232239
See <xref linkend="sql-set"/> and
233240
<xref linkend="runtime-config"/>

‎doc/src/sgml/ref/do.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,13 @@ DO [ LANGUAGE <replaceable class="parameter">lang_name</replaceable> ] <replacea
9191
This is the same privilege requirement as for creating a function
9292
in the language.
9393
</para>
94+
95+
<para>
96+
If <command>DO</command> is executed in a transaction block, then the
97+
procedure code cannot execute transaction control statements. Transaction
98+
control statements are only allowed if <command>DO</command> is executed in
99+
its own transaction.
100+
</para>
94101
</refsect1>
95102

96103
<refsect1 id="sql-do-examples">

0 commit comments

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