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

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).

Ok, I did not find anything (I tried hard) :-). Especially nothing that would currently work.
(I know more of it did work in postgres 4.2 though :-( )
So I really think the insert/update/delete rules, other than the instead stuff of course, are oblivious,
and don't work properly anyways, so we could probably really nuke them.
Not the select rules of course !

I still think the trigger syntax should be extended to allow a block of sql, like in Informix.
Then you could: execute one or more procedures, or as in most cases
do a simple statement like cascade a delete. Also a syntax would be nice
that would allow to change the "new" tuple.

In Informix the block begins with a ( and ends with ), the statements are separated by commas:

(insert into log values ('insert', new.name),
execute procedure current_datetime() into new.lastupdate)

While I dont particularly like the syntax it does provide excellent functionality.

Andreas

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).

Ok, I did not find anything (I tried hard) :-). Especially nothing that would currently work.
(I know more of it did work in postgres 4.2 though :-( )
So I really think the insert/update/delete rules, other than the instead stuff of course, are oblivious,
and don't work properly anyways, so we could probably really nuke them.
Not the select rules of course !

Now the target is clear. Make sure all instead rules work
correct and get rid of the others.

For the triggers: the triggers on SELECT should not be able
to fire in additional tuples. I think it would be enough if
they can modify the actual tuple before it is used or
suppress it at all.

I still think the trigger syntax should be extended to allow a block of sql, like in Informix.
Then you could: execute one or more procedures, or as in most cases
do a simple statement like cascade a delete. Also a syntax would be nice
that would allow to change the "new" tuple.

It might look like:

create trigger mytrig before insert or update on mytab
for each row do (
begin
new.lastupdate := 'now';
return new;
end;
) language 'plpgsql';

This would be easy. Just an enhancement to the parser and to
the create trigger utility processing so it creates the
required function on the fly. Modification of new, raising
errors via elog() and suppressing the operation itself by
returning NULL is already there in PL/pgSQL. We would need
something smart for the functions name, because using the
trigger name only would break the current possibility to
define the same trigger name on different tables with
different actions. Something like __trig_<oid> would be good.

You would still be able to create a regular function with no
arguments and return type opaque and then create triggers
with ... for each row execute procedure myothertrig(). There
can be any number of triggers for the same/overlapping events
on a table (not on a view - they would never be fired). This
is how I currently create triggers in PL/pgSQL.

In Informix the block begins with a ( and ends with ), the statements are separated by commas:

(insert into log values ('insert', new.name),
execute procedure current_datetime() into new.lastupdate)

While I dont particularly like the syntax it does provide excellent functionality.

Andreas

I like the ()'s around the statement block. It is already
something psql cares for when typing in queries. Anything
between can have ;'s and ''s as required. I would like to add
the () to CREATE FUNCTION too.

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:

It might look like:

create trigger mytrig before insert or update on mytab
for each row do (

^^
Why not EXECUTE ?

begin
new.lastupdate := 'now';
return new;
end;
) language 'plpgsql';

This would be easy. Just an enhancement to the parser and to
the create trigger utility processing so it creates the
required function on the fly. Modification of new, raising
errors via elog() and suppressing the operation itself by
returning NULL is already there in PL/pgSQL. We would need
something smart for the functions name, because using the

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

trigger name only would break the current possibility to

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

define the same trigger name on different tables with

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

different actions. Something like __trig_<oid> would be good.

^^^^^^^^^^^^^^^^^
I missed here. What did you mean?

Vadim
P.S. Sorry, I'm very busy currently :((

Jan Wieck wrote:

It might look like:

create trigger mytrig before insert or update on mytab
for each row do (

^^
Why not EXECUTE ?

Just to indicate that this time a function body, for which a
trigger function has to be created on the fly, follows
instead of the name of an existing function to be called.

But for bison it should be no problem to decide whether
EXECUTE PROCEDURE proname(args) or EXECUTE PROCEDURE (body)
is used. I don't really care about the final syntax.

begin
new.lastupdate := 'now';
return new;
end;
) language 'plpgsql';

This would be easy. Just an enhancement to the parser and to
the create trigger utility processing so it creates the
required function on the fly. Modification of new, raising
errors via elog() and suppressing the operation itself by
returning NULL is already there in PL/pgSQL. We would need
something smart for the functions name, because using the

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

trigger name only would break the current possibility to

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

define the same trigger name on different tables with

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

different actions. Something like __trig_<oid> would be good.

^^^^^^^^^^^^^^^^^
I missed here. What did you mean?

What I have in mind for

CREATE TRIGGER ... EXECUTE PROCEDURE (body)

is, that this time a trigger function is automatically
created before the usual trigger is defined. For the function
there is a name required. Currently the following is
possible:

CREATE TRIGGER on_insert AFTER INSERT on emp
FOR EACH ROW EXECUTE PROCEDURE on_ins_emp();

CREATE TRIGGER on_insert AFTER INSERT on payroll
FOR EACH ROW EXECUTE PROCEDURE on_ins_payroll();

The name of the trigger is the same but the table they
triggered for differs and they call different functions. I
don't want to loose this so we cannot use the name of the
trigger (on_insert) to create the trigger function since
overloading depends on different call arguments. But both
functions have no call arguments.

Clear now?

Vadim
P.S. Sorry, I'm very busy currently :((

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) #