Postgres Pro
Downloads
Home   >   mailing lists

Thread: Example in "42.8. Transaction Management" doesn't work for PostgreSQL v 12.7

Example in "42.8. Transaction Management" doesn't work for PostgreSQL v 12.7

From
Vladimir Shvartsgor
Date:
Hi,


--run before:
--create table test1(a int);

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

The CALL fails with error:
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
SQL state: 2D000

The exact version of PostgreSQL: PostgreSQL 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

Regards,
Vladimir

P.S. Generally, I need the option to execute commit/rollback inside a loop , is there a way? 

Re: Example in "42.8. Transaction Management" doesn't work for PostgreSQL v 12.7

From
Guillaume Lelarge
Date:
Hi,

Le mer. 23 juin 2021 à 10:01, Vladimir Shvartsgor <vshvartsgor@gmail.com> a écrit :
Hi,


--run before:
--create table test1(a int);

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

The CALL fails with error:
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
SQL state: 2D000

The exact version of PostgreSQL: PostgreSQL 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit


I copy and paste your example, and it works for me. And the contents of the table look good to me:

$ psql
Time: 4.458 ms
psql (12.7)
Type "help" for help.

postgres@r12 =# truncate test1;
TRUNCATE TABLE
Time: 6.504 ms
postgres@r12 =# CALL transaction_test1();
CALL
Time: 15.540 ms
postgres@r12 =# TABLE test1;
┌───┐
│ a │
├───┤
│ 0 │
│ 2 │
│ 4 │
│ 6 │
│ 8 │
└───┘
(5 rows)
Time: 0.948 ms

There must be something else on your side that makes it fail.


--
Guillaume.

Re: Example in "42.8. Transaction Management" doesn't work for PostgreSQL v 12.7

From
Vladimir Shvartsgor
Date:
Hi Guillaume,
Thanks for the quick response! You are right, the function is working fine in psql. The problem is observed in pgAdmin 4 v5.3. I have disable "Auto Commit", "Auto Rollback", "Prompt to commit/rollback active transactions?".
What setting can influence the behaviour?

Regards,
Vladimir

On Wed, Jun 23, 2021 at 2:54 PM Guillaume Lelarge <guillaume@lelarge.info> wrote:
Hi,

Le mer. 23 juin 2021 à 10:01, Vladimir Shvartsgor <vshvartsgor@gmail.com> a écrit :
Hi,


--run before:
--create table test1(a int);

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

The CALL fails with error:
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
SQL state: 2D000

The exact version of PostgreSQL: PostgreSQL 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit


I copy and paste your example, and it works for me. And the contents of the table look good to me:

$ psql
Time: 4.458 ms
psql (12.7)
Type "help" for help.

postgres@r12 =# truncate test1;
TRUNCATE TABLE
Time: 6.504 ms
postgres@r12 =# CALL transaction_test1();
CALL
Time: 15.540 ms
postgres@r12 =# TABLE test1;
┌───┐
│ a │
├───┤
│ 0 │
│ 2 │
│ 4 │
│ 6 │
│ 8 │
└───┘
(5 rows)
Time: 0.948 ms

There must be something else on your side that makes it fail.


--
Guillaume.