src/pl/plperl/sql/plperl_transaction.sql

   1 CREATE TABLE test1 (a int, b text);
   2
   3
   4 CREATE PROCEDURE transaction_test1()
   5 LANGUAGE plperl
   6 AS $$
   7 foreach my $i (0..9) {
   8     spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
   9     if ($i % 2 == 0) {
  10         spi_commit();
  11     } else {
  12         spi_rollback();
  13     }
  14 }
  15 $$;
  16
  17 CALL transaction_test1();
  18
  19 SELECT * FROM test1;
  20
  21
  22 TRUNCATE test1;
  23
  24 DO
  25 LANGUAGE plperl
  26 $$
  27 foreach my $i (0..9) {
  28     spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
  29     if ($i % 2 == 0) {
  30         spi_commit();
  31     } else {
  32         spi_rollback();
  33     }
  34 }
  35 $$;
  36
  37 SELECT * FROM test1;
  38
  39
  40 TRUNCATE test1;
  41
  42 -- not allowed in a function
  43 CREATE FUNCTION transaction_test2() RETURNS int
  44 LANGUAGE plperl
  45 AS $$
  46 foreach my $i (0..9) {
  47     spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
  48     if ($i % 2 == 0) {
  49         spi_commit();
  50     } else {
  51         spi_rollback();
  52     }
  53 }
  54 return 1;
  55 $$;
  56
  57 SELECT transaction_test2();
  58
  59 SELECT * FROM test1;
  60
  61
  62 -- also not allowed if procedure is called from a function
  63 CREATE FUNCTION transaction_test3() RETURNS int
  64 LANGUAGE plperl
  65 AS $$
  66 spi_exec_query("CALL transaction_test1()");
  67 return 1;
  68 $$;
  69
  70 SELECT transaction_test3();
  71
  72 SELECT * FROM test1;
  73
  74
  75 -- DO block inside function
  76 CREATE FUNCTION transaction_test4() RETURNS int
  77 LANGUAGE plperl
  78 AS $$
  79 spi_exec_query('DO LANGUAGE plperl $x$ spi_commit(); $x$');
  80 return 1;
  81 $$;
  82
  83 SELECT transaction_test4();
  84
  85
  86 -- commit inside cursor loop
  87 CREATE TABLE test2 (x int);
  88 INSERT INTO test2 VALUES (0), (1), (2), (3), (4);
  89
  90 TRUNCATE test1;
  91
  92 DO LANGUAGE plperl $$
  93 my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
  94 my $row;
  95 while (defined($row = spi_fetchrow($sth))) {
  96     spi_exec_query("INSERT INTO test1 (a) VALUES (" . $row->{x} . ")");
  97     spi_commit();
  98 }
  99 $$;
 100
 101 SELECT * FROM test1;
 102
 103 -- check that this doesn't leak a holdable portal
 104 SELECT * FROM pg_cursors;
 105
 106
 107 -- error in cursor loop with commit
 108 TRUNCATE test1;
 109
 110 DO LANGUAGE plperl $$
 111 my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
 112 my $row;
 113 while (defined($row = spi_fetchrow($sth))) {
 114     spi_exec_query("INSERT INTO test1 (a) VALUES (12/(" . $row->{x} . "-2))");
 115     spi_commit();
 116 }
 117 $$;
 118
 119 SELECT * FROM test1;
 120
 121 SELECT * FROM pg_cursors;
 122
 123
 124 -- rollback inside cursor loop
 125 TRUNCATE test1;
 126
 127 DO LANGUAGE plperl $$
 128 my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
 129 my $row;
 130 while (defined($row = spi_fetchrow($sth))) {
 131     spi_exec_query("INSERT INTO test1 (a) VALUES (" . $row->{x} . ")");
 132     spi_rollback();
 133 }
 134 $$;
 135
 136 SELECT * FROM test1;
 137
 138 SELECT * FROM pg_cursors;
 139
 140
 141 -- first commit then rollback inside cursor loop
 142 TRUNCATE test1;
 143
 144 DO LANGUAGE plperl $$
 145 my $sth = spi_query("SELECT * FROM test2 ORDER BY x");
 146 my $row;
 147 while (defined($row = spi_fetchrow($sth))) {
 148     spi_exec_query("INSERT INTO test1 (a) VALUES (" . $row->{x} . ")");
 149     if ($row->{x} % 2 == 0) {
 150         spi_commit();
 151     } else {
 152         spi_rollback();
 153     }
 154 }
 155 $$;
 156
 157 SELECT * FROM test1;
 158
 159 SELECT * FROM pg_cursors;
 160
 161
 162 -- check handling of an error during COMMIT
 163 CREATE TABLE testpk (id int PRIMARY KEY);
 164 CREATE TABLE testfk(f1 int REFERENCES testpk DEFERRABLE INITIALLY DEFERRED);
 165
 166 DO LANGUAGE plperl $$
 167 # this insert will fail during commit:
 168 spi_exec_query("INSERT INTO testfk VALUES (0)");
 169 spi_commit();
 170 elog(WARNING, 'should not get here');
 171 $$;
 172
 173 SELECT * FROM testpk;
 174 SELECT * FROM testfk;
 175
 176 DO LANGUAGE plperl $$
 177 # this insert will fail during commit:
 178 spi_exec_query("INSERT INTO testfk VALUES (0)");
 179 eval {
 180     spi_commit();
 181 };
 182 if ($@) {
 183     elog(INFO, $@);
 184 }
 185 # these inserts should work:
 186 spi_exec_query("INSERT INTO testpk VALUES (1)");
 187 spi_exec_query("INSERT INTO testfk VALUES (1)");
 188 $$;
 189
 190 SELECT * FROM testpk;
 191 SELECT * FROM testfk;
 192
 193
 194 DROP TABLE test1;
 195 DROP TABLE test2;