I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed).

I'll be using ELT as opposed to ETL and managing all this via code. Bash and cron will be used for managing jobs via psql (I'm aware of pgAgent), and I wish to ensure any logging of exceptions is accurate.

I've been trying to find some "best practice" function templates for Postgres that specifically has a generic exception handling pattern for batch loading of databases, so far without much luck, as most I've come across are wanting to deal with custom exceptions, or re-throwing/raising the exception.

For my purposes I don't want the function to error back to the calling process, the logging is enough to determine states, as all dependencies will be managed through this logging workflow.

I've done the following:

What I've come up with as a basic pattern so far is:

create or replace function 
    table_schema.load_table_name(l_job_date date) 
    void as $$
    -- control variables
    l_job_code              constant varchar(50) = 'table_schema.load_table_name';
    l_job_start             timestamp;
    l_job_end               timestamp;

    -- exception management variables       
    l_exception_error_code  text;
    l_exception_message     text;
    l_exception_detail      text;
    l_exception_hint        text;
    l_exception_context     text;
    -- capture start
    l_job_start = clock_timestamp();

    -- do stuff here
    -- ...
    -- ...
    -- ...

    -- capture end
    l_job_end =  = clock_timestamp();

    -- log success
    perform fn_log_job(

-- if something "breaks" do the following
exception when others then
    get stacked diagnostics 
        l_exception_error_code  = RETURNED_SQLSTATE
        ,l_exception_message        = MESSAGE_TEXT
        ,l_exception_detail     = PG_EXCEPTION_DETAIL
        ,l_exception_hint           = PG_EXCEPTION_HINT
        ,l_exception_context        = PG_EXCEPTION_CONTEXT

    -- log exception for debugging
    perform fn_log_exception(

    -- log job failure
    perform fn_log_job(

$$ language plpgsql;

Is this all that is needed? (or have I done anything incorrectly?)

I have nothing in there yet about explicit transaction management as I'm still learning how these are handled in Postgres in general, and in function calls, any tips regarding transaction management will also be greatly appreciated!

  • This should be enough. Catching 'others' might be very expensive, though - if you can, narrow down the error types. – dezso Nov 25 '15 at 10:15
  • @dezso so where I have the comment to "do stuff here", if performing, for example, an insert I should have a begin ... end code block with specific exception handling for expected errors on an insert, as well as catching others? Can I catch a class of exceptions, e.g. Class 23 — Integrity Constraint Violation exceptions if performing an insert? – Adrian Torrie Nov 25 '15 at 23:58
  • I'd say not to open nested blocks - if you add an EXCEPTION clause into it, it basically behaves as a nested transaction, with all the overhead it implicates. I usually advocate catching the errors at the end of the main block. And yes, you can catch classes, by catching the category (in your example, integrity_constraint_violation (23000), always the first item after a class header in postgresql.org/docs/current/static/errcodes-appendix.html) – dezso Nov 26 '15 at 9:08
  • integrity_constraint_violation (23000) ... ah of course, thanks :) Thank you for the tips regarding code blocks. If you provide your comments as a direct answer e.g. "this seems enough + the following should be taken into consideration ... <include you comments>", I'll be able to accept as an answer. – Adrian Torrie Nov 29 '15 at 1:45
  • @dezso Provide your comments as an answer and I will accept. Just cleaning up some old questions of mine. – Adrian Torrie Sep 25 '17 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.