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

Re: System Tables and Triggers

From: Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: Stef Telford <stef(at)chronozon(dot)artofdns(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: System Tables and Triggers
Date: 2003-03-02 08:11:40
Message-ID: 3E61BCBC.8000507@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)chronozon(dot)artofdns(dot)com>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)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>



In response to

pgsql-hackers by date

Next:From: Itai ZukermanDate: 2003-03-02 12:52:03
Subject: GiST: spl_rattr in gistSplit()
Previous:From: Marc G. FournierDate: 2003-03-02 03:20:05
Subject: ignore yet another test ...

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