Re: trigger ddl actions in a table

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: "König, "Monika\"" <monika(dot)koenig(at)bonn(dot)de>
Cc: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: trigger ddl actions in a table
Date: 2007-11-16 20:04:48
Message-ID: 1195243488.8966.379.camel@bnicholson-desktop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Esben Mose Hansen 2007-11-16 20:41:53 Version database structure and content
Previous Message Joshua D. Drake 2007-11-16 16:49:06 Re: PostgreSQL - Services delivery query ?