Does RAISE EXCEPTION rollback previous commands in a stored function?
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;
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
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
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
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.
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
AlexOn 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.
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
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.
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