Re: autonomous transactions - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: autonomous transactions
Date
Msg-id 5d0f79c4-eacc-fa84-cd63-01a2a1395d7d@2ndquadrant.com
Whole thread Raw
In response to Re: autonomous transactions  (Jaime Casanova <jaime.casanova@2ndquadrant.com>)
List pgsql-hackers
On 8/31/16 12:38 AM, Jaime Casanova wrote:
> 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?

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



pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: OpenSSL 1.1 breaks configure and more
Next
From: Michael Paquier
Date:
Subject: Re: WIP: About CMake v2