Does RAISE EXCEPTION rollback previous commands in a stored function?

Started by Alexander Farberabout 10 years ago9 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

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;

#2Andreas Kretschmer
andreas@a-kretschmer.de
In reply to: Alexander Farber (#1)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber <alexander.farber@gmail.com> 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

#3Pavel Stehule
pavel.stehule@gmail.com
In reply to: Alexander Farber (#1)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Hi

2016-03-01 19:41 GMT+01:00 Alexander Farber <alexander.farber@gmail.com>:

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

#4Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Andreas Kretschmer (#2)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Andreas Kretschmer wrote:

Alexander Farber <alexander.farber@gmail.com> 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

#5Alexander Farber
alexander.farber@gmail.com
In reply to: Laurenz Albe (#4)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

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 <laurenz.albe@wien.gv.at>
wrote:

Show quoted text

Andreas Kretschmer wrote:

Alexander Farber <alexander.farber@gmail.com> 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.

#6Pavel Stehule
pavel.stehule@gmail.com
In reply to: Alexander Farber (#5)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Hi

2016-03-02 10:47 GMT+01:00 Alexander Farber <alexander.farber@gmail.com>:

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

Show quoted text

Regargs
Alex

On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:

Andreas Kretschmer wrote:

Alexander Farber <alexander.farber@gmail.com> 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.

#7Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Alexander Farber (#5)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

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

#8Alexander Farber
alexander.farber@gmail.com
In reply to: Laurenz Albe (#7)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

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 <laurenz.albe@wien.gv.at>
wrote:

Show quoted text

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.

#9Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Alexander Farber (#8)
Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote:

On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz <laurenz.albe@wien.gv.at> 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