Thread: exclude table from transactions

Dear list,

I'd like to ask, whether there is a way to exclude a table from the
regular transaction rollback.
Background:
I want to log all exceptions caused by my plpgsql-functions in a certain
log-table. To do this, instead of raising an exception directly, i call
a function that writes a record into that log-table and afterwards
raises the exception. But of course, the exception rolls back the INSERT
to the log-table as well. The only solution I came up with till now, is
to put the log data into the exception's error message, parse the
pgsql-logging-file in /var/log with a cron-script and fix the log-table
with that. It works, but it's definitely not a clean solution. So what
I'm looking for, is a table that behaves like a temporary table (e.g.
excluded from WAL and transaction logic), but with the lifetime of a
normal table.
Anybody got an idea?
Thanks in advance, wz

On Nov 24, 2009, at 7:25 AM, Kurt wrote:
> I'd like to ask, whether there is a way to exclude a table from the
> regular transaction rollback.

Nope, there is not.

> The only solution I came up with till now, is
> to put the log data into the exception's error message, parse the
> pgsql-logging-file in /var/log with a cron-script and fix the log-
> table
> with that.

That's probably the best solution if you want the error message to end
up in a table.

> So what
> I'm looking for, is a table that behaves like a temporary table (e.g.
> excluded from WAL and transaction logic), but with the lifetime of a
> normal table.

Just to be clear, temporary tables partake of the same logic as
regular tables; for example, even a TEMPORARY ON COMMIT DROP table can
be used with savepoints within a transaction.

--
-- Christophe Pettus
    xof@thebuild.com

Kurt <wazkelzu@gmx.net> writes:
> I'd like to ask, whether there is a way to exclude a table from the
> regular transaction rollback.

No.

> I want to log all exceptions caused by my plpgsql-functions in a certain
> log-table.

You could use dblink to connect to another session that has its own
transaction boundaries, and send commands to that session to insert
messages into your log table.

            regards, tom lane