X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3DFEC9FA2F0 for ; Thu, 22 Jun 2006 13:58:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 10453-03 for ; Thu, 22 Jun 2006 13:58:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from deliver.hol.gr (deliver.hol.gr [194.30.193.26]) by postgresql.org (Postfix) with ESMTP id DD4309FA68D for ; Thu, 22 Jun 2006 13:58:28 -0300 (ADT) Received: from auth-smtp.hol.gr (takeit01.mail.dc.hol.net [192.168.20.71]) by deliver.hol.gr (8.12.11/8.11.6) with ESMTP id k5MGvnKC021054 (using TLSv1/SSLv3 with cipher DHE-RSA-AES256-SHA (256 bits) verified OK) for ; Thu, 22 Jun 2006 19:57:49 +0300 Received: from pimp (static223128.dsl.hol.gr [62.38.223.129]) by auth-smtp.hol.gr (8.13.1/8.12.11) with ESMTP id k5MGwPZH007877 for ; Thu, 22 Jun 2006 19:58:25 +0300 Message-Id: <200606221658.k5MGwPZH007877@auth-smtp.hol.gr> From: "Forums @ Existanze" To: Subject: Re: Start up question about triggers Date: Thu, 22 Jun 2006 19:58:30 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005F_01C69636.3BD1E690" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Thread-Index: AcaWFdKy6rK/yyR1TeuloULe+5ixPwABYiYQ In-reply-to: X-Virus-Scanned: ClamAV 0.87/1560/Thu Jun 22 12:07:47 2006 on takeit01.mail.dc.hol.net X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/253 X-Sequence-Number: 25489 This is a multi-part message in MIME format. ------=_NextPart_000_005F_01C69636.3BD1E690 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thank you for your answer, We had though about your solution, the problem is that we have around 80 tables at the moment so your method would suggest adding another 80. I was wondering if it was possible to retrieve the query in the trigger function, cause what we wanted to achieve was to trigger a query log when any insert or update or delete operation was made on any of the 80 tables. This way we would have something like a query log table. Which will have the queries in the order that they were executed by n number of clients. Say one client updates a row, and the next client deletes it, we want to know the queries that occurred in that particular order. I hope this makes some sense :-) I should also mention that what we are trying to achieve is some sort of partial backup operation. Because of bad initial design, we didn't foresee this comming. So now we have two options, changing all the tables,queries and code, to contain two timestamps columns representing created and updated row, a flag for deleted row, and have some sort of maintanance operation that will clean all the deleted records, and create insert/update statements for those records that have been updated ie( time_of_update > time_of_creation). This will give us a list of operation (INSERT OR UPDATE statements) that can be written to a file, and run from a file. So if I had 10 partiall backups and ran them sequencially I would in theory have the data that I originally had. At the moment we are doing full back using pgdump, but this is another type of requirement. Any ideas greatly appreciated. Best Regards, Fotis _____ From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Aaron Bono Sent: 22 June 2006 19:03 To: Forums @ Existanze Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Start up question about triggers Why not just create a history table and have the trigger copy the data out of the table into the history table with a time stamp of the change. Then you don't need the query. For example Table A a_id, a_value1, a_value2 Table A_hist a_id, a_dt, a_value1, a_value2 Then A_hist has a PK of a_id, a_dt This would also be a lot easier to see WHAT changed and WHEN. You can use the NEW.col and OLD.col to see the new and old values during inserts and updates. Of course, I don't know your need so this may not be achieving your goal. -Aaron Bono On 6/22/06, Forums @ Existanze wrote: Sorry This is the complete message Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name If I create a trigger on table B that says that after the insert command to write the query into table A. So if I do insert into B values(1,"Blah") this will trigger my trigger. Is there any way to get the "insert into B values(1,"Blah")? At the moment I can see only the type of query that it is (INSERT UPDATE DELETE) best regards, Fotis _____ From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Forums @ Existanze Sent: 22 June 2006 12:19 To: pgsql-sql@postgresql.org Subject: [SQL] Start up question about triggers Hello all, I know that this question may be really simple, but I have decided to ask here due to fact that I don't know how to search for this on google or on the docs. I created a trigger fuction which updates a specific row in some table A. Is it possible to retain the query that was used to trigger the function. For example Table A query_row_id query_row TABLE B id name ------=_NextPart_000_005F_01C69636.3BD1E690 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Thank you for your answer,
 
