agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feeddebugging triggers - get original statement?
4+ messages / 3 participants
[nested] [flat]
* debugging triggers - get original statement?
@ 2014-01-15 08:52 gmb <gmbouwer@gmail.com>
2014-01-15 11:22 ` Re: debugging triggers - get original statement? Luca Vernini <lucazeo@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: gmb @ 2014-01-15 08:52 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: debugging triggers - get original statement?
2014-01-15 08:52 debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
@ 2014-01-15 11:22 ` Luca Vernini <lucazeo@gmail.com>
2014-01-15 11:30 ` Re: debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luca Vernini @ 2014-01-15 11:22 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; +Cc: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: debugging triggers - get original statement?
2014-01-15 08:52 debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
2014-01-15 11:22 ` Re: debugging triggers - get original statement? Luca Vernini <lucazeo@gmail.com>
@ 2014-01-15 11:30 ` gmb <gmbouwer@gmail.com>
2014-01-15 15:25 ` Re: debugging triggers - get original statement? bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: gmb @ 2014-01-15 11:30 UTC (permalink / raw)
To: pgsql-sql
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....
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: debugging triggers - get original statement?
2014-01-15 08:52 debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
2014-01-15 11:22 ` Re: debugging triggers - get original statement? Luca Vernini <lucazeo@gmail.com>
2014-01-15 11:30 ` Re: debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
@ 2014-01-15 15:25 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: bricklen @ 2014-01-15 15:25 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; +Cc: pgsql-sql
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 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.
>
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');
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-01-15 15:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-15 08:52 debugging triggers - get original statement? gmb <gmbouwer@gmail.com>
2014-01-15 11:22 ` Luca Vernini <lucazeo@gmail.com>
2014-01-15 11:30 ` gmb <gmbouwer@gmail.com>
2014-01-15 15:25 ` bricklen <bricklen@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox