Mengelola transaksi - Amazon Redshift
Mode default tersimpan manajemen transaksi prosedurManajemen transaksi prosedur tersimpan mode nonatomik

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Mengelola transaksi

Anda dapat membuat prosedur tersimpan dengan perilaku manajemen transaksi default atau perilaku nonatomik.

Mode default tersimpan manajemen transaksi prosedur

Perilaku komit otomatis mode transaksi default menyebabkan setiap perintah SQL yang berjalan secara terpisah untuk melakukan komit secara individual. Panggilan ke prosedur tersimpan diperlakukan sebagai perintah SQL tunggal. Pernyataan SQL di dalam prosedur berperilaku seolah-olah mereka berada dalam blok transaksi yang secara implisit dimulai ketika panggilan dimulai dan berakhir ketika panggilan selesai. Panggilan bersarang ke prosedur lain diperlakukan seperti pernyataan SQL lainnya dan beroperasi dalam konteks transaksi yang sama dengan pemanggil. Untuk informasi selengkapnya tentang perilaku komit otomatis, lihatIsolasi yang dapat diserialisasi.

Namun, misalkan Anda memanggil prosedur tersimpan dari dalam blok transaksi yang ditentukan pengguna (didefinisikan oleh BEGIN... COMMIT). Dalam hal ini, semua pernyataan dalam prosedur tersimpan berjalan dalam konteks transaksi yang ditentukan pengguna. Prosedur tidak melakukan secara implisit saat keluar. Penelepon mengontrol prosedur commit atau rollback.

Jika terjadi kesalahan saat menjalankan prosedur tersimpan, semua perubahan yang dilakukan dalam transaksi saat ini dibatalkan.

Anda dapat menggunakan pernyataan kontrol transaksi berikut dalam prosedur tersimpan:

  • COMMIT — melakukan semua pekerjaan yang dilakukan dalam transaksi saat ini dan secara implisit memulai transaksi baru. Untuk informasi selengkapnya, lihat COMMIT.

  • ROLLBACK — mengembalikan pekerjaan yang dilakukan dalam transaksi saat ini dan secara implisit memulai transaksi baru. Untuk informasi selengkapnya, lihat ROLLBACK.

TRUNCATE adalah pernyataan lain yang dapat Anda keluarkan dari dalam prosedur yang disimpan dan memengaruhi manajemen transaksi. Di Amazon Redshift, TRUNCATE mengeluarkan komit secara implisit. Perilaku ini tetap sama dalam konteks prosedur tersimpan. Ketika pernyataan TRUNCATE dikeluarkan dari dalam prosedur yang disimpan, ia melakukan transaksi saat ini dan memulai yang baru. Untuk informasi selengkapnya, lihat MEMOTONG.

Semua pernyataan yang mengikuti pernyataan COMMIT, ROLLBACK, atau TRUNCATE berjalan dalam konteks transaksi baru. Mereka melakukannya sampai pernyataan COMMIT, ROLLBACK, atau TRUNCATE ditemukan atau prosedur yang disimpan keluar.

Bila Anda menggunakan pernyataan COMMIT, ROLLBACK, atau TRUNCATE dari dalam prosedur tersimpan, kendala berikut berlaku:

  • Jika prosedur tersimpan dipanggil dari dalam blok transaksi, itu tidak dapat mengeluarkan pernyataan COMMIT, ROLLBACK, atau TRUNCATE. Pembatasan ini berlaku di dalam tubuh prosedur yang disimpan sendiri dan dalam setiap panggilan prosedur bersarang.

  • Jika prosedur tersimpan dibuat dengan SET config opsi, prosedur tersebut tidak dapat mengeluarkan pernyataan COMMIT, ROLLBACK, atau TRUNCATE. Pembatasan ini berlaku di dalam tubuh prosedur yang disimpan sendiri dan dalam setiap panggilan prosedur bersarang.

  • Setiap kursor yang terbuka (secara eksplisit atau implisit) ditutup secara otomatis ketika pernyataan COMMIT, ROLLBACK, atau TRUNCATE diproses. Untuk batasan pada kursor eksplisit dan implisit, lihat. Pertimbangan untuk dukungan prosedur tersimpan

