Thread: debugging triggers - get original statement?
Hi I have a situation where a summary table is updated via triggers on multiple tables. E.g. an insert is done on table1; a trigger (trigger_func_1) then causes an update on table2 , which in turn update table3 using another trigger (trigger_func_2). I'm encountering invalid data on table3 and I suspect a bug in trigger_func_2. I now want to add debugging to trigger_func_2 where I log the original statement (transaction?). I.e. I'm interested in something like "INSERT INTO table1.... " Anybody have any ideas on how I can get this original statement ? Thanks GMB -- View this message in context: http://postgresql.1045698.n5.nabble.com/debugging-triggers-get-original-statement-tp5787181.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
2014/1/15 gmb <gmbouwer@gmail.com>: > I now want to add debugging to trigger_func_2 where I log the original > statement (transaction?). > I.e. I'm interested in something like "INSERT INTO table1.... " > Anybody have any ideas on how I can get this original statement ? You can log every single statement: In postgresql.conf file, you can change the log_statement to 'all'. BUT: it is dangerous in production: you can fill your file system very fast and slow down the system. Another way is: modify the trigger and write informations you need in a specific table. Not an elegant solution but... > Thanks > GMB Lucazeo.
Thanks for your reply, Lucazeo. > BUT: it is dangerous in production: you can fill your file system very > fast and slow down the system. Yes, this was my concern as well. In my current situation this may be too risky. >Another way is: modify the trigger and write informations you need in >a specific table. Not an elegant solution but... Exactly what I had in mind, but I hoped to be able to log the original statement as well . In that way I can simulate the problem-causing transactions exactly in a more controlled env by running the scripts as in the log table. Thanks any way. -- View this message in context: http://postgresql.1045698.n5.nabble.com/debugging-triggers-get-original-statement-tp5787181p5787212.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Wed, Jan 15, 2014 at 3:30 AM, gmb <gmbouwer@gmail.com> wrote:
>Another way is: modify the trigger and write informations you need inExactly what I had in mind, but I hoped to be able to log the original
>a specific table. Not an elegant solution but...
statement as well .
In that way I can simulate the problem-causing transactions exactly in a
more controlled env by running the scripts as in the log table.
You can also make use of the RAISE functionality and output the statement to your logs.
Here's a quick demo
create table footest (
id serial primary key,
col1 text,
col2 text
);
create or replace function do_something() returns trigger as
$func$
begin
RAISE LOG '%',(ROW(NEW.*)::text);
RETURN NEW;
end;
$func$ language plpgsql;
CREATE TRIGGER footest_trg BEFORE INSERT ON footest FOR EACH ROW EXECUTE PROCEDURE do_something();
-- In the logs:
2014-01-15 07:24:09 PST ... LOG: (1,test1,test2)
2014-01-15 07:24:09 PST ... [INSERT] STATEMENT: insert into footest (col1,col2) values ('test1','test2');
create table footest (
id serial primary key,
col1 text,
col2 text
);
create or replace function do_something() returns trigger as
$func$
begin
RAISE LOG '%',(ROW(NEW.*)::text);
RETURN NEW;
end;
$func$ language plpgsql;
CREATE TRIGGER footest_trg BEFORE INSERT ON footest FOR EACH ROW EXECUTE PROCEDURE do_something();
-- In the logs:
2014-01-15 07:24:09 PST ... LOG: (1,test1,test2)
2014-01-15 07:24:09 PST ... [INSERT] STATEMENT: insert into footest (col1,col2) values ('test1','test2');