pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaudit trail and system catalogs
4+ messages / 4 participants
[nested] [flat]
* audit trail and system catalogs
@ 2001-06-06 14:01 Markus Wagner <magnus@gmx.de>
2001-06-12 18:06 ` Re: audit trail and system catalogs Alex Pilosov <alex@pilosoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Markus Wagner @ 2001-06-06 14:01 UTC (permalink / raw)
To: pgsql-sql
Hi,
we strongly need to implement an audit trail as a prerequisite for
clinical trials, that is a functionality which records any change of any
data item in a database into one single table containing these events.
I found that one could use rules for this, but this would require one rule
for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
new.attr...").
My first question:
Could one reduce the rule set with rules for tables, e. g. one rule which
fires whenever *some* attribute of a table changes? Would there be a
possibility to reference the changed attributes in the action clause?
Assuming that one really needs one rule for each attribute I would like to
create a script which generates all rules for all attributes of all tables
for a database.
The problem is, how to get the names of all tables and their attributes? I
looked into the system tables ("pg_*"), but there were many tables and
many attributes for *my* tables, and I did not figure out how to
distinguish my tables and my attributes from the other ones. None of the
columns in pg_class and pg_attribute seems to give information on wether
the item is system or user defined.
My second question:
How can I loop to all of *my* tables and *my* attributes, ignoring system
tables and system generated attributes within my tables?
Thank you very much for any hint,
Markus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: audit trail and system catalogs
2001-06-06 14:01 audit trail and system catalogs Markus Wagner <magnus@gmx.de>
@ 2001-06-12 18:06 ` Alex Pilosov <alex@pilosoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alex Pilosov @ 2001-06-12 18:06 UTC (permalink / raw)
To: Markus Wagner <magnus@gmx.de>; +Cc: pgsql-sql
The conventional solution for this is to have a trigger on update,
and have a history table with the same structure as original table, and
inserting the old unmodified row into history.
IF you really require structure like "field oldvalue newvalue", it doesn't
seem possible in generic way without writing some C code to look at the
fieldlists of the tables and compare them. Alternatively, you may try
using EXECUTE feature of plpgsql and construct/execute queries dynamically
to find out if attribute has changed or not, but that will be much slower
than a C function.
Your plpgsql code would look like
for attr in (list of attrs of the current table)
execute into is_diff 'select old.attr <> new.attr'
if (is_diff)
execute insert into audit tablename, attr, old.attr, new.attr
end
(This is pseudocode, I gloss over most things :)
On Wed, 6 Jun 2001, Markus Wagner wrote:
> Hi,
>
> we strongly need to implement an audit trail as a prerequisite for
> clinical trials, that is a functionality which records any change of any
> data item in a database into one single table containing these events.
>
> I found that one could use rules for this, but this would require one rule
> for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
> new.attr...").
>
> My first question:
> Could one reduce the rule set with rules for tables, e. g. one rule which
> fires whenever *some* attribute of a table changes? Would there be a
> possibility to reference the changed attributes in the action clause?
>
> Assuming that one really needs one rule for each attribute I would like to
> create a script which generates all rules for all attributes of all tables
> for a database.
>
> The problem is, how to get the names of all tables and their attributes? I
> looked into the system tables ("pg_*"), but there were many tables and
> many attributes for *my* tables, and I did not figure out how to
> distinguish my tables and my attributes from the other ones. None of the
> columns in pg_class and pg_attribute seems to give information on wether
> the item is system or user defined.
>
> My second question:
> How can I loop to all of *my* tables and *my* attributes, ignoring system
> tables and system generated attributes within my tables?
>
> Thank you very much for any hint,
>
> Markus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* audit trail and system catalogs
@ 2001-06-06 15:15 Markus Wagner <wagner@imsd.uni-mainz.de>
2001-06-06 16:23 ` Re: audit trail and system catalogs Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Markus Wagner @ 2001-06-06 15:15 UTC (permalink / raw)
To: pgsql-sql
Hi,
we strongly need to implement an audit trail as a prerequisite for
clinical trials, that is a functionality which records any change of any
data item in a database into one single table containing these events.
I found that one could use rules for this, but this would require one rule
for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
new.attr...").
My first question:
Could one reduce the rule set with rules for tables, e. g. one rule which
fires whenever *some* attribute of a table changes? Would there be a
possibility to reference the changed attributes in the action clause?
Assuming that one really needs one rule for each attribute I would like to
create a script which generates all rules for all attributes of all tables
for a database.
The problem is, how to get the names of all tables and their attributes? I
looked into the system tables ("pg_*"), but there were many tables and
many attributes for *my* tables, and I did not figure out how to
distinguish my tables and my attributes from the other ones. None of the
columns in pg_class and pg_attribute seems to give information on wether
the item is system or user defined.
My second question:
How can I loop to all of *my* tables and *my* attributes, ignoring system
tables and system generated attributes within my tables?
Thank you very much for any hint,
Markus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: audit trail and system catalogs
2001-06-06 15:15 audit trail and system catalogs Markus Wagner <wagner@imsd.uni-mainz.de>
@ 2001-06-06 16:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-06-06 16:23 UTC (permalink / raw)
To: Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-sql
On Wed, 6 Jun 2001, Markus Wagner wrote:
> The problem is, how to get the names of all tables and their attributes? I
> looked into the system tables ("pg_*"), but there were many tables and
> many attributes for *my* tables, and I did not figure out how to
> distinguish my tables and my attributes from the other ones. None of the
> columns in pg_class and pg_attribute seems to give information on wether
> the item is system or user defined.
IIRC, attnum<0 means it's a system column. So you only want the ones with
positive attnum values.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-06-12 18:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-06 14:01 audit trail and system catalogs Markus Wagner <magnus@gmx.de>
2001-06-12 18:06 ` Alex Pilosov <alex@pilosoft.com>
2001-06-06 15:15 audit trail and system catalogs Markus Wagner <wagner@imsd.uni-mainz.de>
2001-06-06 16:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox