Re: rules, triggers and views

We're working on DBI-link which is an access method of sorts
using existing tools.

We want an empty local table foo with
1. a _RETURN RULE
2. an update TRIGGER
3. a delete TRIGGER
4. an insert TRIGGER

It seems we can have either the RULE or the TRIGGERs but not both.
We've tried tweaking relkind to fake it into thinking it was
a table after creating the _RETURN rule turned it into a view.
This didn't work. Apparently it (wisely?) checks
explicitly for a _RETURN rule when trying to create triggers.
Creating the triggers before the rule also did not fool it.

The reason we are using triggers for updates, etc. is that we
cannot access the iteration of the NEW/OLD rows in RULES.

A preferred solution would be to use the existing infrastructure
rather than adding, for example, a new relkind or a full blown
access method.

Also, what are the reasons for forbidding triggers on views?
It is to prevent *possible* mayhem or will it cause real problems?

Thanks,

elein

----- End forwarded message -----

elein <elein(at)varlena(dot)com> writes:
> Also, what are the reasons for forbidding triggers on views?

The fact that they'd never fire (or better never fire). A view does not
actually store any tuples, so update and delete triggers on it are
certainly useless. You could imagine allowing BEFORE INSERT triggers,
with the understanding that nothing will be inserted no matter what the
trigger returns ... but such a trigger is still useless unless we remove
the safety restriction that says you can't INSERT into a view without
having an ON INSERT DO INSTEAD rule.

What you are probably wishing you had is some sort of ability to trigger
on "virtual", pre-rewriter operations, but the executor and the trigger
mechanism know nothing of this.

regards, tom lane

On 2004-12-05, elein <elein(at)varlena(dot)com> wrote:
> We're working on DBI-link which is an access method of sorts
> using existing tools.
>
> We want an empty local table foo with
> 1. a _RETURN RULE
> 2. an update TRIGGER
> 3. a delete TRIGGER
> 4. an insert TRIGGER

Use two tables. One has rules, the other has the trigger. (Neither store
any data.) Here's a working example (tested on 7.4.5):

-- declare the table which we're going to be manipulating. This never
-- actually stores anything (it becomes a view).
create table realtable (id integer, value text);

-- This is where the data comes from (hardcoded for example purposes)
create or replace function datasource() returns setof realtable as '
declare
v realtable;
begin
v.id:=1; v.value:=''foo''; return next v;
v.id:=2; v.value:=''bar''; return next v;
v.id:=3; v.value:=''baz''; return next v;
raise notice ''datasource() returned 3 rows'';
return;
end;
' language plpgsql;

-- This is where the updates etc. go to. It must return NULL, because we
-- want to suppress the actual row insertion.
create or replace function datasink() returns trigger as '
begin
raise notice ''datasink(): % % % % %'',
NEW.opcode,NEW.o_id,NEW.o_value,NEW.n_id,NEW.n_value;
return NULL;
end;
' language plpgsql;

-- This is a dummy table that we use for attaching the trigger to.
-- It's a real table, but it never contains data. Note that it stores
-- two copies of the data from "realtable" plus an opcode.
create table shadowtable (opcode "char",
o_id integer, o_value text,
n_id integer, n_value text);

-- attach the trigger
create trigger shadow_trigger before insert on shadowtable
for each row execute procedure datasink();

-- Rules. We convert all modifications on realtable into inserts on
-- shadowtable.
create rule "_RETURN" as
on select to realtable
do instead select * from datasource();
create rule ins_rule as
on insert to realtable
do instead insert into shadowtable values ('i',NEW.*);
create rule upd_rule as
on update to realtable
do instead insert into shadowtable values ('u',OLD.*,NEW.*);
create rule del_rule as
on delete to realtable
do instead insert into shadowtable values ('d',OLD.*);

Now we see it in action:

test=> update realtable set value = 'quux' where id=3;
NOTICE: datasource() returned 3 rows
NOTICE: datasink(): u 3 baz 3 quux
UPDATE 0
test=> delete from realtable where value='bar';
NOTICE: datasource() returned 3 rows
NOTICE: datasink(): d 2 bar <NULL> <NULL>
DELETE 0
test=> delete from realtable;
NOTICE: datasource() returned 3 rows
NOTICE: datasink(): d 1 foo <NULL> <NULL>
NOTICE: datasink(): d 2 bar <NULL> <NULL>
NOTICE: datasink(): d 3 baz <NULL> <NULL>
DELETE 0
test=> insert into realtable values (4,'zoom');
NOTICE: datasink(): i 4 zoom <NULL> <NULL>
INSERT 0 0

(Unfortunately, all the update operations return 0 rows affected. Not sure
there's any fix for that.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

We'd want the tuples to be produced by the select rule
and treated as if we didn't care where they came from.
Perhaps it doesn't work this way because of the way srfs
were integrated into the SQL execution? I dunno.

do instead triggers should be deemed safe on a view
for those brave enough to try.

The base problem still is having the iterations of OLD and
NEW available in rules--sort of the opposite problem of implementing
per statement triggers.

But we may have a solution still by having rules on the
view and triggers on a table and marrying them via the
update insert delete rules. We'll see how it goes.
This was suggested over on irc.

Thank you...More news as it happens.

--elein

On Sun, Dec 05, 2004 at 06:02:00PM -0500, Tom Lane wrote:
> elein <elein(at)varlena(dot)com> writes:
> > Also, what are the reasons for forbidding triggers on views?
>
> The fact that they'd never fire (or better never fire). A view does not
> actually store any tuples, so update and delete triggers on it are
> certainly useless. You could imagine allowing BEFORE INSERT triggers,
> with the understanding that nothing will be inserted no matter what the
> trigger returns ... but such a trigger is still useless unless we remove
> the safety restriction that says you can't INSERT into a view without
> having an ON INSERT DO INSTEAD rule.
>
> What you are probably wishing you had is some sort of ability to trigger
> on "virtual", pre-rewriter operations, but the executor and the trigger
> mechanism know nothing of this.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> Use two tables. One has rules, the other has the trigger. (Neither store
> any data.) Here's a working example (tested on 7.4.5):

> -- declare the table which we're going to be manipulating. This never
> -- actually stores anything (it becomes a view).
> create table realtable (id integer, value text);

> -- This is where the data comes from (hardcoded for example purposes)
> create or replace function datasource() returns setof realtable as ...

[ this rule converts the table into a view: ]
> create rule "_RETURN" as
> on select to realtable
> do instead select * from datasource();

Interesting hack. It creates a situation that CVS-tip pg_dump can't
handle:

$ pg_dump circle >circle.sql
pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter] FUNCTION datasource (ID 19 OID 293177)
pg_dump: [sorter] TABLE TYPE realtable (ID 206 OID 293173)
pg_dump: [sorter] TABLE realtable (ID 1162 OID 293172)
pg_dump: [sorter] RULE _RETURN (ID 1225 OID 293185)
$

because pg_dump isn't smart enough to break a view down into a table and
rule, which seems the only way to declare such a thing.

I'm inclined to think that it's too late to consider fixing this for 8.0
and we should leave a pg_dump fix for 8.1.

In the meantime, it might be better to avoid the circularity, like so:

create type datasource_type as (...);

create or replace function datasource() returns setof datasource_type as ...

create view realtable as select * from datasource();

regards, tom lane