Hi Stef

I had the same problem some time ago. I wanted to define a trigger 
firing on CREATE TABLE (pg_class). This won't work because in most cases 
system tables are not accessed using the "standard" way for processing 
queries (parse -> rewrite -> plan -> execute). Therefore triggers are 
not allowed. Triggers could lead to conflicts and corruption. I have 
done some code digging and I have removed the warning you have shown. It 
does not help - I guess there is no way to get rid of the problem. All 
you can do is to write a function doing the job for you. System tables 
don't care about events too much.

There used to be a whole thread on this subject matter called "Triggers 
and System Tables" - maybe digging the archives will give you a broader 
view of the problem.

Too bad - it would be a nice feature :).

In my case I have solved the problem with the help of a view and a set 
of rules - maybe this will work for you as well. Also, functions are a 
good choice.

    Regards,

        Hans



Stef Telford wrote:

>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@chronozon.artofdns.com>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>  
>


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>



Sun, 02 Mar 2003 09:11:40 +0100
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: System Tables and Triggers
Stef Telford <stef@chronozon.artofdns.com> writes:
> 	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 ?

Not unless you can explain to us how it can be safe to fire arbitrary
user-defined code when the system tables are in inconsistent
intermediate states.  Few commands change only one row of only one
catalog.

It may be (I haven't thought about it too hard) that it would be safe to
allow AFTER triggers on some tables.  But implementing it --- that is,
actually getting the triggers to be executed --- would be a lot of work.
And I'm afraid of the potential for nasty problems if such a trigger
raises an error or recursively invokes other catalog updates.

Bottom line is that this would be a large, difficult project with
(IMHO) relatively low return.  It may get done someday, but don't
hold your breath.

			regards, tom lane

Sun, 02 Mar 2003 12:43:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: System Tables and Triggers 
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@chronozon.artofdns.com>


02 Mar 2003 22:02:32 -0500
Stef Telford <stef@chronozon.artofdns.com>
System Tables and Triggers