Re: autonomous transactions

On 30 August 2016 at 20:50, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
> - Patches to PL/pgSQL to implement Oracle-style autonomous transaction
> blocks:
>
> AS $$
> DECLARE
> PRAGMA AUTONOMOUS_TRANSACTION;
> BEGIN
> FOR i IN 0..9 LOOP
> START TRANSACTION;
> INSERT INTO test1 VALUES (i);
> IF i % 2 = 0 THEN
> COMMIT;
> ELSE
> ROLLBACK;
> END IF;
> END LOOP;
>
> RETURN 42;
> END;
> $$;
>

this is the syntax it will use?
i just compiled this in head and created a function based on this one.
The main difference is that the column in test1 it's a pk so i used
INSERT ON CONFLICT DO NOTHING

and i'm getting this error

postgres=# select foo();
LOG: namespace item variable itemno 1, name val
CONTEXT: PL/pgSQL function foo() line 7 at SQL statement
STATEMENT: select foo();
ERROR: null value in column "i" violates not-null constraint
DETAIL: Failing row contains (null).
STATEMENT: INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING
ERROR: null value in column "i" violates not-null constraint
DETAIL: Failing row contains (null).
CONTEXT: PL/pgSQL function foo() line 7 at SQL statement
STATEMENT: select foo();
ERROR: null value in column "i" violates not-null constraint
DETAIL: Failing row contains (null).
CONTEXT: PL/pgSQL function foo() line 7 at SQL statement

this happens even everytime i use the PRAGMA even if no START
TRANSACTION, COMMIT or ROLLBACK are used

--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services