Re: Fwd: 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

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Forums @ Existanze
Sent: 22 June 2006 12:19
To: pgsql-sql(at)postgresql(dot)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

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 <forums(at)existanze(dot)com> 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(at)postgresql(dot)org [mailto:
> pgsql-sql-owner(at)postgresql(dot)org] *On Behalf Of *Forums @ Existanze
> *Sent:* 22 June 2006 12:19
> *To:* pgsql-sql(at)postgresql(dot)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
>

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Aaron Bono
Sent: 22 June 2006 19:03
To: Forums @ Existanze
Cc: pgsql-sql(at)postgresql(dot)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 <forums(at)existanze(dot)com> 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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Forums @ Existanze
Sent: 22 June 2006 12:19
To: pgsql-sql(at)postgresql(dot)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

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(at)existanze(dot)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
>

> 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?

yes.
See the section "User Comments" at the very bottom of Chapter 33 after "Writing Trigger Functions
in C". It is odd that a PL_PGSQL example is given at the end a chapter for triggers written in C.

http://www.postgresql.org/docs/8.1/interactive/trigger-example.html

Also see the entire chapter 36.10 "Trigger Procedures"
http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html

Hope this is what you are looking for.

Regards,

Richard Broersma Jr.

Hello again aaron,

Im really interested in the part where you say "generic trigger" can you
give me some tips? As to how I will go about that? I had already read the
links that Richard gave, I new I could get the values like that. So right
now I will have to create a trigger for each of my tables to create the
necessary queries, or I could do it "generically" :-)

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Richard
> Broersma Jr
> Sent: 23 June 2006 08:10
> To: Aaron Bono; pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> > 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?
>
> yes.
> See the section "User Comments" at the very bottom of Chapter
> 33 after "Writing Trigger Functions in C". It is odd that a
> PL_PGSQL example is given at the end a chapter for triggers
> written in C.
>
> http://www.postgresql.org/docs/8.1/interactive/trigger-example.html
>
> Also see the entire chapter 36.10 "Trigger Procedures"
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
>
> Hope this is what you are looking for.
>
> Regards,
>
> Richard Broersma Jr.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Hi Fotis,

If you end up having to create a solution for each of the 80 tables, you may
want to check out the following (may also give you addtional ideas for what
you're trying to achieve):

http://www.varlena.com/GeneralBits/104.php (Logging Audit Changes with
Composite Typed Columns).

Regards,
George

----- Original Message -----
From: "Forums @ Existanze" <forums(at)existanze(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, June 23, 2006 2:15 AM
Subject: Re: Fwd: [SQL] Start up question about triggers

>
> Hello again aaron,
>
> Im really interested in the part where you say "generic trigger" can you
> give me some tips? As to how I will go about that? I had already read the
> links that Richard gave, I new I could get the values like that. So right
> now I will have to create a trigger for each of my tables to create the
> necessary queries, or I could do it "generically" :-)
>
> Best Regards,
> Fotis
>
>> -----Original Message-----
>> From: pgsql-sql-owner(at)postgresql(dot)org
>> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Richard
>> Broersma Jr
>> Sent: 23 June 2006 08:10
>> To: Aaron Bono; pgsql-sql(at)postgresql(dot)org
>> Subject: Re: Fwd: [SQL] Start up question about triggers
>>
>> > 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?
>>
>> yes.
>> See the section "User Comments" at the very bottom of Chapter
>> 33 after "Writing Trigger Functions in C". It is odd that a
>> PL_PGSQL example is given at the end a chapter for triggers
>> written in C.
>>
>> http://www.postgresql.org/docs/8.1/interactive/trigger-example.html
>>
>> Also see the entire chapter 36.10 "Trigger Procedures"
>> http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
>>
>> Hope this is what you are looking for.
>>
>> Regards,
>>
>> Richard Broersma Jr.
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

> Im really interested in the part where you say "generic trigger" can you
> give me some tips? As to how I will go about that? I had already read the
> links that Richard gave, I new I could get the values like that. So right
> now I will have to create a trigger for each of my tables to create the
> necessary queries, or I could do it "generically" :-)

Sorry, I guess I haven't kept up to speed with this thread.

However, from chapter 36.10
http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html

Notice the variables that you have to work with in a trigger function:

TG_WHEN
Data type text; a string of either BEFORE or AFTER depending on the trigger's definition.

TG_RELNAME = Data type name; the name of the table that caused the trigger invocation.

