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

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(at)lelarge(dot)info>
wrote:

> 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.
>