Re: System Tables and Triggers - Mailing list pgsql-hackers
From | Hans-Jürgen Schönig |
---|---|
Subject | Re: System Tables and Triggers |
Date | |
Msg-id | 3E61BCBC.8000507@cybertec.at Whole thread Raw |
In response to | System Tables and Triggers (Stef Telford <stef@chronozon.artofdns.com>) |
List | 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@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>
pgsql-hackers by date: