Re: autonomous transactions

On 30 August 2016 at 20:50, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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