Updateable views...

On Tue, 4 Mar 2003, Eric D Nielsen wrote:

The one place I haven't been able to use PostGreSQL to experiment is
with regards to updateable views. I've found a few threads in
-general and -hackers (including one linked from the ToDo list), but
they all seem to die out without really reaching any sort of
conclusion. I've also seen that in many cases it appears possible to
use triggers/rules to simulate updateable views, but that feels like
an inelegant solution to me.

At some of the conferences I've been to, updatable/insertable-into views
are a big request. I've looked into the spec, which basically said that
views can be marked updatable/insertable-into if all attributes into the
query expression are updatable/insertable into.

I haven't had time to look into it further, but it occurs to me that
handling views which rely on joins would be far from trivial. (The big
selling point of views, for me, is as a means of tying together objects so
that an application has a simplified interface to application logic).

Anyone else do anything on this?

Gavin

Eric D Nielsen <nielsene@MIT.EDU> writes:

The one place I haven't been able to use PostGreSQL to experiment is with
regards to updateable views. I've found a few threads in -general and -hackers
(including one linked from the ToDo list), but they all seem to die out without
really reaching any sort of conclusion.

That's because we've discussed it about as far as is interesting, until
someone actually steps up and does the work ;-). We know how it should
be implemented: in Postgres terms, an updateable-view facility would
simply mean generating the appropriate ON INSERT/UPDATE/DELETE rules
automatically, whenever a view is created that is simple enough that
the code can figure out what said rules ought to be. (Hopefully this
condition will be pretty nearly equivalent to the rules the SQL spec
lays down for whether a view is updatable.)

Are there people working on this topic? I'ld be interested in helping out.

AFAIR, no one has done anything about it.

regards, tom lane

On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote:

The one place I haven't been able to use PostGreSQL to experiment is with
regards to updateable views. I've found a few threads in -general and -hackers
(including one linked from the ToDo list), but they all seem to die out without
really reaching any sort of conclusion. I've also seen that in many
cases it appears possible to use triggers/rules to simulate updateable views,
but that feels like an inelegant solution to me.

How so? A view is defined by ON SELECT rules; it seems natural, then,
that an updateable view would be defined ON INSERT / ON UPDATE rules.
AFAIK the only deficiency with the status quo is that the system does
not automatically define those insertion rules for you (in the subset of
cases where rules actually *can* be defined: for example, the view can't
include aggregation/grouping, calls to a user-defined function, etc.)

If you'd like to work on getting PostgreSQL to make views updateable
automatically, that would be cool -- AFAIK no one else is currently
working on it.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway <neilc@samurai.com> writes:

On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote:

The one place I haven't been able to use PostGreSQL to experiment is with
regards to updateable views. I've found a few threads in -general and -hackers
(including one linked from the ToDo list), but they all seem to die out without
really reaching any sort of conclusion. I've also seen that in many
cases it appears possible to use triggers/rules to simulate updateable views,
but that feels like an inelegant solution to me.

How so? A view is defined by ON SELECT rules; it seems natural, then,
that an updateable view would be defined ON INSERT / ON UPDATE rules.
AFAIK the only deficiency with the status quo is that the system does
not automatically define those insertion rules for you (in the subset of
cases where rules actually *can* be defined: for example, the view can't
include aggregation/grouping, calls to a user-defined function, etc.)

If you'd like to work on getting PostgreSQL to make views updateable
automatically, that would be cool -- AFAIK no one else is currently
working on it.

Would the rules approach be able to handle inline views? Ie, queries like:

UPDATE (select * from a natural join b) set a.foo = b.foo

On Oracle this is often the most efficient way to write update queries where
the data is coming from other tables.

--
greg

On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote:

The one place I haven't been able to use PostGreSQL to experiment is with
regards to updateable views. I've found a few threads in -general and -hac

kers

(including one linked from the ToDo list), but they all seem to die out wit

hout

really reaching any sort of conclusion. I've also seen that in many
cases it appears possible to use triggers/rules to simulate updateable view

s,

but that feels like an inelegant solution to me.

How so? A view is defined by ON SELECT rules; it seems natural, then,
that an updateable view would be defined ON INSERT / ON UPDATE rules.
AFAIK the only deficiency with the status quo is that the system does
not automatically define those insertion rules for you (in the subset of
cases where rules actually *can* be defined: for example, the view can't
include aggregation/grouping, calls to a user-defined function, etc.)

Using user-written rules seems inelegant to me because they force the user
to do something the DBMS should be able to do itself. Should the rules be
auto-generated by the DBMS then I wouldn't consider it inelegant.

If you'd like to work on getting PostgreSQL to make views updateable
automatically, that would be cool -- AFAIK no one else is currently
working on it.

