Monthly Archives: May 2013

Autonomous Transactions for Logging

Updated July 17, 2013
***************
1. External Web Table to read data should run on HOST, not MASTER.
2. The /data/log_error.sh file needs to be created on every segment host.
3. Need to chmod 755 the .sh file on the segment hosts.
4. Need to touch /data/error.log on all of the segment hosts because the file needs to exist to prevent an error reading from an empty table.
***************

An Autonomous Transaction is not a feature of Greenplum database but it can be achieved with some of the features unique to Greenplum. If you aren’t familiar with an Autonomous Transaction, it is basically a new transaction that is created within a transaction but it isn’t tied to your first transaction.

What is the point? Well, in database programming, you may run into an EXCEPTION that you want to RAISE to the calling program but you also want to log the error. Raising an ERROR in Greenplum rolls back your transaction so the INSERT to a log table also gets rolled back.

Here is a way to create a log table that enables you to RAISE an ERROR in your code but the INSERT doesn’t get rolled back!

First create the error_log table:

DROP EXTERNAL TABLE IF EXISTS error_log;

CREATE WRITABLE EXTERNAL WEB TABLE error_log
(
function_name text,
location int,
error_message text
)
EXECUTE '/data/log_error.sh'
FORMAT 'CSV' DISTRIBUTED RANDOMLY;

Next create the /data/log_error.sh file:

read in
echo $in >> /data/error.log

Now you can read this error.log file if you want or you can create another EXTERNAL TABLE to read the data like this:

DROP EXTERNAL TABLE IF EXISTS my_errors;

CREATE EXTERNAL WEB TABLE my_errors
(
function_name text,
location int,
error_message text
)
EXECUTE 'cat /data/error.log' ON /*MASTER*/ HOST
FORMAT 'CSV';

So now to test this out:

gpdb=# BEGIN;
BEGIN
gpdb=# INSERT INTO error_log VALUES ('fn_build_mart', 7500, 'ERROR:  division by zero');
INSERT 0 1
gpdb=# ROLLBACK;
ROLLBACK
gpdb=# SELECT * FROM my_errors;
 function_name | location |      error_message      
---------------+----------+-------------------------
 fn_build_mart |     7500 | ERROR: division by zero
(1 row)

So even though I rolled back my transaction, the INSERT into the EXTERNAL WRITABLE WEB TABLE in Greenplum didn’t get rolled back and thus, you created an Autonomous Transaction in Greenplum.