Write exception message in table

Question

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.

Is it because of the nested function call PERFORM...?
Is it because of the EXCEPTION WHEN OTHERS... that enables to write something in a table afterwards?
Do I have to explicitely call ROLLBACK in the EXCEPTION Block?
Do I have to write my upload_log with DBLink as an "Autonomous Transaction"


    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;

Laurenz Albe · Accepted Answer · 2019-06-18 09:45:12Z

1

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;

answered Jun 18, 2019 at 9:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write exception message in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related