Re: log_statement per table

David Janssens <david(dot)j(at)almacom(dot)co(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800