TG_OP = Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the
trigger was fired.

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level
triggers. This variable is NULL in statement-level triggers.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level
triggers. This variable is NULL in statement-level triggers.

Also, notice chapter 9.19
http://www.postgresql.org/docs/8.1/interactive/functions-info.html

current_user = user name of current execution context

So with this information couldn't one (from a trigger function) insert a record in to a history
table with the following columns?:

Then for each column of the affect table if old.tbl_col1 != new.tbl_col1 then add a record to the
history as follows.

TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col1
TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col2
TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col3
TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_coln

is this something like what you had in mind?

Regards,

Richard Broersma Jr.

Hello again,

First of all thank you all for your effort in helping me solve this problem.

George's link seems like a complete auditing framework for a database, so I
will look into that, cuase it gives you a complete view of what is going on,
and I can have undo opertaions :-)

I have to say that this was not what I was looking for, but you guys helped
realized that it is the way to go.

Richard-
Your suggestion also makes a lot of sense and thank you for your suggestion.

What I have to point out is that no matter which solution we choose, we are
going to need to have an auditing table that represents each of our tables,
which as I mentioned are around 80 at the moment :-( so that is a lot of
extra work, but what can you do!

Image this scenario, and tell me if it wouldn't be awsome!

I have "n" number of tables each of which has different number of
columns,keys constraints and so on, and I have just ONE table with three
columns:

Logger
------
Log_id SERIAL PRIMARY KEY,
User VARCHAR(100) NOT NULL,
Query text

Then there exist a TG_QUERY parameter that we could use to get the actual
query ran by a user, so if I ran the imaginary query

INSERT INTO blah VALUES(DEFAULT,one,"23-08-3000")

I could use TG_QUERY and do

//trigger code
.........
INSERT INTO Logger VALUES(DEFAULT,user,TG_QUERY)
.....// end of trigger

This way I would end up with a log of the query ran on any of the tables so
theoretically

SELECT * FROM Logger ORDER BY Logger_id

Would return

Logger_id User Query
--------- ---- -----
2 fotis 'INSERT INTO blah VALUES(DEFAULT,one,"23-08-3000")'
4 fotis 'UPDATE seconblah SET parispo='hello' WHERE
parispo_id=50'
9 fotis 'DELETE FROM blah WHERE id=30'
12 fotis 'INSERT INTO seconblah VALUES(DEFAULT,'another')'
And so on....

This way I would have all the necessary queries to restore an incomplete
database with the appropiate data from some point in time to another in the
future.

The only difference between the theoretical method and the ones already
suggested is that I will still need "n" trigger function for each of the "n"
tables, but only ONE auditing table.

Would it be much nicer :-)

Once again thank you very much for all your help,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of George Weaver
> Sent: 23 June 2006 15:38
> To: Forums @ Existanze
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> Hi Fotis,
>
> If you end up having to create a solution for each of the 80
> tables, you may want to check out the following (may also
> give you addtional ideas for what you're trying to achieve):
>
> http://www.varlena.com/GeneralBits/104.php (Logging Audit
> Changes with Composite Typed Columns).
>
> Regards,
> George
>
>
> ----- Original Message -----
> From: "Forums @ Existanze" <forums(at)existanze(dot)com>
> To: <pgsql-sql(at)postgresql(dot)org>
> Sent: Friday, June 23, 2006 2:15 AM
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
>
> >
> > Hello again aaron,
> >
> > Im really interested in the part where you say "generic
> trigger" can you
> > give me some tips? As to how I will go about that? I had
> already read the
> > links that Richard gave, I new I could get the values like
> that. So right
> > now I will have to create a trigger for each of my tables
> to create the
> > necessary queries, or I could do it "generically" :-)
> >
> > Best Regards,
> > Fotis
> >
> >> -----Original Message-----
> >> From: pgsql-sql-owner(at)postgresql(dot)org
> >> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Richard
> >> Broersma Jr
> >> Sent: 23 June 2006 08:10
> >> To: Aaron Bono; pgsql-sql(at)postgresql(dot)org
> >> Subject: Re: Fwd: [SQL] Start up question about triggers
> >>
> >> > 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?
> >>
> >> yes.
> >> See the section "User Comments" at the very bottom of Chapter
> >> 33 after "Writing Trigger Functions in C". It is odd that a
> >> PL_PGSQL example is given at the end a chapter for triggers
> >> written in C.
> >>
> >> http://www.postgresql.org/docs/8.1/interactive/trigger-example.html
> >>
> >> Also see the entire chapter 36.10 "Trigger Procedures"
> >> http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
> >>
> >> Hope this is what you are looking for.
> >>
> >> Regards,
> >>
> >> Richard Broersma Jr.
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 9: In versions below 8.0, the planner will ignore your
> desire to
> >> choose an index scan if your joining column's
> datatypes do not
> >> match
> >>
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>

