Re: Why can't I put a BEFORE EACH ROW trigger on a view?

Hi,

I want to do some additional data validation
when data is changed through a view, and
I want pretty exception messages, and I want to
do some slightly complex processing when
determining what data to update where in
what order.

So, I figured I'd make a table, put some
BEFORE EACH ROW triggers on it to do
all the tricky stuff, sometimes including
changing other tables, and then put a
SELECT rule on the table to make it look
like a view.

Turns out there's undocumented restrictions,
and this turns the table into a view which
then can't have triggers on it.

I can put other sorts of rules onto a table,
why can't I put a SELECT rule on a table?
What's the special case here?

It's probably conceivably possible to do
what I want using piles of functions
called by CHECK constraints, and possibly
many conditional rules, but that's
just grody.

For me, having a select rule on a table would
have additional benefit in that the front-end
would recognize the table as something that
can be modified. (PhpPgAdmin) As of now
it considers views as un-modifiable.

If a relation looks like a table in all respects,
why can't it _be_ a table? If it's a matter
of nobody having done the work I might
conceively possibly be able to come up
with a patch.

Any help would be appreciated.

Thanks.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Karl O. Pinc wrote:
> Hi,
>
> I want to do some additional data validation
> when data is changed through a view, and
> I want pretty exception messages, and I want to
> do some slightly complex processing when
> determining what data to update where in
> what order.
>
> So, I figured I'd make a table, put some
> BEFORE EACH ROW triggers on it to do
> all the tricky stuff, sometimes including
> changing other tables, and then put a
> SELECT rule on the table to make it look
> like a view.
>
> Turns out there's undocumented restrictions,
> and this turns the table into a view which
> then can't have triggers on it.

huh?

create your table, create your triggers on the table.
Use a view on top of the table for viewing (there is no such thing as a
select rule (well there is, but it is called a view)).

Sincerely,

Joshua D. Drake

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote:

> create your table, create your triggers on the table.
> Use a view on top of the table for viewing (there is no such thing as
> a
> select rule (well there is, but it is called a view)).

Yes, and you can't put a BEFORE EACH ROW trigger
on a view, which is my problem. I have _additional_
constraints to place on modifications done through
views, and trickyness involved in modifying the
underlying tables.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote:

> create your table, create your triggers on the table.
> Use a view on top of the table for viewing (there is no such thing as
> a
> select rule (well there is, but it is called a view)).

Maybe you don't understand, I want to modify the
underlying tables by using the view, because the
view presents something that is more understandable
to the user.

Sorry to be testy. It's been a long day
going down a road and finding a roadblock at the end and
the postgresql docs have never let me down before.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Karl O. Pinc wrote:
>
> On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote:
>
>> create your table, create your triggers on the table.
>> Use a view on top of the table for viewing (there is no such thing as
>> a
>> select rule (well there is, but it is called a view)).
>
> Yes, and you can't put a BEFORE EACH ROW trigger
> on a view, which is my problem. I have _additional_
> constraints to place on modifications done through
> views, and trickyness involved in modifying the
> underlying tables.

Why can't you put the before each row trigger on the base table?

>
> Karl <kop(at)meme(dot)com>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

>. I have _additional_
> constraints to place on modifications done through
> views, and trickyness involved in modifying the
> underlying tables.

Write a function foo that returns a set, then a view: "create view as
select * from foo()". Incorporate all the trickiness in the function,
including variables, multiple temporary tables, whatever you need.

I also think that a view is supposed to be just that -- a *view* of
underlying data, which in no way modifies the data. I don't know much
about the design ideas behind SQL, but I think this view of views
(haha) is an underlying assumption. If you are modifying data when
you do a select on a view, you are probably not using SQL the way it
was intended (not that that is a bad thing, but ...)

Postgresql has "rules" which I *think* can rewrite select statements.
Rules are kind of a pain, but maybe what you want.

