Verwalten von Transaktionen - Amazon Redshift

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Verwalten von Transaktionen

Sie können eine gespeicherte Prozedur mit standardmäßigem Transaktionsverwaltungsverhalten oder nichtatomarem Verhalten erstellen.

Standardmodus der Transaktionsverwaltung für gespeicherte Prozeduren

Das standardmäßige automatische Commit-Verhalten im Transaktionsmodus bewirkt, dass jeder SQL Befehl, der separat ausgeführt wird, einzeln festgeschrieben wird. Ein Aufruf einer gespeicherten Prozedur wird als ein einziger SQL Befehl behandelt. Die SQL Anweisungen innerhalb einer Prozedur verhalten sich so, als ob sie sich in einem Transaktionsblock befinden würden, der implizit beginnt, wenn der Aufruf beginnt, und endet, wenn der Aufruf beendet wird. Ein verschachtelter Aufruf einer anderen Prozedur wird wie jede andere SQL Anweisung behandelt und arbeitet im Kontext derselben Transaktion wie der Aufrufer. Weitere Informationen über automatisches Commit-Verhalten finden Sie unter Serialisierbare Isolierung.

Nehmen wir jedoch an, dass Sie eine gespeicherte Prozedur innerhalb eines benutzerdefinierten Transaktionsblocks aufrufen (definiert durch... BEGIN COMMIT). In diesem Fall werden alle Anweisungen in der gespeicherten Prozedur im Kontext der vom Benutzer angegebenen Transaktion ausgeführt. Die Prozedur wird beim Beenden nicht implizit übergeben. Der Anrufer steuert die Commit- oder Rollback-Prozedur.

Tritt beim Ausführen einer gespeicherten Prozedur ein Fehler auf, werden alle in der aktuellen Transaktion vorgenommenen Änderungen zurückgesetzt.

Sie können die folgenden Transaktionskontroll-Anweisungen in einer gespeicherten Prozedur verwenden:

  • COMMIT— schreibt alle in der aktuellen Transaktion ausgeführten Arbeiten fest und startet implizit eine neue Transaktion. Weitere Informationen finden Sie unter COMMIT.

  • ROLLBACK— macht die in der aktuellen Transaktion geleistete Arbeit rückgängig und beginnt implizit mit einer neuen Transaktion. Weitere Informationen finden Sie unter ROLLBACK.

TRUNCATEist eine weitere Anweisung, die Sie innerhalb einer gespeicherten Prozedur ausgeben können und die das Transaktionsmanagement beeinflusst. Führt in Amazon Redshift implizit TRUNCATE einen Commit aus. Dieses Verhalten wird auch im Kontext von gespeicherten Prozeduren beibehalten. Wenn eine TRUNCATE Anweisung innerhalb einer gespeicherten Prozedur ausgegeben wird, schreibt sie die aktuelle Transaktion fest und beginnt eine neue. Weitere Informationen finden Sie unter TRUNCATE.

Alle Anweisungen, die auf eine COMMITROLLBACK, oder TRUNCATE -Anweisung folgen, werden im Kontext einer neuen Transaktion ausgeführt. Sie tun dies so langeCOMMIT, bis eineROLLBACK, oder TRUNCATE -Anweisung gefunden wird oder die gespeicherte Prozedur beendet wird.

Wenn Sie innerhalb einer gespeicherten Prozedur eine COMMITROLLBACK, oder TRUNCATE -Anweisung verwenden, gelten die folgenden Einschränkungen:

  • Wenn die gespeicherte Prozedur innerhalb eines Transaktionsblocks aufgerufen wird, kann sie keineCOMMIT,ROLLBACK, TRUNCATE -Anweisung ausgeben. Diese Einschränkung gilt innerhalb des eigenen Hauptteils der gespeicherten Prozedur sowie innerhalb aller verschachtelten Prozeduraufrufe.

  • Wenn die gespeicherte Prozedur mit SET config Optionen erstellt wurde, kann sie keineCOMMIT,ROLLBACK, TRUNCATE -Anweisung ausgeben. Diese Einschränkung gilt innerhalb des eigenen Hauptteils der gespeicherten Prozedur sowie innerhalb aller verschachtelten Prozeduraufrufe.

  • Jeder Cursor, der (explizit oder implizit) geöffnet ist, wird automatisch geschlossen, wenn eine COMMITROLLBACK, oder TRUNCATE -Anweisung verarbeitet wird. Einschränkungen für explizite oder implizite Cursor finden Sie unter Einschränkungen gespeicherter Prozeduren.

