Message-Id: <200606261359.k5QDxE2p004593@auth-smtp.hol.gr>
From: "Forums @ Existanze" <forums@existanze.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Fwd: Start up question about triggers
Date: Mon, 26 Jun 2006 16:59:26 +0300
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcaZHUNepUKAfSvYS6SnjS1FphEz9wACX3Hg
In-Reply-To: <20060626104312.GB15992@phlogiston.dyndns.org>

Hello again,

The problem is not tracking WHAT changed, this can be done, as we have
discussed in this thread, the problem is how to replicate the necessary
commands that will alter a mirror database to reflect what has been changed,
sequencially and in the order that it has occurred.

I you can find my first question (I can repost), you will see that this
problem has arisen due of lack of proper design. This is a requirement that
has just come into the picture and we have to find a solution for it.

My initial question was intended to figure out a way to track these changes
AND create the necessary INSERT,DELETE and UPDATE statements for each of the
changes that occurr in the entire database. I wanted to avoid having to
create an audit table for each of the tables in the database. At the moment
we are counting 82, this will mean another 82 tables, along with 82 triggers
and so on. 

I personally don't believe that what I am trying to do is "fundamentally
misguided", it may not be possible, but my theoretical suggestion a couple
of posts back could be a very simple and fast way of doing this, given the
necessary functionallity. ie. Recording the statement that we ran on the
database.

Once again, I am amassed at this mailing list for the wonderfull support,
and lack of hesitasion in answering a lost soul's question, I just wished
the rest of the mailing list could be like this. I am really gratefull for
all your comments and suggestions. 

Best Regards,
Fotis
 

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org 
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
> Sent: 26 June 2006 13:43
> To: pgsql-sql@postgresql.org
> Subject: Re: Fwd: [SQL] Start up question about triggers
> 
> On Fri, Jun 23, 2006 at 10:29:33PM -0500, Aaron Bono wrote:
> > 
> > What I really cannot find is a way to _dynamically_ in the 
> trigger ask 
> > what COLUMNS are in OLD and NEW.  If we had:
> 
> All of the columns, of course.  When the tuple is UPDATEd, 
> even if you did not mention some columns in your UPDATE, 
> there is an OLD value and a NEW value.  They're the same.
> 
> > I wish I had more experience with stored procedures - I know what I 
> > would try to do, just not if it is possible or how to implement it.
> 
> I think what Tom (but I don't want to put words in his mouth) 
> and, certainly, I have been saying is that your plan to get 
> the list of transform commands is fundamentally misguided.  
> If you want to track changes, then that's what you should do: 
> track what changed.
> 
> Note that there is a project that already, in fact, does this 
> for you as part of how it works: Slony-I.
> 
> A
> 
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
> When my information changes, I alter my conclusions.  What do 
> you do sir?
> 		--attr. John Maynard Keynes
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>