Hello again,

Just a thought! Do any of you know if this is possible?

I have a table person

CREATE TABLE person(
person_id SERIAL PRIMARY KEY,
person_name VARCHAR(100) NOT NULL,
person_lastname VARCHAR(100) NOT NULL
);

And a table audit

CREATE TABLE audit(
audit_id SERIAL PRIMARY KEY,
audit_person person NOT NULL
);

As you can see in the audit table, "audit_person" is of type "person" which
is my second table.

I have managed to save the NEW object in a trigger fuction which represent a
person.

So issuing
INSERT INTO person VALUES(DEFAULT,'name','lastname');

WILL create a row for the audit table as such:

SELECT * FROM audit;

Returns
Id Person
-- ------
1 (1,name,lastname)

I then delete all from person and try to do this, I know it doesn't work but
is it possible?

DELETE FROM person;

INSERT INTO person SELECT (audit_person::person) FROM audit;

If the above was possible then I could theoretically just save the new
object in the audit table for all my tables!!

Any thoughts

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Forums @
> Existanze
> Sent: 23 June 2006 18:49
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> Hello again,
>
> First of all thank you all for your effort in helping me
> solve this problem.
>
> George's link seems like a complete auditing framework for a
> database, so I will look into that, cuase it gives you a
> complete view of what is going on, and I can have undo opertaions :-)
>
> I have to say that this was not what I was looking for, but
> you guys helped realized that it is the way to go.
>
> Richard-
> Your suggestion also makes a lot of sense and thank you for
> your suggestion.
>
>
> What I have to point out is that no matter which solution we
> choose, we are going to need to have an auditing table that
> represents each of our tables, which as I mentioned are
> around 80 at the moment :-( so that is a lot of extra work,
> but what can you do!
>
> Image this scenario, and tell me if it wouldn't be awsome!
>
> I have "n" number of tables each of which has different
> number of columns,keys constraints and so on, and I have just
> ONE table with three
> columns:
>
> Logger
> ------
> Log_id SERIAL PRIMARY KEY,
> User VARCHAR(100) NOT NULL,
> Query text
>
>
> Then there exist a TG_QUERY parameter that we could use to
> get the actual query ran by a user, so if I ran the imaginary query
>
> INSERT INTO blah VALUES(DEFAULT,one,"23-08-3000")
>
> I could use TG_QUERY and do
>
> //trigger code
> .........
> INSERT INTO Logger VALUES(DEFAULT,user,TG_QUERY) .....// end
> of trigger
>
> This way I would end up with a log of the query ran on any of
> the tables so theoretically
>
> SELECT * FROM Logger ORDER BY Logger_id
>
> Would return
>
> Logger_id User Query
> --------- ---- -----
> 2 fotis 'INSERT INTO blah
> VALUES(DEFAULT,one,"23-08-3000")'
> 4 fotis 'UPDATE seconblah SET parispo='hello' WHERE
> parispo_id=50'
> 9 fotis 'DELETE FROM blah WHERE id=30'
> 12 fotis 'INSERT INTO seconblah
> VALUES(DEFAULT,'another')'
> And so on....
>
>
>
> This way I would have all the necessary queries to restore an
> incomplete database with the appropiate data from some point
> in time to another in the future.
>
>
> The only difference between the theoretical method and the
> ones already suggested is that I will still need "n" trigger
> function for each of the "n"
> tables, but only ONE auditing table.
>
> Would it be much nicer :-)
>
> Once again thank you very much for all your help, Fotis
>
> > -----Original Message-----
> > From: pgsql-sql-owner(at)postgresql(dot)org
> > [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of George Weaver
> > Sent: 23 June 2006 15:38
> > To: Forums @ Existanze
> > Cc: pgsql-sql(at)postgresql(dot)org
> > Subject: Re: Fwd: [SQL] Start up question about triggers
> >
> > Hi Fotis,
> >
> > If you end up having to create a solution for each of the
> 80 tables,
> > you may want to check out the following (may also give you
> addtional
> > ideas for what you're trying to achieve):
> >
> > http://www.varlena.com/GeneralBits/104.php (Logging Audit
> Changes with
> > Composite Typed Columns).
> >
> > Regards,
> > George
> >
> >
> > ----- Original Message -----
> > From: "Forums @ Existanze" <forums(at)existanze(dot)com>
> > To: <pgsql-sql(at)postgresql(dot)org>
> > Sent: Friday, June 23, 2006 2:15 AM
> > Subject: Re: Fwd: [SQL] Start up question about triggers
> >
> >
> > >
> > > Hello again aaron,
> > >
> > > Im really interested in the part where you say "generic
> > trigger" can you
> > > give me some tips? As to how I will go about that? I had
> > already read the
> > > links that Richard gave, I new I could get the values like
> > that. So right
> > > now I will have to create a trigger for each of my tables
> > to create the
> > > necessary queries, or I could do it "generically" :-)
> > >
> > > Best Regards,
> > > Fotis
> > >
> > >> -----Original Message-----
> > >> From: pgsql-sql-owner(at)postgresql(dot)org
> > >> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Richard
> > >> Broersma Jr
> > >> Sent: 23 June 2006 08:10
> > >> To: Aaron Bono; pgsql-sql(at)postgresql(dot)org
> > >> Subject: Re: Fwd: [SQL] Start up question about triggers
> > >>
> > >> > 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?
> > >>
> > >> yes.
> > >> See the section "User Comments" at the very bottom of Chapter
> > >> 33 after "Writing Trigger Functions in C". It is odd that a
> > >> PL_PGSQL example is given at the end a chapter for
> triggers written
> > >> in C.
> > >>
> > >>
> http://www.postgresql.org/docs/8.1/interactive/trigger-example.html
> > >>
> > >> Also see the entire chapter 36.10 "Trigger Procedures"
> > >>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
> > >>
> > >> Hope this is what you are looking for.
> > >>
> > >> Regards,
> > >>
> > >> Richard Broersma Jr.
> > >>
> > >> ---------------------------(end of
> > >> broadcast)---------------------------
> > >> TIP 9: In versions below 8.0, the planner will ignore your
> > desire to
> > >> choose an index scan if your joining column's
> > datatypes do not
> > >> match
> > >>
> > >
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 4: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> > your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>

