Re: autonomous transactions

On 8/31/16 12:38 AM, Jaime Casanova wrote:
> 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?

That is the syntax that Oracle uses. We could make up our own.

> 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

The PL/pgSQL part doesn't work well yet. If you want to play around,
use the PL/Python integration.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services