Thread: AW: [HACKERS] Rule system
Jan Wieck wrote: > What else must be there? I think everything on the instance > level is better done by triggers. And if we add > row-/statement-level triggers on SELECT, there would be no > reason left to have non-instead rules. Or am I missing > something? While this is in my opinion true, it would be nice to extend the trigger syntax to allow the triggered action to be expressed in sql like: create trigger blabla after delete on people referencing old as o (insert into graves values (o.*)); -- disregard the syntax Andreas
>
> Jan Wieck wrote:
> > What else must be there? I think everything on the instance
> > level is better done by triggers. And if we add
> > row-/statement-level triggers on SELECT, there would be no
> > reason left to have non-instead rules. Or am I missing
> > something?
> While this is in my opinion true, it would be nice to extend the trigger syntax to
> allow the triggered action to be expressed in sql like:
>
> create trigger blabla after delete on people
> referencing old as o
> (insert into graves values (o.*)); -- disregard the syntax
>
> Andreas
With PL/pgSQL I can actually do the following:
create function on_death() returns opaque as '
begin
insert into graves (name, born, died)
values (old.name, old.born, ''now'');
return old;
end;
' language 'plpgsql';
create trigger on_death after delete on people
for each row execute procedure on_death();
I think we could extend the parser that it accepts the above
syntax and internally creates the required trigger procedure
and the trigger itself in the way we treat triggers now. This
is the same way we actually deal with views (accept create
view but do create table and create rule internally).
It would require two extensions to PL/pgSQL:
A 'RENAME oldname newname' in the declarations part so
the internal trigger procedures record 'old' can be
renamed to 'o'.
Implementation of referencing record/rowtype.* extends to
a comma separated list of parameters when manipulating
the insert statement. My current implementation of
PL/pgSQL can only substitute a single
variable/recordfiled/rowfield into one parameter.
These two wouldn't be that complicated. And it would have a
real advantage. As you see above, I must double any ' because
the function body is written inside of ''s. It's a pain - and
here's a solution to get out of it.
If anyone is happy with this, I would release PL/pgSQL after
6.4 and make the required changes in the parser.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
>> Jan Wieck wrote: >> > What else must be there? I think everything on the instance >> > level is better done by triggers. And if we add >> > row-/statement-level triggers on SELECT, there would be no >> > reason left to have non-instead rules. Or am I missing >> > something? >> While this is in my opinion true, it would be nice to extend the trigger syntax to >> allow the triggered action to be expressed in sql like: >> >> create trigger blabla after delete on people >> referencing old as o >> (insert into graves values (o.*)); -- disregard the syntax >> >> Andreas > > With PL/pgSQL I can actually do the following: > create function on_death() returns opaque as ' > begin > insert into graves (name, born, died) > values (old.name, old.born, ''now''); > return old; > end; > ' language 'plpgsql'; > > create trigger on_death after delete on people > for each row execute procedure on_death(); > > I think we could extend the parser that it accepts the above > syntax and internally creates the required trigger procedure > and the trigger itself in the way we treat triggers now. This > is the same way we actually deal with views (accept create > view but do create table and create rule internally). yup, that would be nice > > It would require two extensions to PL/pgSQL: > > A 'RENAME oldname newname' in the declarations part so > the internal trigger procedures record 'old' can be > renamed to 'o'. Actually, since this does not give added functionality, I guess always using the keywords old and new would be ok (get rid of "current" though, it is unclear and has another SQL92 meaning). > > Implementation of referencing record/rowtype.* extends to > a comma separated list of parameters when manipulating > the insert statement. My current implementation of > PL/pgSQL can only substitute a single > variable/recordfiled/rowfield into one parameter. This is a feature, that would make life easier ;-) (low priority) The real problem I have would be procedures that return more than one column (or an opaque row/or rows), or nothing at all. Like: create function onename returns char(16), char(16) -- or would it have to be returns opaque ? as 'select "Hans", "Moser"' language 'sql'; insert into employee (fname, lname) values (onename()); -- or insert into employee (fname, lname) select onename(); > > These two wouldn't be that complicated. And it would have a > real advantage. As you see above, I must double any ' because > the function body is written inside of ''s. It's a pain - and > here's a solution to get out of it. That is why I suggested a while ago to keyword begin and end for plpgsql, then everything between begin and end would be plsql automatically without the quotes. This would then be much like Oracle PL/SQL. Something like: create function delrow (int highestsalary) as begin delete from employee where sal > highestsalary; -- or :highestsalary or $highestsalary end; > > If anyone is happy with this, I would release PL/pgSQL after > 6.4 and make the required changes in the parser. > Actually for me the possibility to return an opaque row from a function would currently be the most important enhancement of all. Somewhere the code that handles the "returns opaque" case is missing code to handle the case where a whole tuple is returned. Andreas
> > It would require two extensions to PL/pgSQL:
> >
> > A 'RENAME oldname newname' in the declarations part so
> > the internal trigger procedures record 'old' can be
> > renamed to 'o'.
>
> Actually, since this does not give added functionality, I guess always using the
> keywords old and new would be ok (get rid of "current" though, it is unclear and has
> another SQL92 meaning).
But since it was soooo easy I did it already :-)
And yesss - I don't have current at all. Only new and old.
>
> >
> > Implementation of referencing record/rowtype.* extends to
> > a comma separated list of parameters when manipulating
> > the insert statement. My current implementation of
> > PL/pgSQL can only substitute a single
> > variable/recordfiled/rowfield into one parameter.
>
> This is a feature, that would make life easier ;-) (low priority)
Agree - low priority. So I leave this feature for later.
>
> The real problem I have would be procedures that return more than one column (or an opaque row/or rows),
> or nothing at all.
> Like:
>
> create function onename returns char(16), char(16) -- or would it have to be returns opaque ?
> as 'select "Hans", "Moser"' language 'sql';
>
> insert into employee (fname, lname) values (onename()); -- or
> insert into employee (fname, lname) select onename();
> >
> > These two wouldn't be that complicated. And it would have a
> > real advantage. As you see above, I must double any ' because
> > the function body is written inside of ''s. It's a pain - and
> > here's a solution to get out of it.
>
> That is why I suggested a while ago to keyword begin and end for plpgsql,
> then everything between begin and end would be plsql automatically without the quotes.
> This would then be much like Oracle PL/SQL.
>
> Something like:
> create function delrow (int highestsalary) as begin
> delete from employee where sal > highestsalary; -- or :highestsalary or $highestsalary
> end;
>
> >
> > If anyone is happy with this, I would release PL/pgSQL after
> > 6.4 and make the required changes in the parser.
> >
>
> Actually for me the possibility to return an opaque row from a function
> would currently be the most important enhancement of all.
> Somewhere the code that handles the "returns opaque" case is missing code to
> handle the case where a whole tuple is returned.
>
> Andreas
I think I should clearify some details.
PL/pgSQL sticks right into the current implementation of
loadable procedural languages. It has it's own, completely
independent scanner and parser. PostgreSQL's CREATE FUNCTION
simply creates a pg_proc entry with prosrc attribute set to
the functions text.
When the function (or trigger procedure which are functions
too) is invoked, the PL/pgSQL shared object is loaded and
called. This now reads the pg_proc tuple and compiles the
prosrc (only done on the first call of the function).
Then the PL/pgSQL executor (totally different beast from the
PostgreSQL main executor) runs over the precompiled
statements. Many of the statements will invoke calls to the
SPI manager (any expression evaluation and DB access).
Inside of PL/pgSQL BEGIN..END; can be nested like in Oracle's
PL/SQL to build blocks of statements and different targets to
where EXIT will jump out. So if we change CREATE FUNCTION to
accept DECLARE/BEGIN..END; instead of '..' too, then it must
count the nesting level until it reached 0 again. All that
including the initial DECLARE/BEGIN and the final END; is the
string for the prosrc attribute of pg_proc. Needless to say
that comments and literal strings can contain any of these
keywords that don't have to be counted then.
Now we all know enough. So back to discussion.
All restrictions that C functions have are also restrictions
to loadable procedural language functions. And beeing unable
to return multiple results, tuples or tuplesets is such a
restriction to C functions. Thus, we have to extend the
function call interface of PostgreSQL at all. But not before
6.4!
I would really like any kind of function (C, PL/Tcl,
PL/pgSQL, more?) to be able to return tuples or tuplesets.
And I took a look at the executor some time ago to see how
this might be done. But boy, this is far more to do than it
looks like first. Function calls that return tuples have
target lists and they don't return only a tuple, they return
a tuple table slot containing a projection tuple (if I
remember correctly). And functions returning tuple sets are
one of the most ugly thing I've ever seen. In the case of a
set return the executors func node is manipulated so the scan
node from the last sql statement in the function can be used
in subsequent calls to get the next row instead of invoking
the function again. No procedural language that requires back
control after a
RETURN value AND RESUME;
can procude a scan node that could fit into this model,
except that it creates a temp table, inserting all the tuples
to return there, and finally returning something like a
seqscan over the temp table so they get pulled back. Hmmm -
thinking about this is nice - might be a solution :-)
I don't know if it will be possible to give all functions the
ability to return tuples or sets. So let's assume for now
that it doesn't work (I'll really try hard after 6.4).
But then again, even if functions stay that restricted, what
do we need as rule functionality? Up to now I only have all
kinds of INSEAD rules on the statement level on my list.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> But then again, even if functions stay that restricted, what > do we need as rule functionality? Up to now I only have all > kinds of INSTEAD rules on the statement level on my list. The [select] trigger can't return sets/tuples or multiple rows the select rule system can. This is because of the currently restricted "create function" return values. I'll try to look over my diploma paper tonight, to look for rules that (at least currently) cannot be written as triggers (other than instead rules). What I like about the rewrite sytem is, that it passes through the optimizer. No way the trigger stuff is going to be optimizable, is it ? It will always do something like a nested loop. Please let's not get rid of rules until we have the full trigger functionality discussed earlier, even if it does behave strangely in some cases, it is still very usable. I think the select rule stuff is mostly working since your last changes ;-) Maybe we could add syntax to the trigger statement to simply use the current select rule stuff ? Andreas