Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Albe Laurenz]

Alexander Farber wrote:
> On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz  wrote:
>> You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK
>> inside a function.  A function always runs within one transaction.
>> 
>> Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
>> block in PL/pgSQL, so you could write:
>> 
>> DECLARE FUNCTION  AS
>> $$BEGIN
>>/* UPDATE 1 */
>>UPDATE ...;
>>BEGIN  /* sets a savepoint */
>>   /* UPDATE 2, can cause an error */
>>   UPDATE ...;
>>EXCEPTION
>>   /* rollback to savepoint, ignore error */
>>   WHEN OTHERS THEN NULL;
>>END;
>> END;$$;
>> 
>> Even if UPDATE 2 throws an error, UPDATE 1 will be committed.

> Thank you, this is very helpful, just 1 little question:
> 
> 
> Why do you write just EXCEPTION?
> 
> 
> Shouldn't it be RAISE EXCEPTION?

That's something entirely different, see
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

The above construct *catches* the exception, which might be
raised by the UPDATE statement.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Alexander Farber]

Thank you, this is very helpful, just 1 little question:

Why do you write just EXCEPTION?

Shouldn't it be RAISE EXCEPTION?

Regards
Alex


On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz 
wrote:

> Alexander Farber wrote:
> > how to set such a savepoint inside of a stored function?
> >
> > Can I call "START TRANSACTION", and then at some point later in the same
> stored function call RAISE
> > EXCEPTION?
>
> I realize that what I wrote must be confusing.
>
> You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK
> inside a function.  A function always runs within one transaction.
>
> Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
> block in PL/pgSQL, so you could write:
>
> DECLARE FUNCTION  AS
> $$BEGIN
>/* UPDATE 1 */
>UPDATE ...;
>BEGIN  /* sets a savepoint */
>   /* UPDATE 2, can cause an error */
>   UPDATE ...;
>EXCEPTION
>   /* rollback to savepoint, ignore error */
>   WHEN OTHERS THEN NULL;
>END;
> END;$$;
>
> Even if UPDATE 2 throws an error, UPDATE 1 will be committed.
>
>

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Albe Laurenz]

Alexander Farber wrote:
> how to set such a savepoint inside of a stored function?
> 
> Can I call "START TRANSACTION", and then at some point later in the same 
> stored function call RAISE
> EXCEPTION?

I realize that what I wrote must be confusing.

You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK
inside a function.  A function always runs within one transaction.

Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
block in PL/pgSQL, so you could write:

DECLARE FUNCTION  AS
$$BEGIN
   /* UPDATE 1 */
   UPDATE ...;
   BEGIN  /* sets a savepoint */
  /* UPDATE 2, can cause an error */
  UPDATE ...;
   EXCEPTION
  /* rollback to savepoint, ignore error */
  WHEN OTHERS THEN NULL;
   END;
END;$$;

Even if UPDATE 2 throws an error, UPDATE 1 will be committed.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Pavel Stehule]

Hi

2016-03-02 10:47 GMT+01:00 Alexander Farber :

> Hi Laurenz,
>
> how to set such a savepoint inside of a stored function?
>
> Can I call "START TRANSACTION", and then at some point later in the same
> stored function call RAISE EXCEPTION?
>

You cannot to do it explicitly. But, when you handle any exception in bloc,
then subtransaction is used implicitly

BEGIN ~ starts transaction
  ...
  ...
EXCEPTION  WHEN ... ~ rollback transaction

END -- commit transaction when no exception

http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regards

Pavel



>
> Regargs
> Alex
>
> On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz 
> wrote:
>
>> Andreas Kretschmer wrote:
>> >> Alexander Farber  hat am 1. März 2016 um
>> 19:41
>> >> geschrieben:
>>
>
>
>> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
>> >> commands in a stored function?
>> >
>> > Yes.
>>
>> That is, unless you set a savepoint to which you can rollback.
>>
>
>

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Alexander Farber]

Hi Laurenz,

how to set such a savepoint inside of a stored function?

Can I call "START TRANSACTION", and then at some point later in the same
stored function call RAISE EXCEPTION?

Regargs
Alex

On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz 
wrote:

> Andreas Kretschmer wrote:
> >> Alexander Farber  hat am 1. März 2016 um
> 19:41
> >> geschrieben:
>


> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
> >> commands in a stored function?
> >
> > Yes.
>
> That is, unless you set a savepoint to which you can rollback.
>

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Albe Laurenz]