Selain itu, Anda tidak dapat menjalankan COMMIT atau ROLLBACK menggunakan SQL dinamis. Namun, Anda dapat menjalankan TRUNCATE menggunakan SQL dinamis. Untuk informasi selengkapnya, lihat SQL Dinamis.

Saat bekerja dengan prosedur tersimpan, pertimbangkan bahwa pernyataan BEGIN dan END di PL/PGSQL hanya untuk pengelompokan. Mereka tidak memulai atau mengakhiri transaksi. Untuk informasi selengkapnya, lihat Blokir.

Contoh berikut menunjukkan perilaku transaksi saat memanggil prosedur tersimpan dari dalam blok transaksi eksplisit. Dua pernyataan sisipan yang dikeluarkan dari luar prosedur yang disimpan dan yang dari dalamnya semuanya merupakan bagian dari transaksi yang sama (3382). Transaksi dilakukan ketika pengguna mengeluarkan komit eksplisit.

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

Sebaliknya, ambil contoh ketika pernyataan yang sama dikeluarkan dari luar blok transaksi eksplisit dan sesi memiliki autocommit disetel ke ON. Dalam hal ini, setiap pernyataan berjalan dalam transaksinya sendiri.

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

Contoh berikut mengeluarkan pernyataan TRUNCATE setelah memasukkan ke dalam. test_table_a Pernyataan TRUNCATE mengeluarkan komit implisit yang melakukan transaksi saat ini (3335) dan memulai yang baru (3336). Transaksi baru dilakukan ketika prosedur keluar.

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

Contoh berikut mengeluarkan TRUNCATE dari panggilan bersarang. TRUNCATE melakukan semua pekerjaan yang dilakukan sejauh ini dalam prosedur luar dan dalam dalam suatu transaksi (3344). Ini memulai transaksi baru (3345). Transaksi baru dilakukan ketika prosedur luar keluar.

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

Contoh berikut menunjukkan bahwa kursor ditutup cur1 ketika pernyataan TRUNCATE berkomitmen.

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

Contoh berikut mengeluarkan pernyataan TRUNCATE dan tidak dapat dipanggil dari dalam blok transaksi eksplisit.

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

Contoh berikut menunjukkan bahwa pengguna yang bukan superuser atau pemilik tabel dapat mengeluarkan pernyataan TRUNCATE di atas meja. Pengguna melakukan ini menggunakan prosedur Security Definer tersimpan. Contoh menunjukkan tindakan berikut:

  • Pengguna1 membuat tabeltest_tbl.

  • Pengguna1 membuat prosedur sp_truncate_test_tbl tersimpan.

  • User1 memberikan hak EXECUTE istimewa pada prosedur yang disimpan ke user2.

  • User2 menjalankan prosedur tersimpan untuk memotong tabel. test_tbl Contoh menunjukkan jumlah baris sebelum dan sesudah TRUNCATE perintah. 

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;

Contoh berikut masalah COMMIT dua kali. COMMIT pertama melakukan semua pekerjaan yang dilakukan dalam transaksi 10363 dan secara implisit memulai transaksi 10364. Transaksi 10364 dilakukan oleh pernyataan COMMIT kedua. 

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

Contoh berikut mengeluarkan pernyataan ROLLBACK jika sum_vals lebih besar dari 2. Pernyataan ROLLBACK pertama mengembalikan semua pekerjaan yang dilakukan dalam transaksi 10377 dan memulai transaksi baru 10378. Transaksi 10378 dilakukan saat prosedur keluar. 

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

Manajemen transaksi prosedur tersimpan mode nonatomik