On 2/24/07, Webb Sprague <webb(dot)sprague(at)gmail(dot)com> wrote:
> >. I have _additional_
> > constraints to place on modifications done through
> > views, and trickyness involved in modifying the
> > underlying tables.
>
> Write a function foo that returns a set, then a view: "create view as
> select * from foo()". Incorporate all the trickiness in the function,
> including variables, multiple temporary tables, whatever you need.
>

On 02/24/2007 06:47:56 PM, Joshua D. Drake wrote:
> Karl O. Pinc wrote:
> >
> > On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote:
> >
> >> create your table, create your triggers on the table.
> >> Use a view on top of the table for viewing (there is no such thing
> as
> >> a
> >> select rule (well there is, but it is called a view)).
> >
> > Yes, and you can't put a BEFORE EACH ROW trigger
> > on a view, which is my problem. I have _additional_
> > constraints to place on modifications done through
> > views, and trickyness involved in modifying the
> > underlying tables.
>
> Why can't you put the before each row trigger on the base table?

Consider this:

I have two columns in the view A and A1, A1 is the result
of calling a reversible function on A. If the user updates
A, the underlying A should be updated. If the user updates
A1, the underlying A should be updated by computing
the proper value for A by calling the inverse of the function
used to compute A1. If the user specifies both A and A1,
then check to see if the values are in conflict. If so
raise and exception. If not update the underlying A.

And this:

Table X has an Id column and a Name column. It's
joined with another table to make a view. 99.9% of
the time, the Name value is unique in X. If the
user updates the view and supplies a Name
we want the view to check if it's unique and,
if so, come up with the right Id so that the underlying
table can be modified. If the user specifies an
Id also, we again need to check for consistency
with the specified Name and raise an exception
if there's an inconsistency. We do not want the
user using the view to update the Name through
the view. The view is a 1 (of X) to many (of
the table X joins with, say Y), and this confuses the
user. We want the user to use the view to to "move" a row
of Y -- re-relate it to a different row in X by
changing the Name value.

Get very many of these situations and you can
see where it'd be much easier to have all the
logic in one trigger that does all the work.

Most of the problem comes down to raising
exceptions. If you could raise exceptions
in SQL code you might possibly be able to
write large, multi-statement rules and
not have lots of rules with lots of
conditions attached. But why?

Raising your own exceptions are nice because they can
mean something to the user -- with data
values put in the middle of explanatory text,
etc.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 02/24/2007 06:51:27 PM, Webb Sprague wrote:
>> . I have _additional_
>> constraints to place on modifications done through
>> views, and trickyness involved in modifying the
>> underlying tables.
>
> Write a function foo that returns a set, then a view: "create view as
> select * from foo()". Incorporate all the trickiness in the function,
> including variables, multiple temporary tables, whatever you need.

I tried that, and have already sent a message to this list about
it. The function needs to be passed NEW.* in order to know
what data it's looking at. When you try to do that you get
the following error message:

ERROR: function expression in FROM may not refer to other relations
of same query level

See:
http://archives.postgresql.org/pgsql-general/2007-02/msg01074.php

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 02/24/2007 06:55:45 PM, Webb Sprague wrote:
> I also think that a view is supposed to be just that -- a *view* of
> underlying data, which in no way modifies the data. I don't know much
> about the design ideas behind SQL, but I think this view of views
> (haha) is an underlying assumption. If you are modifying data when
> you do a select on a view, you are probably not using SQL the way it
> was intended (not that that is a bad thing, but ...)

The postgresql docs say something like: Having lots of views
is the mark of good database design.

That's because views present the same data in various ways,
to different users as each user thinks of the data. In general,
they allow good db design, and in a well designed db will
be incomprensibe to the average user, partly because it contains lots
of foreign keys to manage 1-to-many relationships. If it
makes sense to make a view so the user can see the data
in a form they can understand, then it makes sense to
allow the user to update the same view of the data.

> Postgresql has "rules" which I *think* can rewrite select statements.
> Rules are kind of a pain, but maybe what you want.

