CODEWITHSUNDEEP
postgresqlexceptiontransactions
Jürgen Zornig
Jürgen Zornig

Reputation: 1244

Write exception message in table

I have the following function, which is called by an Web API to import some uploaded data. There is also an upload_log Table which holds the state of the upload and is pollable by the API.

My Question is specific to Postgresql transaction management: I know, that all between the BEGIN...END Block is a transaction in PG and that it is getting rolled back automatically as soon as one statement fails.

Therefore the exception shouldn't never get written to the upload_log Table, should it?

Nevertheless, I get error messages in my upload_log and I am asking myself why this happens. 


    CREATE OR REPLACE FUNCTION finalize_upload(sync_id in varchar)
    RETURNS void AS $$
    DECLARE
       vCount numeric;
       err1 text;
       err2 text;
       err3 text;
    BEGIN
       -- Mark as Upload in Progress
       update upload_log
       set
          sync_status='1'
       where
          upload_log.gto_sync_id=finalize_upload.sync_id;

       begin
          vCount := 1;

          -- do some heavy stuff
          -- move data from import tables into production
          perform finalize_upload_specific(finalize_upload.sync_id);

       exception when others then
          GET STACKED DIAGNOSTICS err1 = MESSAGE_TEXT,
                            err2 = PG_EXCEPTION_DETAIL,
                            err3 = PG_EXCEPTION_HINT;

          -- Mark Upload as failed
          update upload_log
          set
             END_DATE=current_timestamp,
             sync_status='-1',
             err_hint=err1||' '||err2||' '||err3
          where
             upload_log.sync_id=finalize_upload.sync_id;
       end
       ;
    END
    $$ LANGUAGE plpgsql;

Upvotes: 0

Views: 37

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

Everything between the BEGIN and the associated EXCEPTION in a PL/pgSQL block will be executed in a subtransaction.

If any of the handled exceptions is thrown, the subtransaction is rolled back, but the enclosing transaction continues. The UPDATE in the exception handler will be committed because it is already outside the subtransaction that gets rolled back.

To illustrate it with pseude-code and SQL savepoints:

START TRANSACTION;
UPDATE upload_log SET sync_status='1' ...;
SAVEPOINT a;
SELECT finalize_upload_specific($1);
-- if an exception was thrown:
ROLLBACK TO SAVEPOINT a;
RELEASE SAVEPOINT a;
UPDATE upload_log SET sync_status='-1' ...;
COMMIT;
-- else
ROLLBACK;

Upvotes: 1

Related Questions