Prosedur tersimpan yang dibuat dalam mode NONATOMIC memiliki perilaku kontrol transaksi yang berbeda dari prosedur yang dibuat dalam mode default. Mirip dengan perilaku komit otomatis perintah SQL di luar prosedur tersimpan, setiap pernyataan SQL di dalam prosedur NONATOMIC berjalan dalam transaksinya sendiri dan melakukan secara otomatis. Jika pengguna memulai blok transaksi eksplisit dalam prosedur tersimpan NONATOMIC, maka pernyataan SQL dalam blok tidak secara otomatis melakukan komit. Blok transaksi mengontrol komit atau pengembalian pernyataan di dalamnya.

Dalam prosedur tersimpan NONATOMIC, Anda dapat membuka blok transaksi eksplisit di dalam prosedur menggunakan pernyataan MULAI TRANSAKSI. Namun, jika sudah ada blok transaksi terbuka, pernyataan ini tidak akan melakukan apa-apa karena Amazon Redshift tidak mendukung sub transaksi. Transaksi sebelumnya terus berlanjut.

Saat Anda bekerja dengan kursor FOR loop di dalam prosedur NONATOMIC, pastikan Anda membuka blok transaksi eksplisit sebelum mengulangi hasil kueri. Jika tidak, kursor ditutup ketika pernyataan SQL di dalam loop secara otomatis berkomitmen.

Beberapa pertimbangan saat menggunakan perilaku mode NONATOMIC adalah sebagai berikut:

  • Setiap pernyataan SQL di dalam prosedur tersimpan secara otomatis dilakukan jika tidak ada blok transaksi terbuka, dan sesi memiliki autocommit disetel ke ON.

  • Anda dapat mengeluarkan pernyataan COMMIT/ROLLBACK/TRUNCATE untuk mengakhiri transaksi jika prosedur yang disimpan dipanggil dari dalam blok transaksi. Ini tidak mungkin dalam mode default.

  • Anda dapat mengeluarkan pernyataan MULAI TRANSAKSI untuk memulai blok transaksi di dalam prosedur yang disimpan.

Contoh berikut menunjukkan perilaku transaksi saat bekerja dengan prosedur tersimpan NONATOMIC. Sesi untuk semua contoh berikut memiliki autocommit disetel ke ON.

Dalam contoh berikut, prosedur tersimpan NONATOMIC memiliki dua pernyataan INSERT. Ketika prosedur dipanggil di luar blok transaksi, setiap pernyataan INSERT dalam prosedur secara otomatis melakukan. 

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)

Namun, ketika prosedur dipanggil dari dalam blok BEGIN.. COMMIT, semua pernyataan adalah bagian dari transaksi yang sama (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)

Dalam contoh ini, dua pernyataan INSERT berada di antara MULAI TRANSAKSI... KOMIT. Ketika prosedur dipanggil di luar blok transaksi, kedua pernyataan INSERT berada dalam transaksi yang sama (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)

Ketika prosedur dipanggil dari dalam blok BEGIN... COMMIT, TRANSAKSI MULAI di dalam prosedur tidak melakukan apa-apa karena sudah ada transaksi terbuka. COMMIT di dalam prosedur melakukan transaksi saat ini (xid = 1876) dan memulai yang baru.

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)

Contoh ini menunjukkan cara bekerja dengan loop kursor. Tabel test_table_a memiliki tiga nilai. Tujuannya adalah untuk mengulangi melalui tiga nilai dan memasukkannya ke dalam tabel test_table_b. Jika prosedur tersimpan NONATOMIC dibuat dengan cara berikut, itu akan melempar kursor kesalahan “cur1" tidak ada setelah mengeksekusi pernyataan INSERT di loop pertama. Ini karena komit otomatis INSERT menutup kursor yang terbuka.

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

Untuk membuat kursor loop bekerja, letakkan di antara MULAI TRANSAKSI... 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