You can't rewrite select statments. That's where I started.
Trying to do so turns your table into a view, which then
can't have triggers.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

"Karl O. Pinc" <kop(at)meme(dot)com> writes:
> why can't I put a SELECT rule on a table?

Because then it would be a view.

As for $SUBJECT, the problem is that there will never be an insert into
a view --- not at the level of a physical insert attempt anyway --- and
thus there is nothing for a trigger to do.

The reason there will never be an insertion trigger event is that we
reject any INSERT on a view that isn't rewritten (by an unconditional
DO INSTEAD rule) into something else.

I recall a prior discussion about making it possible to use triggers on
views as a substitute for DO INSTEAD rules, by removing the rewrite-time
check and only erroring out if we actually get to the point of
attempting a physical insert. Then a BEFORE INSERT trigger could do
something appropriate with the data and return NULL to prevent the
error. This seems like a good idea because triggers often are much
easier to work with than rules --- eg, there's no problem with multiple
evaluations of volatile functions, even if you send the data to several
places. However, I'm not sure that the idea scales to cover updates and
deletes; with no concept of physical tuple identity (ctid) for the view
rows, it's not clear that you can write triggers that will reliably do
the right things.

regards, tom lane

> I recall a prior discussion about making it possible to use triggers on
> views as a substitute for DO INSTEAD rules, by removing the rewrite-time
> check and only erroring out if we actually get to the point of
> attempting a physical insert. Then a BEFORE INSERT trigger could do
> something appropriate with the data and return NULL to prevent the
> error. This seems like a good idea because triggers often are much
> easier to work with than rules ---

It is also my experience that triggers are faster than rules. At least
with table partitioning.

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

(Important stuff last.)

On 02/24/2007 07:48:58 PM, Tom Lane wrote:
> The reason there will never be an insertion trigger event is that we
> reject any INSERT on a view that isn't rewritten (by an unconditional
> DO INSTEAD rule) into something else.

(Yup. But I tried to make my own view implimentation by putting
a SELECT rule on a table and that failed because I couldn't
make a BEFORE EACH ROW trigger.

"Problems putting a SELECT rule on a table" would be
just another way to phrase $SUBJECT.

The annoying part was that I spent much of the day assuming it
would work and then when I tested what I wanted I found that
the CREATE RULE statement required that I supply "_RETURN"
as a rule name, and that then it wouldn't create the
rule anyway because the table had triggers. Usually this
sort of thing happens when I mis-read the docs, but this
time the docs provided no warning.)

> I recall a prior discussion about making it possible to use triggers
> on
> views as a substitute for DO INSTEAD rules, by removing the
> rewrite-time
> check and only erroring out if we actually get to the point of
> attempting a physical insert.

I had no error check fallback, but if I wanted one I suppose
I'd do a AFTER ... EACH ROW trigger that raised an exception
if it was ever called. I did do a BEFORE EACH STATEMENT
trigger that raised an exception and figured on deleting
it for those "views" that I made that needed BEFORE EACH
ROW triggers. Rules worked just fine for some views and
I was going to leave those, but allways do the views "my
way" for consistency instead of sometimes using CREATE VIEW
and sometimes not.

Then a BEFORE INSERT trigger could do
> something appropriate with the data and return NULL to prevent the
> error.

Exactly. My plan was to always have the trigger return NULL
so the underlying table would never be modified.

> This seems like a good idea because triggers often are much
> easier to work with than rules --- eg, there's no problem with
> multiple
> evaluations of volatile functions, even if you send the data to
> several
> places. However, I'm not sure that the idea scales to cover updates
> and
> deletes; with no concept of physical tuple identity (ctid) for the
> view
> rows, it's not clear that you can write triggers that will reliably do
> the right things

