Skip site navigation (1) Skip section navigation (2)

System Tables and Triggers

From: Stef Telford <stef(at)chronozon(dot)artofdns(dot)com>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: System Tables and Triggers
Date: 2003-03-03 03:02:32
Message-ID: 1046660552.3281.8.camel@testlin.hades (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

	First of all, kudos on postgreSQL. That being said,
am having a slight problem with it, namely, System tables
appear to be 'special tables' and disallow triggers. Perhaps
its jst the way i am doing it, however, here is a rather
trite example to illustrate (file called system.trig):

	CREATE OR REPLACE FUNCTION action_upd() RETURNS opaque AS '
	BEGIN
		RAISE NOTICE ''User % added/updated'',NEW.usename;
		RETURN new;
	END;' LANGUAGE 'plpgsql';

	CREATE TRIGGER mysys_trig
	AFTER INSERT OR UPDATE on pg_shadow
	FOR EACH ROW
	EXECUTE PROCEDURE action_upd();

test1=# \i /data_raid/stefs_home/postgres/system.trig
CREATE FUNCTION
psql:/data_raid/stefshome/postgres/system.trig:10: ERROR: 
CreateTrigger: can't create trigger for system relation pg_shadow


	Now, i know that this is largely a useless example,
however the theory was that if this -was- possible, then it
would be child's play to create a session table for each user.
This would allow the system to store values which would make
things easier for the rest of the database and apache system
i am using (it would allow me to improve and normalise my
schema somewhat). I can also see the above being useful for
when you have more than one DBA and would like to log user
creation (actually, am thinking more of using pl/perl here
and then emailing out each user/modification to all admins)

	Last time this was tried (around 7.2) it allowed triggers
on system tables, but pg_user and pg_shadow and most other tables
would have values inserted in outside of the 'INSERT' logic. I can
understand that this is great for speed, but it seriously hampers
the usefulness of triggers (they wouldnt fire :)

	This would seem to be a step backwards to me though, as
now we cant even put triggers on the system tables. Is there any
chance of this being lifted ? (actually, can understand not
allowing pg_trigger mainly because it could end up a cyclic
trigger very easily :)
	

	Thanks and hope this makes some sort of sense :)

regards 
Stef Telford <stef(at)chronozon(dot)artofdns(dot)com>


Responses

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2003-03-03 03:11:09
Subject: Re: CLUSTER loses nulls (was Re: [ADMIN] Still a bug in
Previous:From: Rod TaylorDate: 2003-03-03 02:06:04
Subject: Re: Postgresql performace question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group