Re: Thoughts about updateable views

I am currently thinking of updateable views for a possible student research
project. In this
case there comes some points to my mind, i want to share with the list.

a) Definition of an updateable view?

The first thing what i thought about was, what defines a updateable view.
An updateable
view cannot always be updateable, according to several RDBMS (SAPDB or DB2)
there
are the following issues:

- Multi-Join views without PKs of all underlying tables (so, how can the
base tables
adressed anyway?)
- Views that contains DISTINCT, Aggregates, GROUP BY, ORDER BY, HAVING or
functions
etc. in the col/table list
- Views that are based itself on views or on nested queries.

... [ room for enhancements.....]

b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
with
the creation of the SELECT Rule. I understand how PostgreSQL handles views
with its Rule
System, but what happens when no appropiate Rule can be created? Reject the
view make
it non-updateable per default or other action? In this case i don't
understand, if the WITH
CHECK OPTION is required for updateable views in PostgreSQL, since the view
rules can
be created as part of the SELECT rule and, according to the docs, the query
tree has
no entry for parts of the underlying table not mentioned in the views'
query.

This points are only a small overview what i have though about this
weekend. So, i believe
there are many more issues that should be mentioned when planning
updateable views,
aren't they? Some input would be nice, since i need a feeling for the
estimated complexity
of this project.

--

TIA

Bernd

On Mon, 22 Mar 2004, Bernd Helmle wrote:

> I am currently thinking of updateable views for a possible student research
> project. In this
> case there comes some points to my mind, i want to share with the list.
>
> a) Definition of an updateable view?

The SQL spec. You should definately get a look at at least the SQL92
definition before proceeding.

> b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
> with
> the creation of the SELECT Rule. I understand how PostgreSQL handles views
> with its Rule
> System, but what happens when no appropiate Rule can be created? Reject the

CREATE VIEW needs to check if all columns in the target list and the base
table are updatable or insertable into. Eg: How can you update: select
foo, random() from bar?

If the user specifically asked for an updatable view, then they can't have
one. If they didn't specifically ask, they get the usual read only view.

Gavin

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> a) Definition of an updateable view?

> The first thing what i thought about was, what defines a updateable view.

The SQL spec clearly defines the requirements for a view to be
updateable. It seems sufficient to me to handle the cases required by
the spec.

> b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
> with
> the creation of the SELECT Rule. I understand how PostgreSQL handles views
> with its Rule
> System, but what happens when no appropiate Rule can be created?

You don't create it. This corresponds to the view not being updateable.
AFAICS the spec expects CREATE VIEW to create both kinds of view without
the implementation making any particular comment about it.

We might need to mark automatically created rules as such, and be
prepared to drop them if the user then defines a manually-created rule.
Otherwise we will have backwards-compatibility problems with existing
databases.

regards, tom lane

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> If the user specifically asked for an updatable view, then they can't have
> one. If they didn't specifically ask, they get the usual read only view.

"Specifically asked" how? AFAICS the CREATE VIEW syntax doesn't make any
distinction. (The WITH CHECK OPTION option requires the view to be
updateable, but I imagine we'd not support that to begin with, anyway.)

regards, tom lane

Tom Lane wrote:
> Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> > a) Definition of an updateable view?
>
> > The first thing what i thought about was, what defines a updateable view.
>
> The SQL spec clearly defines the requirements for a view to be
> updateable. It seems sufficient to me to handle the cases required by
> the spec.
>
> > b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
> > with
> > the creation of the SELECT Rule. I understand how PostgreSQL handles views
> > with its Rule
> > System, but what happens when no appropiate Rule can be created?
>
> You don't create it. This corresponds to the view not being updateable.
> AFAICS the spec expects CREATE VIEW to create both kinds of view without
> the implementation making any particular comment about it.
>
> We might need to mark automatically created rules as such, and be
> prepared to drop them if the user then defines a manually-created rule.
> Otherwise we will have backwards-compatibility problems with existing
> databases.

I was thinking we create an updatable view if possible, and throw an
error if they try to insert/update/delete into a non-updatable view.
Right now we ignore such activity, but that seems wrong.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I was thinking we create an updatable view if possible, and throw an
> error if they try to insert/update/delete into a non-updatable view.

Right.

> Right now we ignore such activity, but that seems wrong.

Nonsense.

regression=# create view bar as select * from foo;
CREATE VIEW
regression=# insert into bar values (1);
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
regression=#

The only backwards-compatibility risk I see is if there's a manually
created ON-whatever rule; this probably has to override the automatic ones.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I was thinking we create an updatable view if possible, and throw an
> > error if they try to insert/update/delete into a non-updatable view.
>
> Right.
>
> > Right now we ignore such activity, but that seems wrong.
>
> Nonsense.
>
> regression=# create view bar as select * from foo;
> CREATE VIEW
> regression=# insert into bar values (1);
> ERROR: cannot insert into a view
> HINT: You need an unconditional ON INSERT DO INSTEAD rule.
> regression=#
>
> The only backwards-compatibility risk I see is if there's a manually
> created ON-whatever rule; this probably has to override the automatic ones.

Oh, good. What release fixed this? I sure hope it is 7.5 because I
don't see any mention of it in the release notes. :-) I poked around
in CVS and can't see when it was added. No matter --- it is fixed now.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> regression=# insert into bar values (1);
>> ERROR: cannot insert into a view