On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:
>
> Then there exist a TG_QUERY parameter that we could use to get the actual
> query ran by a user, so if I ran the imaginary query

Which "actual query"? By the time the trigger fires, the query might
already have been rewritten, I think. No? I _think_ that even
BEFORE triggers happen after the rewriter stage is called, but
someone who has more clue will be able to correct me if I'm wrong.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
Everything that happens in the world happens at some place.
--Jane Jacobs

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:
>> Then there exist a TG_QUERY parameter that we could use to get the actual
>> query ran by a user, so if I ran the imaginary query

> Which "actual query"? By the time the trigger fires, the query might
> already have been rewritten, I think. No? I _think_ that even
> BEFORE triggers happen after the rewriter stage is called, but
> someone who has more clue will be able to correct me if I'm wrong.

Even if you could get hold of the user query text, it'd be a serious
mistake to imagine that it tells you everything you need to know about
the update. Aside from rule rewrites, previous BEFORE triggers could
have changed fields that are mentioned nowhere in the query. The only
safe way to determine what's going on is to compare the OLD and NEW
row values.

regards, tom lane

This is why I was searching for good meta data.

Here is a thought. If your trigger has the OLD and NEW, is there a way to
get a list of fields from OLD and NEW? If TG_RELNAME is the name of the
table, could you just ask PostgreSQL what the columns are in that table,
iterate through those columns, get the values for each of these columns out
of OLD and NEW and save the old/new values?

What I really cannot find is a way to _dynamically_ in the trigger ask what
COLUMNS are in OLD and NEW. If we had:

- table affected (TG_RELNAME?)
- columns that are in the table
- old values for each of these columns
- new values for each of these columns

Then you could store this information into two tables:

modify_table
modify_table_id
modify_dt
table_name

modify_value
modify_value_id
modify_table_id
old_value
new_value

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.

Tom makes a very good point that having the actual query is not going to
help in a general sense. If someone does an insert or update which fires a
trigger that does further updates and inserts or even changes values on the
fly, the inserts and updates you record will NOT reveal exactly what is
going on. Keeping the values from OLD and NEW at the very end would be much
more useful.

-Aaron Bono

