Skip site navigation (1) Skip section navigation (2)

autonomous transactions

From: "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: autonomous transactions
Date: 2008-01-22 16:02:44
Message-ID: 1A6E6D554222284AB25ABE3229A9276271549A@nrtexcus702.int.asurion.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I really needed this functionality in PostgreSQL.  A common use for
autonomous transactions is error logging.  I want to log sqlerrm in a
function and raise an exception so the calling application knows there is an
error and I have it logged to a table.  

 

I figured out a way to "hack" an autonomous transaction by using a dblink in
a function and here is a simple example:

 

create or replace function fn_log_error(p_function varchar, p_location int,
p_error varchar) returns void as

$$

declare

  v_sql varchar;

  v_return varchar;

  v_error varchar;

begin

  perform dblink_connect('connection_name', 'dbname=...');

  

  v_sql := 'insert into error_log (function_name, location, error_message,
error_time) values (''' || p_function_name || ''', ' || 

           p_location || ', ''' || p_error || ''', clock_timestamp())';

  

  select * from dblink_exec('connection_name', v_sql, false) into v_return;

 

  --get the error message

  select * from dblink_error_message('connection_name') into v_error;

 

  if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0
then

    raise exception '%', v_error;

  end if;

 

  perform dblink_disconnect('connection_name');

 

exception

  when others then

    perform dblink_disconnect('connection_name');

    raise exception '(%)', sqlerrm;

end;

$$

language 'plpgsql' security definer;

 

I thought I would share and it works rather well.  Maybe someone could
enhance this concept to include it with the core database to provide
autonomous transactions.

 

 

Jon

Responses

pgsql-hackers by date

Next:From: Patrick McPheeDate: 2008-01-22 17:00:42
Subject: Re: Password policy
Previous:From: Alvaro HerreraDate: 2008-01-22 15:42:39
Subject: Re: Thoughts about bug #3883

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group