From: | "Nikolay Grebnev" <nikolaygrebnev(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Trigger proglem |
Date: | 2007-12-10 18:36:03 |
Message-ID: | a7cd64c30712101036h69b9bb77hd070bbd6d2044897@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Good day,
We have written a trigger that is
We have written a trigger that is associated with the table. When any
changes are submitted, then this trigger reconstruct and writes sql query to
the table querieslog.
We have found a problem: from time to time some sql query is not written
while the query is executed . It is seen: In php where the queries are
formed we see that the query is sent for execution and executed ant the base
was modified, but the trigger seems just to skip it (does not work with the
query). It is seen most in big tables and during big load of the server.
Current version of postgresql is 8.2.5.
Description of the table where the queries are stored
\d querieslog
Table "public.querieslog"
Column | Type | Modifiers
------------+--------------------------+---------------------------------------------------------------
query | text |
query_id | bigint | not null default
nextval('querieslog_query_id_seq'::regclass)
timequery | timestamp with time zone | default now()
timeinsert | integer |
Indexes:
"querieslog_query_id_index" btree (query_id)
The parameters of summoning the trigger for the table with one key:
pgr_iud_trig BEFORE INSERT OR DELETE OR UPDATE ON phpbb_users FOR EACH ROW
EXECUTE PROCEDURE pgr_iud('user_id')
For the tahbles with several key fields - we write the list of keys with
space between them
pgr_iud_trig BEFORE INSERT OR DELETE OR UPDATE ON mailing_list FOR EACH ROW
EXECUTE PROCEDURE pgr_iud('user_id mailing_id')
cc -I /usr/local/pgsql/include
/server/ -fpic -c -O3 repl.c
cc -shared -o pgr_iud.so repl.o
and repl.c itself - http://www.grebnevs.com/trigger/repl.c
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2007-12-10 18:51:39 | Re: Release Note Changes |
Previous Message | Alvaro Herrera | 2007-12-10 18:36:02 | Re: archive_command failures report confusing exit status |