plpgsql_txn

# LogicTest: !local-mixed-23.1

statement ok
CREATE TABLE t (x INT);

# Variables are preserved across both COMMIT and ROLLBACK.
statement ok
CREATE PROCEDURE p(a INT, b INT) LANGUAGE PLpgSQL AS $$
  DECLARE
    x INT := 0;
    y INT := 1;
  BEGIN
    RAISE NOTICE 'a: % b: % x: % y: %', a, b, x, y;
    a := a + 1;
    COMMIT;
    RAISE NOTICE 'a: % b: % x: % y: %', a, b, x, y;
    b := b + 1;
    COMMIT;
    RAISE NOTICE 'a: % b: % x: % y: %', a, b, x, y;
    x := x + 1;
    ROLLBACK;
    RAISE NOTICE 'a: % b: % x: % y: %', a, b, x, y;
    y := y + 1;
    ROLLBACK;
    RAISE NOTICE 'a: % b: % x: % y: %', a, b, x, y;
  END;
$$;

query T noticetrace
CALL p(100, 101);
----
NOTICE: a: 100 b: 101 x: 0 y: 1
NOTICE: a: 101 b: 101 x: 0 y: 1
NOTICE: a: 101 b: 102 x: 0 y: 1
NOTICE: a: 101 b: 102 x: 1 y: 1
NOTICE: a: 101 b: 102 x: 1 y: 2

# Verify that mutations are correctly committed or reverted.
statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'INSERT (1)';
    INSERT INTO t VALUES (1);
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'COMMIT;';
    COMMIT;
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'INSERT (2)';
    INSERT INTO t VALUES (2);
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'ROLLBACK;';
    ROLLBACK;
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'DELETE (1)';
    DELETE FROM t WHERE x = 1;
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
    RAISE NOTICE 'ROLLBACK;';
    ROLLBACK;
    RAISE NOTICE 'max: %', (SELECT max(x) FROM t);
  END;
$$;

query T noticetrace
CALL p();
----
NOTICE: max: <NULL>
NOTICE: INSERT (1)
NOTICE: max: 1
NOTICE: COMMIT;
NOTICE: max: 1
NOTICE: INSERT (2)
NOTICE: max: 2
NOTICE: ROLLBACK;
NOTICE: max: 1
NOTICE: DELETE (1)
NOTICE: max: <NULL>
NOTICE: ROLLBACK;
NOTICE: max: 1

query I rowsort
SELECT * FROM t;
----
1

# Verify that the transaction timestamp is advanced.
statement ok
CREATE TABLE txn_timestamps (ts TIMESTAMP);

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    INSERT INTO txn_timestamps VALUES (now());
    COMMIT;
    INSERT INTO txn_timestamps VALUES (now());
    ROLLBACK;
    INSERT INTO txn_timestamps VALUES (now());
  END;
$$;

statement ok
CALL p();

query I
SELECT count(DISTINCT ts) FROM txn_timestamps;
----
2

# There should be no blocking when updating and locking the same row in
# different transactions, since they never overlap.
statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    SELECT * FROM t FOR UPDATE;
    COMMIT;
    UPDATE t SET x = x + 1;
    ROLLBACK;
    UPDATE t SET x = x + 1;
  END;
$$;
CALL p();

query I
SELECT * FROM t;
----
2

subtest branches_loops

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  DECLARE
    i INT;
  BEGIN
    IF i IS NULL THEN
      i := 0;
      ROLLBACK;
    END IF;
    WHILE i < 5 LOOP
      IF i = 2 THEN
        ROLLBACK;
      END IF;
      i := i + 1;
      COMMIT;
      IF i = a THEN
        EXIT;
      END IF;
    END LOOP;
    RAISE NOTICE '%', i;
  END;
$$;

query T noticetrace
CALL p(2);
----
NOTICE: 2

query T noticetrace
CALL p(10);
----
NOTICE: 5

subtest cursors

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  DECLARE
    foo CURSOR FOR SELECT 1;
    foundException BOOL := false;
  BEGIN
    IF a = 0 THEN
      OPEN foo;
      COMMIT;
      BEGIN
        CLOSE foo;
      EXCEPTION WHEN SQLSTATE '34000' THEN
        foundException := true;
      END;
    ELSIF a = 1 THEN
      OPEN foo;
      ROLLBACK;
      BEGIN
        CLOSE foo;
      EXCEPTION WHEN SQLSTATE '34000' THEN
        foundException := true;
      END;
    ELSIF a = 3 THEN
      COMMIT;
      OPEN foo;
      BEGIN
        CLOSE foo;
      EXCEPTION WHEN SQLSTATE '34000' THEN
        foundException := true;
      END;
    ELSE
      ROLLBACK;
      OPEN foo;
      BEGIN
        CLOSE foo;
      EXCEPTION WHEN SQLSTATE '34000' THEN
        foundException := true;
      END;
    END IF;
    RAISE NOTICE 'found exception: %', foundException;
  END;