Außerdem können Sie Dynamic SQL nicht ausführen COMMIT oder ROLLBACK verwenden. Sie können jedoch TRUNCATE dynamisch ausführenSQL. Weitere Informationen finden Sie unter Dynamisches SQL.

Beachten Sie bei der Arbeit mit gespeicherten Prozeduren, dass die END Anweisungen BEGIN und in PL/PG nur zur SQL Gruppierung dienen. Sie beginnen weder eine Transaktion, noch beenden sie eine. Weitere Informationen finden Sie unter Block.

Das folgende Beispiel zeigt das Transaktionsverhalten beim Aufruf einer gespeicherten Prozedur aus einem explizitenTransaktionsblock heraus. Die beiden Insert-Anweisungen, die von außerhalb der gespeicherten Prozedur sowie aus ihr heraus ausgegeben werden, sind alle Teil derselben Transaktion (3382). Die Transaktion wird bestätigt, wenn der Benutzer den expliziten Commit durchführt.

CREATE OR REPLACE PROCEDURE sp_insert_table_a(a int) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO test_table_a values (a); END; $$; Begin; insert into test_table_a values (1); Call sp_insert_table_a(2); insert into test_table_a values (3); Commit; select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+-----+---------+---------------------------------------- 103 | 3382 | 599 | UTILITY | Begin; 103 | 3382 | 599 | QUERY | insert into test_table_a values (1); 103 | 3382 | 599 | UTILITY | Call sp_insert_table_a(2); 103 | 3382 | 599 | QUERY | INSERT INTO test_table_a values ( $1 ) 103 | 3382 | 599 | QUERY | insert into test_table_a values (3); 103 | 3382 | 599 | UTILITY | COMMIT

Sehen wir uns im Gegenzug folgendes Beispiel an: Wenn dieselben Anweisungen von außerhalb eines expliziten Transaktionsblocks ausgegeben werden, und Autocommit der Sitzung auf ON gesetzt ist, wird jede Anweisung in einer eigenen Transaktion ausgeführt.

insert into test_table_a values (1); Call sp_insert_table_a(2); insert into test_table_a values (3); select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+-----+---------+------------------------------------------------------------------------------------------------------------------------------------------------- 103 | 3388 | 599 | QUERY | insert into test_table_a values (1); 103 | 3388 | 599 | UTILITY | COMMIT 103 | 3389 | 599 | UTILITY | Call sp_insert_table_a(2); 103 | 3389 | 599 | QUERY | INSERT INTO test_table_a values ( $1 ) 103 | 3389 | 599 | UTILITY | COMMIT 103 | 3390 | 599 | QUERY | insert into test_table_a values (3); 103 | 3390 | 599 | UTILITY | COMMIT

Im folgenden Beispiel wird nach dem Einfügen in eine TRUNCATE Anweisung ausgegeben. test_table_a Die TRUNCATE Anweisung gibt einen impliziten Commit aus, der die aktuelle Transaktion festschreibt (3335) und eine neue startet (3336). Die neue Transaktion wird beim Beenden der Prozedur bestätigt.

CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO test_table_a values (a); TRUNCATE test_table_b; INSERT INTO test_table_b values (b); END; $$; Call sp_truncate_proc(1,2); select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+-------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 103 | 3335 | 23636 | UTILITY | Call sp_truncate_proc(1,2); 103 | 3335 | 23636 | QUERY | INSERT INTO test_table_a values ( $1 ) 103 | 3335 | 23636 | UTILITY | TRUNCATE test_table_b 103 | 3335 | 23636 | UTILITY | COMMIT 103 | 3336 | 23636 | QUERY | INSERT INTO test_table_b values ( $1 ) 103 | 3336 | 23636 | UTILITY | COMMIT