I'm definately willing to look into it, can anyone offer any advice for
getting "situated" in the code? Are there paticular areas I should focus on
understanding/areas I should be able to safely ignore? All my PostGreSQL
experiences have been in user-land so far. Is there a good place to view the
SQL99 standard without shelling out the $20 to ASNI?

I know I'll have more questions later, but until then, happy coding...

Eric

Gavin Sherry <swm@linuxworld.com.au> writes:

I haven't had time to look into it further, but it occurs to me that
handling views which rely on joins would be far from trivial.

Views containing joins would not be updatable; problem solved. The set
of views the automatic-rule-generation machinery needs to handle are
those defined as updatable by the SQL spec. SQL92 says

12)A <query specification> QS is updatable if and only if the fol-
lowing conditions hold:

a) QS does not specify DISTINCT.

b) Every <value expression> contained in the <select list> imme-
diately contained in QS consists of a <column reference>, and
no <column reference> appears more than once.

c) The <from clause> immediately contained in the <table ex-
pression> immediately contained in QS specifies exactly one
<table reference> and that <table reference> refers either to
a base table or to an updatable derived table.

Note: updatable derived table is defined in Subclause 6.3,
"<table reference>".

d) If the <table expression> immediately contained in QS imme-
diately contains a <where clause> WC, then no leaf generally
underlying table of QS shall be a generally underlying table
of any <query expression> contained in WC.

e) The <table expression> immediately contained in QS does not
include a <group by clause> or a <having clause>.

The reference to 6.3 appears to be pointing at this:

8) A <derived table> is an updatable derived table if and only if
the <query expression> simply contained in the <subquery> of the
<table subquery> of the <derived table> is updatable.

I haven't quite wrapped my head around what clause 12d means, but 12c is
perfectly clear that you only get one table reference.

regards, tom lane

Gavin Sherry <swm@linuxworld.com.au> writes:

I haven't had time to look into it further, but it occurs to me that
handling views which rely on joins would be far from trivial.

Views containing joins would not be updatable; problem solved.

I see how that is what the spec says, but aren't the majority of joins that
people use/want to update a join of some type? I thought that SQL99 allowed
updating view created by joins.

In either case is this a place where "exceeding" the spec would be a good
thing or a bad thing?

Eric

Eric D Nielsen <nielsene@MIT.EDU> writes:

In either case is this a place where "exceeding" the spec would be a good
thing or a bad thing?

Unless there is an obvious definition of what updating a join means
(obvious not only to the implementor, but to the user) I think this
is dangerous territory.

regards, tom lane

On Wed, 2003-03-05 at 10:39, Eric D Nielsen wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

I haven't had time to look into it further, but it occurs to me that
handling views which rely on joins would be far from trivial.

Views containing joins would not be updatable; problem solved.

I see how that is what the spec says, but aren't the majority of joins that
people use/want to update a join of some type? I thought that SQL99 allowed
updating view created by joins.

In either case is this a place where "exceeding" the spec would be a good
thing or a bad thing?

Lets try to meet the spec first, then debate about whether extending it
is a good or bad thing :)

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On Wed, 5 Mar 2003, Eric D Nielsen wrote:

I see how that is what the spec says, but aren't the majority of joins that
people use/want to update a join of some type? I thought that SQL99 allowed
updating view created by joins.

In either case is this a place where "exceeding" the spec would be a good
thing or a bad thing?

This is a case where exceeding the spec would be a very good thing.
And there has been lots of research on how to make more complex views
updatable; see Date's _Introduction to Database Systems, 7th Edition_
for an in-depth discussion of this.

That said, one step at a time is always good, and even having just the
very simplest views updatable would be a very nice thing.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Tom Lane <tgl@sss.pgh.pa.us> writes:

Eric D Nielsen <nielsene@MIT.EDU> writes:

In either case is this a place where "exceeding" the spec would be a good
thing or a bad thing?

Unless there is an obvious definition of what updating a join means
(obvious not only to the implementor, but to the user) I think this
is dangerous territory.

Joins are a *BIG* part of the reason people want updateable views. In every
single case that I updated a view it was a join. Just being able to update
subsets of tables or restricted sets of columns is really a fairly trivial use
of a powerful feature.

In Oracle the constraint is fairly straightforward (at least to describe):
for each column you're updating the primary key of the table it came from has
to be present in the view.

--
greg

I'm pressing ahead with trying to implement the SQL92 version of updateable
views. I'm trying to track down a copy of the SQL92 standard, I thought that
ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in
the wrong place?

Eric Nielsen

Eric D Nielsen <nielsene@MIT.EDU> writes:

I'm pressing ahead with trying to implement the SQL92 version of updateable
views. I'm trying to track down a copy of the SQL92 standard, I thought that
ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in
the wrong place?

I'm not sure that ANSI would bother with obsoleted versions of
standards. What I tend to use is the final draft version of SQL92,
mainly because it's available in plain text that I can grep (PDF is not
a user-friendly format IMHO). The draft version is available for free
on the net --- I've forgotten exactly where, but if you trawl the list
archives you will find a URL. SQL99 is available in the same way, btw.
But I like SQL92 because it's much smaller and more readable.

