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

AW: [HACKERS] triggers, views and rules (not instead)

From: Zeugswetter Andreas SARZ <Andreas(dot)Zeugswetter(at)telecom(dot)at>
To: "'Jan Wieck'" <jwieck(at)debis(dot)com>
Cc: "'pgsql-hackers(at)hub(dot)org'" <pgsql-hackers(at)hub(dot)org>
Subject: AW: [HACKERS] triggers, views and rules (not instead)
Date: 1998-02-20 17:51:42
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
Since we have so little documentation on the rules, I think we should save
little word describing them, so could you simply put the following into a
(undigested is still better than not adding it)

> > Why I like the rewrite system is:
> >    1. select rewrite     -- select trigger would be no good (optimizer)
>     Exactly that's what is done if you create  a  view.  Postgres
>     creates  a  regular  table  (look  at  pg_class  and into the
>     database directory) and then sets up a relation level instead
>     rewrite rule on select.
> >    2. The client can be really dumb, like MS Access or some other
> > standard ODBC tool
> >         which does not know anything about funcs procs and the like
> >         (even without using passthrough)
>     Yupp  -  the  client  must not know why and how and where the
>     data is left and coming from. But that's true in any case - a
>     trigger  for each row on insert can do anything different and
>     push the data wherever it wants.
> >    3. it is a lot more powerful than views
>     As said - views are only one special rule case in Postgres.
> >    4. it allows the optimizer to get involved (this is where triggers
> > fail per definition)
> >    5. once understood it is very easy to use
> >         easier than trigger with c stored procedure at least
>     Optimizing again and again. If the rules aren't instead,  the
>     querytree  get's  additional queries for every rule appended.
>     Have a table field that references an entry in another  table
>     and  this entry should have a refcount. So on update you must
>     decrease the refcount from the old ref and increase it on the
>     new.   You  create  two  rules so the UPDATE will result in 1
>     scan and 2 nestloops with scans inside - really optimized  if
>     the  referenced value doesn't change.  And don't think that a
>     rule qual of NEW != CURRENT might help - that will result  in
>     2 mergejoins where the scanned tuples are compared.
I fought that like a windmill, I guess it would be better to kill the
CURRENT keyword
with this meaning alltogether, since it only has the same meaning as the
tablename itself.
I have already crossed it out of my mind and don't miss anything.
I think there should instead be an OLD and NEW keyword
like in triggers:
	referencing old as <oldname> new as <newname>
that only reference the tuples in memory.

>     BTW,  this  sample  doesn't  work currently because the rules
>     queries are appended at the end of the  querytree,  thus  the
>     decrement  scan  having  the  same qual will not find the old
>     tuple    at    all    because    it's    already     outdated
>     (command_counter_increment  between  processing the queries).
>     Referencing CURRENT in a rule is not what most  people  think
>     it is.
>     The old 4.2 postgres had a second, instance level rule system
>     (prs2 stubs) that fired the rules actions when  actually  the
>     old  tuple and the new projected tuple where handy. There you
>     could have made also things like 'UPDATE NEW SET a = 4'  that
>     really   modified  the  in  memory  tuple  in  the  executors
>     expression context. Who the hell removed all that? It was  so
>     nice :-(
Absolutely !    I did cry up when that was done, but nobody responded :-(
Well to be honest Vadim did respond with the trigger code, which made me
feel comfortable again.

>     A  really  simple to write trigger can compare old != new and
>     only if send down the other two queries. This time they  wont
>     be  nestloops,  they  are  simple  scans. And the trigger can
>     arrange that the queries it uses  are  only  parsed  on  it's
>     first  of  all  calls and store the generated execution plans
>     permanently for quick execution (look at SPI_prepare).
>     For the stored C procedures you're  totally  right.  I  don't
>     like  the  C functions because it requires postgres superuser
>     rights to develop them and thus I created  PL/Tcl  where  joe
>     user  can  hack  around without having complete access to the
>     whole database (look at src/pl/tcl). And  someday  after  6.3
>     release  I'll really start on a plain PL/pgSQL implementation
>     that would give a  normal  user  the  opportunity  to  create
>     functions and triggers on a high level. There is light at the
>     end of the tunnel - hope that it isn't the coming train :-)
> >
> > I guess if triggers could also trigger simple select statements, I could
> do
> > most of what I want using triggers except of course the select stuff.
> > But as I said I like the rules system very much, especially after your
> > recent
> > fixes Jan :-) So please stick to supporting all 3: triggers, views and
> > rules. Wow :-)
>     Well - a trigger cannot build a view. The relation underlying
>     the view doesn't contain any tuples and a select trigger will
>     never be fired.  As long as there is no possibility to return
>     tuple  sets  from  non-SQL  functions.  But  a trigger can do
>     things like the pg_hide_passwd stuff much more powerful.  You
>     could  define  the trigger so that it checks if the user is a
>     superuser and overwrite the passwd value  only  in  the  case
>     where  he/she isn't. If fired at the right place it would too
>     work for things like the copy command etc.
>     We must stay with all 3 features. And I will take a  look  at
>     the  INSERT  ...  SELECT  view problem really soon as it is a
>     rule system problem that breaks views. But this is  only  the
>     SELECT  rewriting part of the rule system which I really like
>     (optimizable). The other areas (insert,  update,  delete)  of
>     the  rule  system are dangerous and I really think a powerful
>     PL/pgSQL language could make them obsolete.
> Jan
Ok, to sum it up:
	1. We need and want the select part of the rewrite rules.
	2. for the insert/update/delete rules the old instance rules system
	    was much more appropriate. TODO: dig up the old code
	    and merge it with the current trigger Implementation
		    it must be pretty much the wanted functionality (it
supported sql)
		3. the CURRENT keyword in the i/u/d rewrite rules is stupid
and should be disabled
		   destroyed and burned in hell
		4. To stick to the mainstream we should enhance the trigger
		    and forget the rule stuff for i/u/d
		create trigger passwd_utr
		referencing old as o new as n
		  for each row (statement, statement, statement, procedure,
...... all PL/pgSQL syntax allowed );
		-- with a syntax to modify the new tuple in memory



pgsql-hackers by date

Next:From: Zeugswetter Andreas SARZDate: 1998-02-20 17:56:12
Subject: AW: [HACKERS] Permissions on copy
Previous:From: Jan WieckDate: 1998-02-20 17:22:33
Subject: Re: [HACKERS] Running pgindent

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