Does RAISE EXCEPTION rollback previous commands in a stored function?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Does RAISE EXCEPTION rollback previous commands in a stored function?
Date: 2016-03-01 18:41:46
Message-ID: CAADeyWgGZntMo4Hi6pzX10c8xOCvhXPSBGqG7ZQ=ZBwyBWmB0g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2016-03-01 18:48:42 Re: Does RAISE EXCEPTION rollback previous commands in a stored function?
Previous Message Tom Lane 2016-03-01 15:07:39 Re: Rules on View