regards, tom lane

Stanards URL's are in the developers FAQ.

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

Tom Lane wrote:

Eric D Nielsen <nielsene@MIT.EDU> writes:

I'm pressing ahead with trying to implement the SQL92 version of updateable
views. I'm trying to track down a copy of the SQL92 standard, I thought that
ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in
the wrong place?

I'm not sure that ANSI would bother with obsoleted versions of
standards. What I tend to use is the final draft version of SQL92,
mainly because it's available in plain text that I can grep (PDF is not
a user-friendly format IMHO). The draft version is available for free
on the net --- I've forgotten exactly where, but if you trawl the list
archives you will find a URL. SQL99 is available in the same way, btw.
But I like SQL92 because it's much smaller and more readable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

After finding the SQL92 draft spec that Tom quoted from earlier I think I
understand the conditions for the spec's version of view updatability. I've
made few comments below on the conditions and I'ld appreciate it if anyone
would correct any mis-interpretations on my part.

12)A <query specification> QS is updatable if and only if the fol-
lowing conditions hold:

a) QS does not specify DISTINCT.

No explanation needed.

b) Every <value expression> contained in the <select list> imme-
diately contained in QS consists of a <column reference>, and
no <column reference> appears more than once.

This appears to say that the select list must be of the form:
[<qualifier> <period>] <column name> [[AS] <column name>] [, ...]

No operations/functions may be applied to the column. Columns may be
renamed from the base table to the view using either "SELECT .. AS .." in the
query defining the view or in the "column name list" of the view, the
latter taking precedence if specified.

No column in the view may be a literal constant. No column from the base
table may appear more than once. (The '*' is expanded as discussed in the
spec into a form that matches the format listed above.)

c) The <from clause> immediately contained in the <table ex-
pression> immediately contained in QS specifies exactly one
<table reference> and that <table reference> refers either to
a base table or to an updatable derived table.

No joins (implicit or explicit) are allowed in an updateable view.
Updateable derived tables include: views that meet the requirements as
well as unnamed, intermediate dervived tables that meet the same standards.

I beleive this should allow queries such as:
UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2;
as well as the
CREATE VIEW foo_view AS SELECT bar, baz FROM foo;
UPDATE foo_view SET bar=1 WHERE baz==2;
DROP VIEW foo_view;
three-query analog.

However the one-query version can't be handled by the auto-
generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at.

CREATE VIEW foo_view AS SELECT bar, baz FROM (SELECT bar, baz FROM foo) AS qux;
should yield an updateable view as the derived table used in the from clause
is itself an updateable derived table.

d) If the <table expression> immediately contained in QS imme-
diately contains a <where clause> WC, then no leaf generally
underlying table of QS shall be a generally underlying table
of any <query expression> contained in WC.

I beleive this is saying that the ultimate base tables of the QS and the
ultimate base table invoved in a query in the WC must be disjoint.
e.g. (stupid example, but...)
CREATE VIEW foo_view AS SELECT bar,baz FROM foo WHERE bar<10;
CREATE VIEW foo2_view AS SELECT bar,baz FROM foo
WHERE baz in (SELECT bar,baz FROM foo_view) AND baz >15;

foo_view would be updateable. foo2_view would not be as the same ultimate
base table appears in both the table expression for the view and in the query
expression of the WC. Changing foo2_view to
CREATE VIEW foo2_view AS SELECT bar,baz, FROM foo_view ...
would not fix the problem as its the _ultimate_ base tables that matter.

e) The <table expression> immediately contained in QS does not
include a <group by clause> or a <having clause>.

No explanation needed.

Eric Nielsen

Eric D Nielsen <nielsene@MIT.EDU> writes:

I beleive this should allow queries such as:
UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2;
as well as the
CREATE VIEW foo_view AS SELECT bar, baz FROM foo;
UPDATE foo_view SET bar=1 WHERE baz==2;
DROP VIEW foo_view;
three-query analog.

However the one-query version can't be handled by the auto-
generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at.

Well, if you don't support joins or complex expressions then there's really no
value in inline views in update statements. WHERE clauses and excluded columns
are only really useful for security restrictions in real views.

It does seem to me that allowing complex expressions is fairly
straightforward: you bar updates to those columns, but allow use of them in
the rhs of set clauses.

That makes things like this possible:

CREATE VIEW foo as (select col, func1(col) as new_val where func2(col))

UPDATE foo SET col = new_val

which should be translated to:

UPDATE foo SET col = func1(col) WHERE func2(col)

That's not terribly useful in itself, but it means if you need those
additional columns for some other purpose, then you still get to take
advantage of the updateableness of the other columns.

I still hold out hope for eventually supporting joins, but that's obviously
more complicated to implement.

--
greg