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

Hi,

Le mer. 23 juin 2021 à 10:01, Vladimir Shvartsgor <vshvartsgor(at)gmail(dot)com> a
écrit :

> Hi,
>
> When the first example of the
> https://www.postgresql.org/docs/12/plpgsql-transactions.html:
>
> *--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(at)r12 =# truncate test1;
TRUNCATE TABLE
Time: 6.504 ms
postgres(at)r12 =# CALL transaction_test1();
CALL
Time: 15.540 ms
postgres(at)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.