Im folgenden Beispiel wird ein From TRUNCATE aus einem verschachtelten Aufruf ausgegeben. Der TRUNCATE Commit überträgt die gesamte Arbeit, die bisher in den äußeren und inneren Prozeduren einer Transaktion ausgeführt wurde (3344). Es startet eine neue Transaktion (3345). Die neue Transaktion wird beim Beenden der äußeren Prozedur bestätigt.

CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO inner_table values (c); TRUNCATE outer_table; INSERT INTO inner_table values (d); END; $$; CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO outer_table values (a); Call sp_inner(c, d); INSERT INTO outer_table values (b); END; $$; Call sp_outer(1, 2, 3, 4); select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+-------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 103 | 3344 | 23636 | UTILITY | Call sp_outer(1, 2, 3, 4); 103 | 3344 | 23636 | QUERY | INSERT INTO outer_table values ( $1 ) 103 | 3344 | 23636 | UTILITY | CALL sp_inner( $1 , $2 ) 103 | 3344 | 23636 | QUERY | INSERT INTO inner_table values ( $1 ) 103 | 3344 | 23636 | UTILITY | TRUNCATE outer_table 103 | 3344 | 23636 | UTILITY | COMMIT 103 | 3345 | 23636 | QUERY | INSERT INTO inner_table values ( $1 ) 103 | 3345 | 23636 | QUERY | INSERT INTO outer_table values ( $1 ) 103 | 3345 | 23636 | UTILITY | COMMIT

Das folgende Beispiel zeigt, dass der Cursor geschlossen cur1 wurde, als die TRUNCATE Anweisung bestätigt wurde.

CREATE OR REPLACE PROCEDURE sp_open_cursor_truncate() LANGUAGE plpgsql AS $$ DECLARE rec RECORD; cur1 cursor for select * from test_table_a order by 1; BEGIN open cur1; TRUNCATE table test_table_b; Loop fetch cur1 into rec; raise info '%', rec.c1; exit when not found; End Loop; END $$; call sp_open_cursor_truncate(); ERROR: cursor "cur1" does not exist CONTEXT: PL/pgSQL function "sp_open_cursor_truncate" line 8 at fetch

Das folgende Beispiel gibt eine TRUNCATE Anweisung aus und kann nicht innerhalb eines expliziten Transaktionsblocks aufgerufen werden.

CREATE OR REPLACE PROCEDURE sp_truncate_atomic() LANGUAGE plpgsql AS $$ BEGIN TRUNCATE test_table_b; END; $$; Begin; Call sp_truncate_atomic(); ERROR: TRUNCATE cannot be invoked from a procedure that is executing in an atomic context. HINT: Try calling the procedure as a top-level call i.e. not from within an explicit transaction block. Or, if this procedure (or one of its ancestors in the call chain) was created with SET config options, recreate the procedure without them. CONTEXT: SQL statement "TRUNCATE test_table_b" PL/pgSQL function "sp_truncate_atomic" line 2 at SQL statement

Das folgende Beispiel zeigt, dass ein Benutzer, der kein Superuser oder Eigentümer einer Tabelle ist, eine TRUNCATE Anweisung für die Tabelle ausgeben kann. Der Benutzer verwendet dafür eine Security Definer-gespeicherte Prozedur. Das Beispiel zeigt die folgenden Aktionen:

  • Der Benutzer1 erstellt eine Tabelle test_tbl.

  • Der Benutzer1 erstellt die gespeicherte Prozedur sp_truncate_test_tbl.

  • Der Benutzer1 erteilt Benutzer2 die Berechtigung EXECUTE für die gespeicherte Prozedur.

  • Der Benutzer2 führt die gespeicherte Prozedur aus, um Tabelle verkürzen test_tbl. Das Beispiel zeigt die Zeilenanzahl vor und nach dem Befehl TRUNCATE.