$$;

# Closing the transaction causes the cursor to be closed as well.
query T noticetrace
CALL p(0);
----
NOTICE: found exception: true

# Closing the transaction causes the cursor to be closed as well.
query T noticetrace
CALL p(1);
----
NOTICE: found exception: true

# The cursor is opened after the new transaction starts, so no exception.
query T noticetrace
CALL p(2);
----
NOTICE: found exception: false

# The cursor is opened after the new transaction starts, so no exception.
query T noticetrace
CALL p(3);
----
NOTICE: found exception: false

statement ok
SET close_cursors_at_commit = false;

# The cursor stays open after commit due to the setting.
query T noticetrace
CALL p(0);
----
NOTICE: found exception: false

# The cursor is still closed, because the transaction was aborted.
query T noticetrace
CALL p(1);
----
NOTICE: found exception: true

statement ok
RESET close_cursors_at_commit;
CLOSE ALL;

subtest blocks

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    INSERT INTO t VALUES (3);
    ROLLBACK;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    IF a > 0 THEN
      BEGIN
        RAISE NOTICE '%', (SELECT max(x) FROM t);
        INSERT INTO t VALUES (4);
        COMMIT;
        RAISE NOTICE '%', (SELECT max(x) FROM t);
      END;
    END IF;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    INSERT INTO t VALUES (5);
    COMMIT;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    IF a > 0 THEN
      BEGIN
        RAISE NOTICE '%', (SELECT max(x) FROM t);
        INSERT INTO t VALUES (6);
        ROLLBACK;
        RAISE NOTICE '%', (SELECT max(x) FROM t);
      END;
    END IF;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
  END;
$$;

query T noticetrace
CALL p(0);
----
NOTICE: 2
NOTICE: 2
NOTICE: 2
NOTICE: 5
NOTICE: 5

statement ok
DELETE FROM t WHERE x > 2;

query T noticetrace
CALL p(1);
----
NOTICE: 2
NOTICE: 2
NOTICE: 2
NOTICE: 4
NOTICE: 4
NOTICE: 5
NOTICE: 5
NOTICE: 5
NOTICE: 5

query I rowsort
SELECT * FROM t;
----
2
4
5

subtest exceptions

