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;