set session_authorization to user1; create table test_tbl(id int, name varchar(20)); insert into test_tbl values (1,'john'), (2, 'mary'); CREATE OR REPLACE PROCEDURE sp_truncate_test_tbl() LANGUAGE plpgsql AS $$ DECLARE tbl_rows int; BEGIN select count(*) into tbl_rows from test_tbl; RAISE INFO 'RowCount before Truncate: %', tbl_rows; TRUNCATE test_tbl; select count(*) into tbl_rows from test_tbl; RAISE INFO 'RowCount after Truncate: %', tbl_rows; END; $$ SECURITY DEFINER; grant execute on procedure sp_truncate_test_tbl() to user2; reset session_authorization; set session_authorization to user2; call sp_truncate_test_tbl(); INFO: RowCount before Truncate: 2 INFO: RowCount after Truncate: 0 CALL reset session_authorization;

Das folgende Beispiel gibt COMMIT zweimal eine Ausgabe aus. Im ersten Fall wird COMMIT die gesamte in Transaktion 10363 ausgeführte Arbeit festgeschrieben und implizit die Transaktion 10364 gestartet. Transaktion 10364 wird durch die zweite Anweisung festgeschrieben. COMMIT

CREATE OR REPLACE PROCEDURE sp_commit(a int, b int) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO test_table values (a); COMMIT; INSERT INTO test_table values (b); COMMIT; END; $$; call sp_commit(1,2); select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+-------+------+---------+----------------------------------------------------------------------------------------------------------------- 100 | 10363 | 3089 | UTILITY | call sp_commit(1,2); 100 | 10363 | 3089 | QUERY | INSERT INTO test_table values ( $1 ) 100 | 10363 | 3089 | UTILITY | COMMIT 100 | 10364 | 3089 | QUERY | INSERT INTO test_table values ( $1 ) 100 | 10364 | 3089 | UTILITY | COMMIT

Im folgenden Beispiel wird eine ROLLBACK Anweisung ausgegeben, wenn der sum_vals Wert größer als 2 ist. Die erste ROLLBACK Anweisung macht die gesamte in Transaktion 10377 ausgeführte Arbeit rückgängig und startet eine neue Transaktion 10378. Die Transaktion 10378 wird beim Beenden der Prozedur bestätigt.

CREATE OR REPLACE PROCEDURE sp_rollback(a int, b int) LANGUAGE plpgsql AS $$ DECLARE sum_vals int; BEGIN INSERT INTO test_table values (a); SELECT sum(c1) into sum_vals from test_table; IF sum_vals > 2 THEN ROLLBACK; END IF; INSERT INTO test_table values (b); END; $$; call sp_rollback(1, 2); select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+-------+------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 100 | 10377 | 3089 | UTILITY | call sp_rollback(1, 2); 100 | 10377 | 3089 | QUERY | INSERT INTO test_table values ( $1 ) 100 | 10377 | 3089 | QUERY | SELECT sum(c1) from test_table 100 | 10377 | 3089 | QUERY | Undoing 1 transactions on table 133646 with current xid 10377 : 10377 100 | 10378 | 3089 | QUERY | INSERT INTO test_table values ( $1 ) 100 | 10378 | 3089 | UTILITY | COMMIT

Nichtatomarer Modus der Transaktionsverwaltung für gespeicherte Prozeduren

Eine im NONATOMIC Modus erstellte gespeicherte Prozedur weist ein anderes Transaktionssteuerungsverhalten auf als eine im Standardmodus erstellte Prozedur. Ähnlich dem automatischen Commit-Verhalten von SQL Befehlen außerhalb von gespeicherten Prozeduren wird jede SQL Anweisung innerhalb einer NONATOMIC Prozedur in einer eigenen Transaktion ausgeführt und automatisch festgeschrieben. Wenn ein Benutzer einen expliziten Transaktionsblock innerhalb einer NONATOMIC gespeicherten Prozedur beginnt, werden die SQL Anweisungen innerhalb des Blocks nicht automatisch festgeschrieben. Der Transaktionsblock steuert den Commit oder das Rollback der darin enthaltenen Anweisungen.

