Thread: log_statement per table

Hello,
I would like to log statements that modify a small subset of tables in a
databases.
(not all tables, because the log files become too big in that case and I
also worry about performance)
I currently use log_statement='mod' but I didn't find a way to limit
this to the set of tables I want.
What is the best way to do this?

--
David Janssens

On Mar 4, 2014, at 2:19 AM, David Janssens <david.j@almacom.co.th> wrote:

> Hello,
> I would like to log statements that modify a small subset of tables in a databases.
> (not all tables, because the log files become too big in that case and I also worry about performance)
> I currently use log_statement='mod' but I didn't find a way to limit this to the set of tables I want.
> What is the best way to do this?

You might want to look at trigger based audit logs.

Some example code, and a couple of useful packages:

http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE
https://github.com/disqus/pg_audit
http://pgfoundry.org/projects/tablelog/
http://jimmyg.org/blog/2007/audit-tables-in-postgresql-with-debian.html
http://www.varlena.com/GeneralBits/104.php

Cheers,
  Steve

David Janssens <david.j@almacom.co.th> writes:

> Hello,
> I would like to log statements that modify a small subset of tables in
> a databases.
> (not all tables, because the log files become too big in that case and
> I also worry about performance)
> I currently use log_statement='mod' but I didn't find a way to limit
> this to the set of tables I want.
> What is the best way to do this?

Below is not a perfect solution and exercise for reader to disable
logging after mods on this table.

Below is tested on 9.1 and works as per the trivial example...

But if you don't reset the log_statement setting again in an affter
statement trigger other tables modified  in same transaction are going
to log as well.

And this is wherein lies the rub, if you  had already set log_statement
to something non-default  earlier in same transaction, the trigger is
going to unconditionally reset it.

Perhaps there's a way around this too but if so, I'm not going to
divert cycles to thinking of it right now.

HTH


begin;

create table foo (
       a int
);

create function  foo()
returns trigger as
$$
begin
set local log_statement to 'all';
return null;
end
$$
language plpgsql;

create trigger foo
before insert or update or delete
on foo
execute procedure foo();

commit;

insert into foo
select 1;


>
> --
> David Janssens

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800