pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
audit 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>
  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

* audit trail and system catalogs
@ 2001-06-06 15:15  Markus Wagner <wagner@imsd.uni-mainz.de>
  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 16:23  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Markus Wagner <wagner@imsd.uni-mainz.de>
  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

* Re: audit trail and system catalogs
@ 2001-06-12 18:06  Alex Pilosov <alex@pilosoft.com>
  parent: Markus Wagner <magnus@gmx.de>
  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


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