In NONATOMIC gespeicherten Prozeduren können Sie mithilfe der START TRANSACTION Anweisung einen expliziten Transaktionsblock innerhalb der Prozedur öffnen. Wenn es jedoch bereits einen offenen Transaktionsblock gibt, hat diese Anweisung keinerlei Auswirkungen, da Amazon Redshift Untertransaktionen nicht unterstützt. Die vorherige Transaktion wird fortgesetzt.

Wenn Sie mit FOR Cursorschleifen innerhalb einer NONATOMIC Prozedur arbeiten, stellen Sie sicher, dass Sie einen expliziten Transaktionsblock öffnen, bevor Sie durch die Ergebnisse einer Abfrage iterieren. Andernfalls wird der Cursor geschlossen, wenn die SQL Anweisung in der Schleife automatisch festgeschrieben wird.

Bei der Verwendung des NONATOMIC Modusverhaltens sind einige Überlegungen zu beachten:

  • Jede SQL Anweisung innerhalb der gespeicherten Prozedur wird automatisch festgeschrieben, wenn kein offener Transaktionsblock vorhanden ist und Autocommit in der Sitzung auf ON gesetzt ist.

  • Sie können eine COMMITROLLBACK//TRUNCATE-Anweisung ausgeben, um die Transaktion zu beenden, wenn die gespeicherte Prozedur innerhalb eines Transaktionsblocks aufgerufen wird. Dies ist im Standardmodus nicht möglich.

  • Sie können eine START TRANSACTION Anweisung ausgeben, um einen Transaktionsblock innerhalb der gespeicherten Prozedur zu starten.

Die folgenden Beispiele veranschaulichen das Transaktionsverhalten bei der Arbeit mit NONATOMIC gespeicherten Prozeduren. In der Sitzung für alle folgenden Beispiele ist Autocommit auf AN gesetzt.

Im folgenden Beispiel hat eine NONATOMIC gespeicherte Prozedur zwei INSERT Anweisungen. Wenn die Prozedur außerhalb eines Transaktionsblocks aufgerufen wird, wird jede INSERT Anweisung innerhalb der Prozedur automatisch festgeschrieben.

CREATE TABLE test_table_a(v int); CREATE TABLE test_table_b(v int); CREATE OR REPLACE PROCEDURE sp_nonatomic_insert_table_a(a int, b int) NONATOMIC AS $$ BEGIN INSERT INTO test_table_a values (a); INSERT INTO test_table_b values (b); END; $$ LANGUAGE plpgsql; Call sp_nonatomic_insert_table_a(1,2); Select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+------------+---------+---------------------------------------- 1 | 1792 | 1073807554 | UTILITY | Call sp_nonatomic_insert_table_a(1,2); 1 | 1792 | 1073807554 | QUERY | INSERT INTO test_table_a values ( $1 ) 1 | 1792 | 1073807554 | UTILITY | COMMIT 1 | 1793 | 1073807554 | QUERY | INSERT INTO test_table_b values ( $1 ) 1 | 1793 | 1073807554 | UTILITY | COMMIT (5 rows)

Wenn die Prozedur jedoch innerhalb einesBEGIN.. aufgerufen wird. COMMITBlock, alle Anweisungen sind Teil derselben Transaktion (xid=1799).

Begin; INSERT INTO test_table_a values (10); Call sp_nonatomic_insert_table_a(20,30); INSERT INTO test_table_b values (40); Commit; Select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+------------+---------+------------------------------------------ 1 | 1799 | 1073914035 | UTILITY | Begin; 1 | 1799 | 1073914035 | QUERY | INSERT INTO test_table_a values (10); 1 | 1799 | 1073914035 | UTILITY | Call sp_nonatomic_insert_table_a(20,30); 1 | 1799 | 1073914035 | QUERY | INSERT INTO test_table_a values ( $1 ) 1 | 1799 | 1073914035 | QUERY | INSERT INTO test_table_b values ( $1 ) 1 | 1799 | 1073914035 | QUERY | INSERT INTO test_table_b values (40); 1 | 1799 | 1073914035 | UTILITY | COMMIT (7 rows)

