Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1121B2E2D68 for ; Fri, 16 Nov 2007 16:04:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 82366-06 for ; Fri, 16 Nov 2007 16:04:50 -0400 (AST) Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id 33B9C2E2D41 for ; Fri, 16 Nov 2007 16:04:50 -0400 (AST) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1It7Qm-0004nT-UV; Fri, 16 Nov 2007 15:04:48 -0500 Subject: Re: trigger ddl actions in a table From: Brad Nicholson To: =?ISO-8859-1?Q?=22K=F6nig=2C?= "Monika\"" Cc: "'pgsql-admin@postgresql.org'" In-Reply-To: <1C2329C9430609409808ECF10237287612EA4CCA@mydomain.com> References: <1C2329C9430609409808ECF10237287612EA4CCA@mydomain.com> Content-Type: text/plain; charset=utf-8 Date: Fri, 16 Nov 2007 15:04:48 -0500 Message-Id: <1195243488.8966.379.camel@bnicholson-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 Content-Transfer-Encoding: quoted-printable X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/166 X-Sequence-Number: 27409 On Fri, 2007-11-16 at 07:01 +0100, "K=C3=B6nig, Monika" wrote: >=20 > 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 . >=20 > I tried to make an trigger on pg_catalog.pg_class but this throws an erro= r : > "ERROR: permission denied: "pg_class" is a system catalog". >=20 > I can see all ddl in my log files, but=20 > - there are even the errors in and=20 > - i can't use it for a select >=20 >=20 > 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. --=20 Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.