We had though about your solution, the problem = is that we=20 have around 80 tables at the moment so your method would suggest adding = another=20 80.
 
I was wondering if it was possible to retrieve = the query in=20 the trigger function, cause what we wanted to achieve was to trigger a = query log=20 when any insert or update or delete operation was made on any of the 80 = tables.=20 This way we would have something like a query log table. Which will have = the=20 queries in the order that they were executed by n  number of = clients.=20
 
Say one client updates a row, and the next = client deletes=20 it, we want to know the queries that occurred in that particular=20 order.
 
I hope this makes some sense = :-)
 
I should also mention that what we are trying = to achieve is=20 some sort of partial backup operation. Because of bad initial design, we = didn't=20 foresee this comming. So now we have two options, changing all the=20 tables,queries and code, to contain two timestamps columns=20 representing created and updated row, a flag for deleted row, and = have some=20 sort of maintanance operation that will clean all the deleted records, = and=20 create insert/update statements for those records that have been updated = ie(=20 time_of_update > time_of_creation). This will give us a list of = operation=20 (INSERT OR UPDATE statements) that can be written to a file, and run = from a=20 file.
 
So if I had 10 partiall backups and ran them = sequencially I=20 would in theory have the data that I originally had. At the moment we = are doing=20 full back using pgdump, but this is another type of=20 requirement.
 
 
Any ideas greatly = appreciated.
 
Best Regards,
Fotis

From: = pgsql-sql-owner@postgresql.org=20 [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Aaron=20 Bono
Sent: 22 June 2006 19:03
To: Forums @=20 Existanze
Cc: pgsql-sql@postgresql.org
Subject: = Re: [SQL]=20 Start up question about triggers

Why not just create a history table and have the trigger = copy the=20 data out of the table into the history table with a time stamp of the=20 change.  Then you don't need the query.

For = example
Table=20 A
a_id,
a_value1,
a_value2

Table=20 A_hist
a_id,
a_dt,
a_value1,
a_value2

Then A_hist = has a PK=20 of a_id, a_dt

This would also be a lot easier to see WHAT = changed and=20 WHEN.  You can use the NEW.col and OLD.col to see the new and old = values=20 during inserts and updates.

Of course, I don't know your need = so this=20 may not be achieving your goal.

-Aaron Bono

On 6/22/06, Forums @=20 Existanze <forums@existanze.com> = wrote:
Sorry=20 This is the complete message
 
Hello = all,
 
I know that this question may = be really=20 simple, but I have decided to ask here due to fact that I don't know = how to=20 search for this on google or on the docs.
 
I created a trigger fuction = which updates=20 a specific row in some table A. Is it possible to retain the query = that was=20 used to trigger the function. For example
 
Table A
query_row_id
query_row
 
 
 
TABLE B
id
name
 
 
 
 
 
If I create a trigger on = table B that=20 says that after the insert command to write the query into  = table A. So=20 if I do
 
insert into B=20 values(1,"Blah")
 
this will trigger my trigger. = Is there=20 any way to get the "insert into B values(1,"Blah")? At the moment I = can see=20 only the type of query that it is (INSERT UPDATE = DELETE)
 
best = regards,
Fotis

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf = Of=20 Forums @ Existanze
Sent: 22 June 2006 = 12:19
To: pgsql-sql@postgresql.org
Subject: = [SQL] Start=20 up question about triggers

Hello = all,
 
I know that this question may = be really=20 simple, but I have decided to ask here due to fact that I don't know = how to=20 search for this on google or on the docs.
 
I created a trigger fuction = which updates=20 a specific row in some table A. Is it possible to retain the query = that was=20 used to trigger the function. For example
 
Table A
query_row_id
query_row
 
 
 
TABLE B
id
name

------=_NextPart_000_005F_01C69636.3BD1E690--