In diesem Beispiel befinden sich zwei INSERT Anweisungen zwischen... START TRANSACTION COMMIT. Wenn die Prozedur außerhalb eines Transaktionsblocks aufgerufen wird, befinden sich die beiden INSERT Anweisungen in derselben Transaktion (xid=1866).

CREATE OR REPLACE PROCEDURE sp_nonatomic_txn_block(a int, b int) NONATOMIC AS $$ BEGIN START TRANSACTION; INSERT INTO test_table_a values (a); INSERT INTO test_table_b values (b); COMMIT; END; $$ LANGUAGE plpgsql; Call sp_nonatomic_txn_block(1,2); Select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+------------+---------+---------------------------------------- 1 | 1865 | 1073823998 | UTILITY | Call sp_nonatomic_txn_block(1,2); 1 | 1866 | 1073823998 | QUERY | INSERT INTO test_table_a values ( $1 ) 1 | 1866 | 1073823998 | QUERY | INSERT INTO test_table_b values ( $1 ) 1 | 1866 | 1073823998 | UTILITY | COMMIT (4 rows)

Wenn die Prozedur innerhalb eines... aufgerufen wird BEGIN COMMITBlock, das START TRANSACTION Innere der Prozedur tut nichts, weil es bereits eine offene Transaktion gibt. Das COMMIT Innere der Prozedur schreibt die aktuelle Transaktion fest (xid=1876) und startet eine neue.

Begin; INSERT INTO test_table_a values (10); Call sp_nonatomic_txn_block(20,30); INSERT INTO test_table_b values (40); Commit; Select userid, xid, pid, type, trim(text) as stmt_text from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence; userid | xid | pid | type | stmt_text --------+------+------------+---------+---------------------------------------- 1 | 1876 | 1073832133 | UTILITY | Begin; 1 | 1876 | 1073832133 | QUERY | INSERT INTO test_table_a values (10); 1 | 1876 | 1073832133 | UTILITY | Call sp_nonatomic_txn_block(20,30); 1 | 1876 | 1073832133 | QUERY | INSERT INTO test_table_a values ( $1 ) 1 | 1876 | 1073832133 | QUERY | INSERT INTO test_table_b values ( $1 ) 1 | 1876 | 1073832133 | UTILITY | COMMIT 1 | 1878 | 1073832133 | QUERY | INSERT INTO test_table_b values (40); 1 | 1878 | 1073832133 | UTILITY | COMMIT (8 rows)

Dieses Beispiel veranschaulicht die Arbeit mit Cursor-Schleifen. Die Tabelle test_table_a weist drei Werte auf. Ziel ist es, die drei Werte zu durchlaufen und sie in die Tabelle test_table_b einzufügen. Wenn eine NONATOMIC gespeicherte Prozedur auf die folgende Weise erstellt wird, gibt sie nach der Ausführung der INSERT Anweisung in der ersten Schleife den Fehler „cur1" existiert nicht aus. Dies liegt daran, dass der auto Commit von den den geöffneten Cursor INSERT schließt.

insert into test_table_a values (1), (2), (3); CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC LANGUAGE plpgsql AS $$ DECLARE rec RECORD; cur1 cursor for select * from test_table_a order by 1; BEGIN open cur1; Loop fetch cur1 into rec; exit when not found; raise info '%', rec.v; insert into test_table_b values (rec.v); End Loop; END $$; CALL sp_nonatomic_cursor(); INFO: 1 ERROR: cursor "cur1" does not exist CONTEXT: PL/pgSQL function "sp_nonatomic_cursor" line 7 at fetch

Damit die Cursorschleife funktioniert, platzieren Sie sie zwischen STARTTRANSACTION... COMMIT.

insert into test_table_a values (1), (2), (3); CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC LANGUAGE plpgsql AS $$ DECLARE rec RECORD; cur1 cursor for select * from test_table_a order by 1; BEGIN START TRANSACTION; open cur1; Loop fetch cur1 into rec; exit when not found; raise info '%', rec.v; insert into test_table_b values (rec.v); End Loop; COMMIT; END $$; CALL sp_nonatomic_cursor(); INFO: 1 INFO: 2 INFO: 3 CALL