On 6/23/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> > On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:
> >> Then there exist a TG_QUERY parameter that we could use to get the
> actual
> >> query ran by a user, so if I ran the imaginary query
>
> > Which "actual query"? By the time the trigger fires, the query might
> > already have been rewritten, I think. No? I _think_ that even
> > BEFORE triggers happen after the rewriter stage is called, but
> > someone who has more clue will be able to correct me if I'm wrong.
>
> Even if you could get hold of the user query text, it'd be a serious
> mistake to imagine that it tells you everything you need to know about
> the update. Aside from rule rewrites, previous BEFORE triggers could
> have changed fields that are mentioned nowhere in the query. The only
> safe way to determine what's going on is to compare the OLD and NEW
> row values.
>
> regards, tom lane
>
>

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(at)crankycanuck(dot)ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

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(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: 26 June 2006 13:43
> To: pgsql-sql(at)postgresql(dot)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(at)crankycanuck(dot)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
>

> 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.

If your finial goal is just to achieve db server replication, wouldn't slony achieve what you
want?

Regards,

Richard Broersma Jr.

On Mon, Jun 26, 2006 at 04:59:26PM +0300, Forums @ Existanze wrote:
> 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.

Ah. That was not, in fact, clear to me. What is it about Slony that
doesn't solve this problem? You've checked it out, right?
<http://www.slony.info> will get you there.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA

On Mon, 2006-06-26 at 08:59, Forums @ Existanze wrote:
> 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'm having a mad scientist moment.

I bet it would be possible to hack pgpool to do this. Just have it
shoot all the queries that come in to it at the normal database, AND at
a text file or something like that.

Not sure that's any better than using pgsql logging to do the same
thing, but it certainly doesn't seem like it would be that hard to do.

On Mon, Jun 26, 2006 at 10:38:06AM -0500, Scott Marlowe wrote:
> I bet it would be possible to hack pgpool to do this. Just have it
> shoot all the queries that come in to it at the normal database, AND at
> a text file or something like that.

Now you're back to the same problem: it doesn't tell you what the
database looks like. It tells you what commands were sent. What
about triggers? Rules? For that matter, what about

SELECT now()

or

SELECT nextval()

?

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

Hello again,

I looked into slony, I have a question though, how would I go about
controlling slony via a jdbc driver? See this whole problem has arisen
because for some reason my client wants to keep to separate databases in two
separate locations with the same data. So he would call this partiall backup
function via a GUI client we provide, and with this information he would go
to the same GUI client in this other location and import this partiall
backup.

Best Regards,
Fotis

PS- Hacking pgtool is way out my leage :-) But thanks for the suggestion!!!

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: 26 June 2006 17:43
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> On Mon, Jun 26, 2006 at 04:59:26PM +0300, Forums @ Existanze wrote:
> > 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.
>
> Ah. That was not, in fact, clear to me. What is it about
> Slony that doesn't solve this problem? You've checked it out, right?
> <http://www.slony.info> will get you there.
>
> A
>
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> If they don't do anything, we don't need their acronym.
> --Josh Hamilton, on the US FEMA
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

On Tue, Jun 27, 2006 at 11:16:17AM +0300, Forums @ Existanze wrote:
>
> I looked into slony, I have a question though, how would I go about
> controlling slony via a jdbc driver? See this whole problem has arisen
> because for some reason my client wants to keep to separate databases in two
> separate locations with the same data. So he would call this partiall backup
> function via a GUI client we provide, and with this information he would go
> to the same GUI client in this other location and import this partiall
> backup.

It is totally normal to want to keep two databases in two locations:
that's a matter of safety. Slony does it automatically, as long as
the daemon is running. No need to control it.

_Unless_ you want to be able to write in the second database. That's
a different problem. You can't do that with Slony.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

Hello again andrew,

