[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;