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.

5 thoughts on “Autonomous Transactions for Logging

  1. Jon

    In what sort of environment did you do this test?

    On a DCA I was only able to get this to work with these table definitions. Unfortunately, when I query the my_errors table I get an error returned for each segment instead of a single row per error.

    Test_db=# \d my_errors
    External table “public.my_errors”
    Column | Type | Modifiers
    —————+———+———–
    function_name | text |
    location | integer |
    error_message | text |
    Type: readable
    Encoding: UTF8
    Format type: csv
    Format options: delimiter ‘,’ null ” escape ‘”‘ quote ‘”‘
    Command: cat /data1/error.log
    Execute on: all segments

    Test_db=# \d error_log
    External table “public.error_log”
    Column | Type | Modifiers
    —————+———+———–
    function_name | text |
    location | integer |
    error_message | text |
    Type: writable
    Encoding: UTF8
    Format type: csv
    Format options: delimiter ‘,’ null ” escape ‘”‘ quote ‘”‘
    Command: /data1/log_error.sh
    Execute on: all segments

    The error only logs to one file/segment per error so I’m confused as to the query returning back 6 entries per error.

    [sdw4] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw5] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw6] -rw-rw-r– 1 gpadmin gpadmin 43 Jul 16 14:23 /data1/error.log
    [sdw7] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw1] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw2] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw3] -rw-rw-r– 1 gpadmin gpadmin 0 Jul 16 14:22 /data1/error.log
    [sdw8] -rw-rw-r– 1 gpadmin gpadmin 44 Jul 16 14:27 /data1/error.log

    Test_db=# select * from my_errors ;
    function_name | location | error_message
    —————-+———-+————————-
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart2 | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    fn_build_mart | 7500 | ERROR: division by zero
    (12 rows)

    Reply
  2. Jon Post author

    Thanks for finding the errors in this. I added additional information to make this work. Let me know if this doesn’t work for you now.

    Reply
  3. Jon

    All good now. Thanks!

    Test_db=# \i ./my_errors.sql
    DROP EXTERNAL TABLE
    CREATE EXTERNAL TABLE
    Test_db=# \d my_errors
    External table “public.my_errors”
    Column | Type | Modifiers
    —————+———+———–
    function_name | text |
    location | integer |
    error_message | text |
    Type: readable
    Encoding: UTF8
    Format type: csv
    Format options: delimiter ‘,’ null ” escape ‘”‘ quote ‘”‘
    Command: cat /data1/error.log
    Execute on: one segment per host

    Test_db=# begin; BEGIN
    Test_db=# INSERT INTO error_log VALUES (‘fn_build_mart5’, 7500, ‘ERROR: division by zero’);
    INSERT 0 1
    Test_db=# ROLLBACK;
    ROLLBACK
    Test_db=# SELECT * FROM my_errors;
    function_name | location | error_message
    —————-+———-+————————-
    fn_build_mart5 | 7500 | ERROR: division by zero

    Reply
  4. Jon

    Jon, is there an “in memory” option/solution to this problem? Suppose one or more segment hosts goes down – would this solution would be impacted due to the lack of writing capabilities to the log file on a downed system or is this handled gracefully in that, the system recognizes that the segment(s) is down/unavailable and won’t bother attempting to write to it because it doesn’t see it?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.