Actually man I do need to be able to write to both databases, and keep them
synchronized, and all this because of the recurring xenofobia for technology
and the fear of "hackers" (I know the difference between hackers and
crackers) on the internet, I actually said, it will cost less to have an
adsl line on both location connecting to the same database than to pay me to
create the synchronization framework, but hey what can you do!

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: 27 June 2006 14:40
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> On Tue, Jun 27, 2006 at 11:16:17AM +0300, Forums @ Existanze wrote:
> >
> > I looked into slony, I have a question though, how would I go about
> > controlling slony via a jdbc driver? See this whole problem
> has arisen
> > because for some reason my client wants to keep to separate
> databases
> > in two separate locations with the same data. So he would call this
> > partiall backup function via a GUI client we provide, and with this
> > information he would go to the same GUI client in this
> other location
> > and import this partiall backup.
>
> It is totally normal to want to keep two databases in two locations:
> that's a matter of safety. Slony does it automatically, as
> long as the daemon is running. No need to control it.
>
> _Unless_ you want to be able to write in the second database.
> That's a different problem. You can't do that with Slony.
>
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> The fact that technology doesn't work is no bar to success in
> the marketplace.
> --Philip Greenspun
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Andrew Sullivan wrote:
> On Tue, Jun 27, 2006 at 11:16:17AM +0300, Forums @ Existanze wrote:
>> I looked into slony, I have a question though, how would I go about
>> controlling slony via a jdbc driver? See this whole problem has arisen
>> because for some reason my client wants to keep to separate databases in two
>> separate locations with the same data. So he would call this partiall backup
>> function via a GUI client we provide, and with this information he would go
>> to the same GUI client in this other location and import this partiall
>> backup.
>
> It is totally normal to want to keep two databases in two locations:
> that's a matter of safety. Slony does it automatically, as long as
> the daemon is running. No need to control it.

But with file-based log shipping (see
http://linuxfinances.info/info/logshipping.html) one could write a Java
app to control when the updates are applied.

Joe

On Tue, Jun 27, 2006 at 02:48:38PM +0300, Forums @ Existanze wrote:
> Hello again andrew,
>
> Actually man I do need to be able to write to both databases, and keep them
> synchronized, and all this because of the recurring xenofobia for technology

Then sorry, but this can't be done out of the box by anything. You
have all manner of race conditions here.

> and the fear of "hackers" (I know the difference between hackers and

Moreover, this won't solve their problem: if someone compromised the
application, both databases would be corrupted. There's no way to
avoid that in such a design.

Better to use Slony in log-shipping mode (or PITR) and keep backups
of all the change sets. Then if someone broke in, you'd at least be
able to roll back to the known-good state.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

On Tue, Jun 27, 2006 at 08:14:34AM -0400, Joe wrote:
>
> But with file-based log shipping (see
> http://linuxfinances.info/info/logshipping.html) one could write a Java
> app to control when the updates are applied.

Well, sure. I mean, if you decide first, "I'll use Java," and then
start asking what problems you can solve, you can do anything ;-) I
prefer the strategy whereby one asks what the problem is to be solved
first, then choose the technology.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

> > Actually man I do need to be able to write to both databases, and keep them
> > synchronized, and all this because of the recurring xenofobia for technology
>
> Then sorry, but this can't be done out of the box by anything. You
> have all manner of race conditions here.

Doesn't PGcluster allow for multiple master databases that are kept synchronized?
http://pgfoundry.org/projects/pgcluster/

I thought that Mammoth replicator might support synchronous masters but it appears to be an
Asynchronous system like Slony.
http://www.commandprompt.com/products/mammothreplicator

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:

> I thought that Mammoth replicator might support synchronous masters
> but it appears to be an Asynchronous system like Slony.
> http://www.commandprompt.com/products/mammothreplicator

You are right, Mammoth Replicator is asynchronous single master, just
like Slony.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hello again,

I did some research on your suggestions with Slony-I and logshipping, one
major drawback we just realized is that the server where postgres will sit
on runs Windows XP. Our client has this techie that only knows how to manage
a windows box, and he runs some other services that we cannot port to linux.
Is there anything similar to slony-I that we could run on windows?

On the install notes of Slony 1.1.5 we didn't find any information about
compiling and running Slony-I on windows and we know that it will be
available in version 1.2

Best Regards,
Fotis

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Joe
> Sent: 27 June 2006 15:15
> To: Andrew Sullivan
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: Fwd: [SQL] Start up question about triggers
>
> Andrew Sullivan wrote:
> > On Tue, Jun 27, 2006 at 11:16:17AM +0300, Forums @ Existanze wrote:
> >> I looked into slony, I have a question though, how would I
> go about
> >> controlling slony via a jdbc driver? See this whole problem has
> >> arisen because for some reason my client wants to keep to separate
> >> databases in two separate locations with the same data. So
> he would
> >> call this partiall backup function via a GUI client we
> provide, and
> >> with this information he would go to the same GUI client in this
> >> other location and import this partiall backup.
> >
> > It is totally normal to want to keep two databases in two locations:
> > that's a matter of safety. Slony does it automatically, as long as
> > the daemon is running. No need to control it.
>
> But with file-based log shipping (see
> http://linuxfinances.info/info/logshipping.html) one could
> write a Java app to control when the updates are applied.
>
> Joe
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>