> Oh, good. What release fixed this?

[experiments...] 7.1.

regards, tom lane

--On Montag, März 22, 2004 16:48:29 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>> If the user specifically asked for an updatable view, then they can't
>> have one. If they didn't specifically ask, they get the usual read only
>> view.
>
> "Specifically asked" how? AFAICS the CREATE VIEW syntax doesn't make any
> distinction. (The WITH CHECK OPTION option requires the view to be
> updateable, but I imagine we'd not support that to begin with, anyway.)

Currently no SQL spec handy (i will have one soon) , i took a look onto
O'Reillys "SQL in a
Nutshell", where the WITH CHECK OPTION is defined as follows (translated
from German....):

Only data that can be read from the specific view can be updated, fields
that aren't
part of the view can't be updated. E.g. if a view is defined to display
only the monthly
salary of an employee, it wouldn't be possible to modify an employees
hourly salary.
O'Reilly says this is SQL99. There is also a CASCADED and LOCAL keyword
mentioned
which makes this check local to the specific view, or triggers the check to
all
possible underlying views.

Makes that sense or do i misunderstand important things?

In O'Reilly there are also the conditions mentioned a view has to pass when
declaring it updateable (also SQL99):

- The SELECT of the CREATE VIEW command depends only on one table
- UNION, EXCEPT and INTERSECT are not part of the View's SELECT
- No GROUP BY or HAVING
- Pseudo-Columns are forbidden, too (but i think that can improved to
functions....)
- No Aggregates
- No DISTINCT

so joined views are even not updateable, too.

--

Bernd

--On Montag, März 22, 2004 16:17:34 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Bernd Helmle <mailings(at)oopsware(dot)de> writes:

[...]

> We might need to mark automatically created rules as such, and be
> prepared to drop them if the user then defines a manually-created rule.
> Otherwise we will have backwards-compatibility problems with existing
> databases.

Not going into deeper details, but where should such information be stored?
It sounds like to have this in pg_rewrite?

--
Bernd

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> --On Montag, Mrz 22, 2004 16:17:34 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>> We might need to mark automatically created rules as such, and be
>> prepared to drop them if the user then defines a manually-created rule.

> Not going into deeper details, but where should such information be stored?
> It sounds like to have this in pg_rewrite?

Yes, a boolean column added to pg_rewrite is what I was thinking of.

regards, tom lane

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> Currently no SQL spec handy (i will have one soon) , i took a look onto
> O'Reillys "SQL in a
> Nutshell", where the WITH CHECK OPTION is defined as follows (translated
> from German....):

> Only data that can be read from the specific view can be updated, fields
> that aren't
> part of the view can't be updated. E.g. if a view is defined to display
> only the monthly
> salary of an employee, it wouldn't be possible to modify an employees
> hourly salary.

That sounds bogus to me. It's obvious that columns not present in the
view can't be updated through the view --- you simply do not have a way
to name them, so how could you affect them?

What the spec actually says, if I'm reading it correctly, is that CHECK
OPTION forbids you from using the view to insert/update *rows* that
would not appear in the view. For example given

CREATE VIEW v AS SELECT * FROM t WHERE flag != 42
WITH CHECK OPTION;

you could see the "flag" column in the view, and could set it on insert
or update --- so long as you didn't try to set it to 42. That would
mean that the result row was invisible in the view, which is what CHECK
OPTION forbids.

In the general case with complicated WHERE conditions, it seems this
would be extremely expensive to enforce. It would certainly be very
difficult to do it using only Postgres RULE mechanisms. So I'd suggest
not implementing the WITH CHECK OPTION feature; certainly not as part of
your first cut.

(But: it looks to me like the spec gives license to be restrictive about
the form of WHERE clauses in updatable views, so it might be that
something could be done about WITH CHECK OPTION with less pain than I'm
imagining.)

regards, tom lane

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> Currently no SQL spec handy (i will have one soon) ,

BTW, I find that the SQL92 spec is vastly more readable than SQL99,
partly because it's vastly shorter, and partly because the SQL99 authors
seem to have been into unnecessary formalism and obscurantism. It's
usually a good idea to read SQL92 first to see what the feature is
actually intended to do, before you try to make sense of SQL99's
treatment.

You can find draft copies of both the 92 and 99 specs for free on the
web (I think there are links in our developer's FAQ). I tend to use
these even though I have the official PDFs, mainly because plain ASCII
text is much easier to search than a PDF.

regards, tom lane

--On Montag, März 22, 2004 18:00:55 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

>
> That sounds bogus to me. It's obvious that columns not present in the
> view can't be updated through the view --- you simply do not have a way
> to name them, so how could you affect them?
>
> What the spec actually says, if I'm reading it correctly, is that CHECK
> OPTION forbids you from using the view to insert/update *rows* that
> would not appear in the view. For example given
>
> CREATE VIEW v AS SELECT * FROM t WHERE flag != 42
> WITH CHECK OPTION;
>
> you could see the "flag" column in the view, and could set it on insert
> or update --- so long as you didn't try to set it to 42. That would
> mean that the result row was invisible in the view, which is what CHECK
> OPTION forbids.
>

Ah, okay. I should have read this more carefully. Thanks for the
clarification. No it
makes sense....

--
Bernd