Autonomous Transactions for Logging v2

Here is another way that is fault tolerant to a segment host failure.

Step 1

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;

Step 2
Create /data/log_error.sh on every segment host and be sure to chmod 755 the file once created.

read in

function_name=$(echo $in | awk '{split($0,arr,",")} END{print arr[1]}')
location=$(echo $in | awk '{split($0,arr,",")} END{print arr[2]}')
error_message=$(echo $in | awk '{split($0,arr,",")} END{print arr[3]}')

psql -h mdw -c "INSERT INTO my_errors values ('$function_name', $location, '$error_message')"

Note: I’ll admit that there is probably a more efficient way to parse $in but this works.

Step 3
Create the my_errors table.

DROP TABLE IF EXISTS my_errors;

CREATE TABLE my_errors
(
function_name text,
location int,
error_message text
)
DISTRIBUTED RANDOMLY;

Step 4
Insert some data to the error_log External Web Table that isn’t influenced by the transaction.

BEGIN;

INSERT INTO error_log VALUES ('fn_build_mart', 7500, 'ERROR:  division by zero');

ROLLBACK;

Step 5
Verify that the data is in the error_log table.

SELECT * FROM my_errors;

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.