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 F1D689FA5E6 for ; Fri, 23 Jun 2006 00:17:49 -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 29949-01 for ; Fri, 23 Jun 2006 00:17:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.205]) by postgresql.org (Postfix) with ESMTP id 4997B9FA2F4 for ; Fri, 23 Jun 2006 00:17:44 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id i31so375753wxd for ; Thu, 22 Jun 2006 20:17:43 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=DLVDTVil/AGaa9ICz026Wq2IRHx1haWQCDTTCNLNlc5CFEVl9uzcc13rqhj7emnPYj3I7tLQmL3jR1A/EqL/3P6oxK7iSQuykmLFwc62E+chwk9Uc0siQv2LrmDJIVDewbLvA57I48+LkRnox7p8Y21/0j/Xh4vb6a2KPjJ0WAg= Received: by 10.70.16.4 with SMTP id 4mr3906441wxp; Thu, 22 Jun 2006 20:17:43 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Thu, 22 Jun 2006 20:17:43 -0700 (PDT) Message-ID: Date: Thu, 22 Jun 2006 22:17:43 -0500 From: "Aaron Bono" To: pgsql-sql@postgresql.org Subject: Fwd: Start up question about triggers In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18290_6812519.1151032663577" References: <200606221658.k5MGwPZH007877@auth-smtp.hol.gr> X-Google-Sender-Auth: a54e3a3e7f5c43a4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/254 X-Sequence-Number: 25490 ------=_Part_18290_6812519.1151032663577 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I did some research and can't even find a way to get meta data in a trigger. In a trigger, is there a way to inspect OLD and NEW to see what columns are there and see what has changed? If so, you may not be able to grab the actual query but you could create a generic trigger that reconstructs a possible update/insert/delete for any table in your database. Does anyone know of a good place to go get information about using meta data in a stored procedure or trigger? Thanks, Aaron On 6/22/06, Forums @ Existanze wrote: > 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 > ------=_Part_18290_6812519.1151032663577 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I did some research and can't even find a way to get meta data in a trigger.

In a trigger, is there a way to inspect OLD and NEW to see what columns are there and see what has changed?  If so, you may not be able to grab the actual query but you could create a generic trigger that reconstructs a possible update/insert/delete for any table in your database.

Does anyone know of a good place to go get information about using meta data in a stored procedure or trigger?

Thanks,
Aaron

On 6/22/06, Forums @ Existanze <forums@existanze.com> wrote:
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


------=_Part_18290_6812519.1151032663577--