pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brad Nicholson <bnichols@ca.afilias.info>
To: "König, "Monika\"" <monika.koenig@bonn.de>
Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
Subject: Re: trigger ddl actions in a table
Date: Fri, 16 Nov 2007 15:04:48 -0500
Message-ID: <1195243488.8966.379.camel@bnicholson-desktop> (raw)
In-Reply-To: <1C2329C9430609409808ECF10237287612EA4CCA@mydomain.com>
References: <1C2329C9430609409808ECF10237287612EA4CCA@mydomain.com>

On Fri, 2007-11-16 at 07:01 +0100, "König, Monika" wrote:
> 
> I need a way for logging every action on the system catalog. I must have a
> table which shows when a tables was created or updated or deleted. I need
> the date  of the change .
> 
> I tried to make an trigger on pg_catalog.pg_class but this throws an error :
> "ERROR: permission denied: "pg_class" is a system catalog".
> 
> I can see all ddl in my log files, but 
> 	- there are even the errors in and 
> 	- i can't use it for a select
> 
> 
> Is there an possibility for having such an system-action table??

You can't put triggers on the system catalog tables (as your error
points out).  The only way that I know of to track this directly through
the DB is to log the DDL changes in the Postgres logs, and then parse
that out.


-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.





view thread (4+ messages)

Message-ID: <1195243488.8966.379.camel@bnicholson-desktop>
Permalink:  ../1195243488.8966.379.camel@bnicholson-desktop/
Also on:    postgresql.org/message-id/1195243488.8966.379.camel@bnicholson-desktop

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: bnichols@ca.afilias.info, monika.koenig@bonn.de
  Subject: Re: trigger ddl actions in a table
  In-Reply-To: <1195243488.8966.379.camel@bnicholson-desktop>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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