statement ok
DELETE FROM t WHERE True;
INSERT INTO t VALUES (1);

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    INSERT INTO t VALUES (2);
    COMMIT;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    BEGIN
      IF (a // 0) > 0 THEN
        RAISE NOTICE 'foo';
      END IF;
    EXCEPTION WHEN division_by_zero THEN
      RAISE NOTICE 'bar';
    END;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
    INSERT INTO t VALUES (3);
    ROLLBACK;
    RAISE NOTICE '%', (SELECT max(x) FROM t);
  END;
$$;

query T noticetrace
CALL p(1);
----
NOTICE: 1
NOTICE: 2
NOTICE: bar
NOTICE: 2
NOTICE: 2

query I rowsort
SELECT * FROM t;
----
1
2

statement ok
DROP PROCEDURE p;

# COMMIT is not valid in a block with an exception handler.
statement error pgcode 2D000 pq: invalid transaction termination
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    IF a > 0 THEN
      COMMIT;
    END IF;
  EXCEPTION WHEN division_by_zero THEN
    RAISE NOTICE 'foo';
  END;
$$;

statement error pgcode 2D000 pq: invalid transaction termination
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    IF a > 0 THEN
      ROLLBACK;
    END IF;
  EXCEPTION WHEN division_by_zero THEN
    RAISE NOTICE 'foo';
  END;
$$;

# The cases above, with a nested block.
statement error pgcode 2D000 pq: invalid transaction termination
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    BEGIN
      IF a > 0 THEN
        COMMIT;
      END IF;
    EXCEPTION WHEN division_by_zero THEN
      RAISE NOTICE 'foo';
    END;
  END;
$$;

statement error pgcode 2D000 pq: invalid transaction termination
CREATE PROCEDURE p(a INT) LANGUAGE PLpgSQL AS $$
  BEGIN
    BEGIN
      IF a > 0 THEN
        ROLLBACK;
      END IF;
    EXCEPTION WHEN division_by_zero THEN
      RAISE NOTICE 'foo';
    END;
  END;
$$;

subtest postgres_example

# This case is adopted from the postgres "Transaction Management" docs page.
statement ok
CREATE TABLE test1 (a INT);

statement ok
CREATE PROCEDURE transaction_test1() LANGUAGE plpgsql AS $$
  DECLARE
    i INT := 0;
  BEGIN
    WHILE i <= 9 LOOP
      RAISE NOTICE 'i = %', i;
      INSERT INTO test1 (a) VALUES (i);
      IF i % 2 = 0 THEN
        RAISE NOTICE 'COMMIT;';
        COMMIT;
      ELSE
        RAISE NOTICE 'ROLLBACK;';
        ROLLBACK;
      END IF;
      i := i + 1;
    END LOOP;
  END;
$$;

query T noticetrace
CALL transaction_test1();
----
NOTICE: i = 0
NOTICE: COMMIT;
NOTICE: i = 1
NOTICE: ROLLBACK;
NOTICE: i = 2
NOTICE: COMMIT;
NOTICE: i = 3
NOTICE: ROLLBACK;
NOTICE: i = 4
NOTICE: COMMIT;
NOTICE: i = 5
NOTICE: ROLLBACK;
NOTICE: i = 6
NOTICE: COMMIT;
NOTICE: i = 7
NOTICE: ROLLBACK;
NOTICE: i = 8
NOTICE: COMMIT;
NOTICE: i = 9
NOTICE: ROLLBACK;

query I rowsort
SELECT * FROM test1;
----
0
2
4
6
8

subtest function

statement error pgcode 2D000 pq: invalid transaction termination
CREATE FUNCTION f() RETURNS INT LANGUAGE PLpgSQL AS $$
  BEGIN
    COMMIT;
    RETURN 1;
  END;
$$;

statement error pgcode 2D000 pq: invalid transaction termination
CREATE FUNCTION f() RETURNS INT LANGUAGE PLpgSQL AS $$
  BEGIN
    ROLLBACK;
    RETURN 1;
  END;
$$;

subtest explicit_txn

statement ok
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    COMMIT;
  END;
$$;

statement ok
BEGIN;

statement error pgcode 2D000 pq: invalid transaction termination
CALL p();

statement ok
ABORT;

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    ROLLBACK;
  END;
$$;

statement ok
BEGIN;

statement error pgcode 2D000 pq: invalid transaction termination
CALL p();

statement ok
ABORT;

subtest serializable_retry

statement ok
DELETE FROM t WHERE x <> 1;

statement ok
CREATE SEQUENCE s1;
CREATE SEQUENCE s2;

# TODO(#119632): we can't use RAISE statements, since they cause results to be
# flushed to the client, which prevents retries.
statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    INSERT INTO t VALUES (2);
    COMMIT;
    INSERT INTO t VALUES (3);
    SELECT IF(nextval('s1')<3, crdb_internal.force_retry('1h':::INTERVAL), 0);
    ROLLBACK;
    INSERT INTO t VALUES (4);
    SELECT IF(nextval('s2')<3, crdb_internal.force_retry('1h':::INTERVAL), 0);
    COMMIT;
  END
$$;

statement ok
CALL p();

query I rowsort
SELECT * FROM t;
----
1
2
4

# Verify that the automatic retries happened.
query II
SELECT currval('s1'), currval('s2');
----
3  3

subtest read_committed_retry

statement ok
DELETE FROM t WHERE x <> 1;

statement ok
DROP PROCEDURE p;
DROP SEQUENCE s1;
DROP SEQUENCE s2;
CREATE SEQUENCE s1;
CREATE SEQUENCE s2;

# TODO(#119632): we can't use RAISE statements, since they cause results to be
# flushed to the client, which prevents retries.
statement ok
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    INSERT INTO t VALUES (2);
    COMMIT;
    INSERT INTO t VALUES (3);
    SELECT IF(nextval('s1')<3, crdb_internal.force_retry('1h':::INTERVAL), 0);
    ROLLBACK;
    INSERT INTO t VALUES (4);
    SELECT IF(nextval('s2')<3, crdb_internal.force_retry('1h':::INTERVAL), 0);
    COMMIT;
  END
$$;

statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

statement ok
CALL p();

query I rowsort
SELECT * FROM t;
----
1
2
4

# Verify that the automatic retries happened.
query II
SELECT currval('s1'), currval('s2');
----
3  3

statement ok
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

subtest err

statement ok
DROP PROCEDURE p;

statement error pgcode 0A000 pq: unimplemented: COMMIT or ROLLBACK with AND CHAIN syntax is not yet implemented
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    COMMIT AND CHAIN;
  END
$$;

statement error pgcode 0A000 pq: unimplemented: COMMIT or ROLLBACK with AND CHAIN syntax is not yet implemented
CREATE PROCEDURE p() LANGUAGE PLpgSQL AS $$
  BEGIN
    ROLLBACK AND CHAIN;
  END
$$;

subtest end