Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does RAISE EXCEPTION rollback previous commands in a stored function?

Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Alexander Farber
Date:
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;



Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Andreas Kretschmer
Date:

> 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.

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Pavel Stehule
Date:
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
  

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Albe Laurenz
Date:
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

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Alexander Farber
Date:
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:
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.

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Pavel Stehule
Date:
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

Regards

Pavel

 

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.


Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Albe Laurenz
Date:
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

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Alexander Farber
Date:
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:
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.

Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

From
Albe Laurenz
Date:
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