I started with inserts, ran into problems, and came
screaming to the list for help. (Thanks.) I somehow assumed
that I'd be able to get a hold of NEW.* and OLD.* in my triggers
-- because they were there for me in when I first tried to
impliment the logic with rules. How is it that the rules
can come up with NEW and OLD for a view and why wouldn't
something be able to give triggers the same data. (At
least BEFORE ... EACH ROW triggers, we wouldn't care about
other triggers, would we?)

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 02/24/2007 08:30:21 PM, Karl O. Pinc wrote:
> How is it that the rules
> can come up with NEW and OLD for a view and why wouldn't
> something be able to give triggers the same data.

Ah, NEW and OLD are only good in the WHERE part
of the rule, which is still in "query land"
country before execution starts.

At this point my ignorance is blinding me.
Couldn't the backend somehow use the SELECT
rule to produce and iterate over what's to
be modified so there'd be something to have
for OLD? Merge the WHERE part of the query
plan for the modification with the underlying
WHERE part? Likewise for NEW?

It must do something like that for the WHERE
part of the rule to get NEW and OLD.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

"Karl O. Pinc" <kop(at)meme(dot)com> writes:
> On 02/24/2007 07:48:58 PM, Tom Lane wrote:
>> However, I'm not sure that the idea scales to cover updates and
>> deletes; with no concept of physical tuple identity (ctid) for the
>> view rows, it's not clear that you can write triggers that will
>> reliably do the right things

> How is it that the rules can come up with NEW and OLD for a view and
> why wouldn't something be able to give triggers the same data.

NEW and OLD only include the user-visible columns. I'm not sure that
that's sufficient. If you assume that the view exposes a primary key
for each of its underlying tables, then you could use the pkey values
to find and update a row in the underlying table(s), but this is not
exactly guaranteeing that you're updating the same row that the view
query saw initially. And what if you don't want the view to expose
the pkey?

regards, tom lane

On 02/24/2007 08:48:04 PM, Karl O. Pinc wrote:

> Ah, NEW and OLD are only good in the WHERE part
> of the rule, which is still in "query land"
> country before execution starts.

No. I'm wrong here.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 02/24/2007 08:55:40 PM, Tom Lane wrote:
> "Karl O. Pinc" <kop(at)meme(dot)com> writes:
>
> NEW and OLD only include the user-visible columns. I'm not sure that
> that's sufficient. If you assume that the view exposes a primary key
> for each of its underlying tables, then you could use the pkey values
> to find and update a row in the underlying table(s), but this is not
> exactly guaranteeing that you're updating the same row that the view
> query saw initially. And what if you don't want the view to expose
> the pkey?

These are INSERT, UPDATE, and DELETEs, so why not get rid of
the SELECT column criteria and modify the query plan to
come up with a SELECT * ? In other words, why not expose everything
to the trigger? You'd only be doing this if there wasn't
otherwise a rule for, say, UPDATE, on the view anyhow.

Oh. You'd have to keep any non-columm expressions that the view
exposes. NEW sounds easy, but I don't know about OLD.
Somehow the rules are doing something for OLD now.

You'd have to come up with column name
conventions (or some syntax)
for NEW and OLD to handle conflicts should some
joined tables have non-unique column names. (The ones exposed
by the view already have names.)
I don't know the right way to approach
this problem but it feels tractable. (To somebody who's
very unlikely to be writing the code. FYI, you're like the
Pg-General-List-Code-Angel.)

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

On 2/24/07, Karl O. Pinc <kop(at)meme(dot)com> wrote:
> (Important stuff last.)
>
> On 02/24/2007 07:48:58 PM, Tom Lane wrote:
> > The reason there will never be an insertion trigger event is that we
> > reject any INSERT on a view that isn't rewritten (by an unconditional
> > DO INSTEAD rule) into something else.
>
> (Yup. But I tried to make my own view implimentation by putting
> a SELECT rule on a table and that failed because I couldn't
> make a BEFORE EACH ROW trigger.
>
> "Problems putting a SELECT rule on a table" would be
> just another way to phrase $SUBJECT.
>
> The annoying part was that I spent much of the day assuming it
> would work and then when I tested what I wanted I found that
> the CREATE RULE statement required that I supply "_RETURN"
> as a rule name, and that then it wouldn't create the
> rule anyway because the table had triggers. Usually this
> sort of thing happens when I mis-read the docs, but this
> time the docs provided no warning.)
>