Andreas Kretschmer wrote:
>> Alexander Farber  hat am 1. März 2016 um 19:41
>> geschrieben:
>>
>>
>> Good evening,
>>
>> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
>> commands in a stored function?
> 
> Yes.

That is, unless you set a savepoint to which you can rollback.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Pavel Stehule]

Hi

2016-03-01 19:41 GMT+01:00 Alexander Farber :

> Good evening,
>
> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
> commands in a stored function?
>
> I have a stored function (the code is at the bottom), which takes a JSON
> array of objects as arguments.
>
> First it prepares some data and then loops through the JSON array and
> upserts the objects into a table.
>
> However if any of the objects fails an authenticity check (using md5 +
> some secret string) - I would like to rollback everything.
>
> Since I can not use START TRANSACTION in a stored function, I wonder if
> another loop should be added at the very beginning - or if I can just use
> the one I already have at the end.
>

transaction is started implicitly when you start SQL statement.

Pavel

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Andreas Kretschmer]

> Alexander Farber  hat am 1. März 2016 um 19:41
> geschrieben:
> 
> 
> Good evening,
> 
> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
> commands in a stored function?


Yes.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Alexander Farber]

Good evening,

in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
commands in a stored function?

I have a stored function (the code is at the bottom), which takes a JSON
array of objects as arguments.

First it prepares some data and then loops through the JSON array and
upserts the objects into a table.

However if any of the objects fails an authenticity check (using md5 + some
secret string) - I would like to rollback everything.

Since I can not use START TRANSACTION in a stored function, I wonder if
another loop should be added at the very beginning - or if I can just use
the one I already have at the end.

Thank you
Alex

CREATE OR REPLACE FUNCTION words_merge_users(
IN in_users jsonb,
IN in_ip inet,
OUT out_uid integer)
RETURNS integer AS
$func$
DECLARE
j jsonb;
uids integer[];
new_vip timestamptz;
new_grand timestamptz;
new_banned timestamptz;
new_reason varchar(255);
BEGIN
uids := (
SELECT ARRAY_AGG(uid)
FROM words_social
JOIN JSONB_ARRAY_ELEMENTS(in_users) x
ON sid = x->>'sid'
AND social = (x->>'social')::int
);

RAISE NOTICE 'uids = %', uids;

SELECT
MIN(uid),
CURRENT_TIMESTAMP + SUM(vip_until - CURRENT_TIMESTAMP),
CURRENT_TIMESTAMP + SUM(grand_until - CURRENT_TIMESTAMP),
MAX(banned_until)
INTO
out_uid,
new_vip,
new_grand,
new_banned
FROM words_users
WHERE uid = ANY(uids);

RAISE NOTICE 'out_uid = %', out_uid;
RAISE NOTICE 'new_vip = %', new_vip;
RAISE NOTICE 'new_grand = %', new_grand;
RAISE NOTICE 'new_banned = %', new_banned;

IF out_uid IS NULL THEN
INSERT INTO words_users (
created,
visited,
ip,
medals,
green,
red
) VALUES (
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
in_ip,
0,
0,
0
) RETURNING uid INTO out_uid;
ELSE
SELECT banned_reason
INTO new_reason
FROM words_users
WHERE banned_until = new_banned
LIMIT 1;

RAISE NOTICE 'new_reason = %', new_reason;

UPDATE words_social
SET uid = out_uid
WHERE uid = ANY(uids);

DELETE FROM words_users
WHERE uid <> out_uid
AND uid = ANY(uids);

UPDATE words_users SET
visited = CURRENT_TIMESTAMP,
ip = in_ip,
vip_until = new_vip,
grand_until = new_grand,
banned_until = new_banned,
banned_reason = new_reason
WHERE uid = out_uid;

END IF;

FOR j IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP

  -- XXX will RAISE EXCEPTION here reliably rollback
everything? XXX

  UPDATE words_social SET
social = (j->>'social')::int,
female = (j->>'female')::int,
given  = j->>'given',
family = j->>'family',
photo  = j->>'photo',
place  = j->>'place',
stamp  = (j->>'stamp')::int,
uid= out_uid

WHERE sid = j->>'sid' AND social = (j->>'social')::int;

IF NOT FOUND THEN
INSERT INTO words_social (
sid,
social,
female,
given,
family,
photo,
place,
stamp,
uid
) VALUES (
j->>'sid',
(j->>'social')::int,
(j->>'female')::int,
j->>'given',
j->>'family',
j->>'photo',
j->>'place',
(j->>'stamp')::int,
out_uid
);
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;