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.
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:
- read the manual on error trapping
- found the example provided for capturing exceptions
- used the example here for setting functions variables
- found a couple of examples, one which raises exceptions, the other that wraps another
begin ... endinside the function definition, that didn't seem to quite be what I was looking for
- and that I should prefix local variables so as not to confuse the parser
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) returns void as $$ declare -- 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; begin -- capture start l_job_start = clock_timestamp(); -- do stuff here -- ... -- ... -- ... -- capture end l_job_end = = clock_timestamp(); -- log success perform fn_log_job( l_job_code ,l_job_date ,l_job_start ,l_job_end ,'success' ) ; -- 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( l_exception_error_code ,l_exception_message ,l_exception_detail ,l_exception_hint ,l_exception_context ) ; -- log job failure perform fn_log_job( l_job_code ,l_job_date ,l_job_start ,l_job_end ,'fail' ) ; end; $$ 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!