http://www.postgresql.org/docs/current/static/rules-views.html

Actually, i found it very clear: if you create a SELECT rule on a
table it becomes a view, this is what postgres does every time you
create a view

> I started with inserts, ran into problems, and came
> screaming to the list for help. (Thanks.) I somehow assumed
> that I'd be able to get a hold of NEW.* and OLD.* in my triggers
> -- because they were there for me in when I first tried to
> impliment the logic with rules. How is it that the rules
> can come up with NEW and OLD for a view and why wouldn't
> something be able to give triggers the same data. (At
> least BEFORE ... EACH ROW triggers, we wouldn't care about
> other triggers, would we?)
>

why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW
on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite
those operations to the equivalents on the tables... that way you will
have your TRIGGERS validating the data...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

On 02/24/2007 11:24:40 PM, Jaime Casanova wrote:
> On 2/24/07, Karl O. Pinc <kop(at)meme(dot)com> wrote:

> http://www.postgresql.org/docs/current/static/rules-views.html
>
> Actually, i found it very clear: if you create a SELECT rule on a
> table it becomes a view, this is what postgres does every time you
> create a view

It does say that. But it does not say that if you do it exactly
the same thing will happen. I figured postgres does that internally,
when it knows it wants to make a view, but
if I did it explicitly I'd have a table with a select rule on it
that would operate just like a view but would still be a table.

And why not let me have a table with a select rule on it that
makes the table act like a view, but that I can otherwise
fiddle with myself, like adding triggers, and make of it
what I want? Then it's up to me to expose the foreign
keys or whatever else the triggers need to work.
That would be the painless solution that would work just
fine for my purposes, however nice it'd be to be able
to put BEFORE triggers on VIEWS -- after exposing all
columns of the underlying tables etc. (As I fantasized
about in a previous post.)

>
> why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW
> on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite
> those operations to the equivalents on the tables... that way you will
> have your TRIGGERS validating the data...

Because the view has columns that the underlying table does not,
that are computed but that I might want to update through,
validate, etc. See my previous post. Somebody already asked
this exact question.

I could write triggers on all my underlying tables that
do the "instantiated view" thing and update another table
with all the right info. And _then_ do what
you suggest, being sure to pass the data back
to the true underlying tables. (Actually, at that
point there'd be no point in CREATE VIEW
at all.) But that's a lot of work
and why go to the trouble when an ordinary view
will do just fine (as far as output goes anyway,
modification is where I have trouble.)
Having a table with real data in it seems like
a lot of overhead I don't need.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Added to TODO:

* Allow BEFORE INSERT triggers on views

http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php

---------------------------------------------------------------------------

Tom Lane wrote:
> "Karl O. Pinc" <kop(at)meme(dot)com> writes:
> > why can't I put a SELECT rule on a table?
>
> Because then it would be a view.
>
> As for $SUBJECT, the problem is that there will never be an insert into
> a view --- not at the level of a physical insert attempt anyway --- and
> thus there is nothing for a trigger to do.
>
> The reason there will never be an insertion trigger event is that we
> reject any INSERT on a view that isn't rewritten (by an unconditional
> DO INSTEAD rule) into something else.
>
> I recall a prior discussion about making it possible to use triggers on
> views as a substitute for DO INSTEAD rules, by removing the rewrite-time
> check and only erroring out if we actually get to the point of
> attempting a physical insert. Then a BEFORE INSERT trigger could do
> something appropriate with the data and return NULL to prevent the
> error. This seems like a good idea because triggers often are much
> easier to work with than rules --- eg, there's no problem with multiple
> evaluations of volatile functions, even if you send the data to several
> places. However, I'm not sure that the idea scales to cover updates and
> deletes; with no concept of physical tuple identity (ctid) for the view
> rows, it's not clear that you can write triggers that will reliably do
> the right things.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +