Updateable views
Hi,
I'm currently working with Bernd in an implementation
of updateable views and want to know the hacker's
opinion on this issue.
What features have to be implemented in a first
extension in order to the patch to be accepted? What
features can wait until a second extension?
This are my first thought on this (i start working on
this just two weeks ago).
***************** thoughts *******************
- What if we cannot create one of the three rules?
Make the rule not updateable at all?
Or create the rules we can? (i think this is the
correct)
General Restrictions!!!
---------------------------
- The column target list holds column fields only,
that are retrieved from one base relation / view
only. (NO joined views).
- UNION [ALL]/EXCEPT, DISTINCT and GROUP BY query
expressions aren't updateable at all.
- HAVING, Aggregates, function expressions and
Subqueries aren't allowed to be updateable, too
NOTE: one option is add a catalog that contains info
about updateability of the view attributes, just
like ORACLE's user_updateable_column view
(actually pg_attribute says what columns has a
view, can it be extended?).
That way we can have views in which some columns
are updateable and other are not. Views with
more complicated querys (even joined ones) can
be allowed this way.
Insertable???
----------------------
We need to provide, at least, a value for every column
in the underlaying table that is NOT NULL and do not
have a DEFAULT value.
- If primary key of the table is a serial we can
manage it
CREATE RULE "ins_people_full" as ON
INSERT TO people_full DO INSTEAD
(
INSERT INTO people (person_id, inits, fname)
VALUES (nextval('people_person_id_seq'),NEW.inits,
NEW.fname);
INSERT INTO addresses (person_id,city, state, zip)
VALUES (currval('people_person_id_seq'), NEW.city,
NEW.state, NEW.zip);
);
- What if we add a new not null column without a
default value to the underlaying table? The insert
rule must be deleted?
Updateable???
----------------------
Deleteable???
----------------------
- Can we delete a row from the underlaying table if
the view where i execute the delete stmnt does not
view all the columns in that table?
- What about joined views? What is deleted?
Consider:
CREATE VIEW people_full AS
SELECT p.*, a.city, a.state, s.state_long,
a.country, a.zip
FROM people p JOIN addresses a USING (person_id)
JOIN states s USING (state);
The a.city, a.state, s.state_long, a.country, a.zip
columns must be deleted as well as the p.* columns
***********************************
- Other point is: some people will not be happy
with updateable views, they will want their views to
be read-only. Should we have an extension to the sql
specs for this? Something like a READONLY keyword?
The patch Bernd did, actually covers some of this
points but is just for *very, very* simple views. We
want improve it.
These of course are just general ideas, and we really
want to know your opinion.
regards,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Informaci�n de Estados Unidos y Am�rica Latina, en Yahoo! Noticias.
Vis�tanos en http://noticias.espanol.yahoo.com
- What if we cannot create one of the three rules?
Make the rule not updateable at all?
Or create the rules we can? (i think this is the
correct)
I seem to be in the minority here. But I think creating complex rules to
fiddle with the updates to translate them to the underlying tables is the
wrong approach.
I think you want to extend the SQL syntax to allow updating views, and
implement plan nodes and executor functionality to handle them. So things
like this works:
UPDATE (SELECT id,val FROM t) SET val=0 where id < 100
Then the rules you create on the views are just like the rules for SELECT,
they simply mechanically replace the view with the view definition.
I think this is the right approach because:
a) I think creating the general rules to transform an update into an update on
the underlying table will be extremely complex, and you'll only ever be
able to handle the simplest cases. By handling the view at planning time
you'll be able to handle arbitrarily complex cases limited only by whether
you can come up with reasonable semantics.
b) I think it's aesthetically weird to have functionality that's only
accessible via creating DDL objects and then using them, and not accessible
directly in a single SQL DML command. Ie, it would be strange to have to
create a "temporary view" just in order to execute an update because
there's no equivalent syntax available for use directly.
General Restrictions!!!
---------------------------
- The column target list holds column fields only,
that are retrieved from one base relation / view
only. (NO joined views).
I know there are other uses for updatable views (eg implementing column-based
security policies) but the _only_ reason I ever found them useful in Oracle
was precisely for joined views. They're the Oracle blessed method for
achieving the same performance win as Postgres's FROM clause.
So in Oracle you can do:
UPDATE (select a.val as newval, b.b_id, b.val from a,b where a.b_id = b.b_id) SET val = newval
--
greg
--- Greg Stark <gsstark@mit.edu> escribi�: - What if we cannot create one of the three
rules?
Make the rule not updateable at all?
Or create the rules we can? (i think this is
the correct)I seem to be in the minority here. But I think
creating complex rules to fiddle with the updates
to translate them to the underlying tables is the
wrong approach.I think you want to extend the SQL syntax to allow
updating views, and implement plan nodes and
executor functionality to handle them.
What if someone want his views to be readonly? with
rules he can just drop rule. In the approach you
mention he cannot.
So things like this works:
UPDATE (SELECT id,val FROM t) SET val=0 where id <
100
???? You really do things like that??? For what?? I'm
asking because i do not know any situation when it
becomes usefull.
Views, conceptually, should have the same behavior a
table has, because you can use it to let some people
view part of your info without letting them touch the
table. Sometimes you need they can update the fields
they can see, but then how u can prevent them touching
other fields they have no rights to? Updateable views
are handy for that.
In your example is obvious that you can access to the
t table, why not do the update directly?? Besides,
this enforce to create privileges per columns rather
than per table.
Then the rules you create on the views are just like
the rules for SELECT, they simply mechanically
replace the view with the view definition.I think this is the right approach because:
a) I think creating the general rules to transform
an update into an update on the underlying table
will be extremely complex, and you'll only ever
be
able to handle the simplest cases. By handling
the view at planning time you'll be able to
handle arbitrarily complex cases limited only by
whether you can come up with reasonable
semantics.
I don't think is *extremely complex* to create the
rules; but yes, there will be limitations.
b) I think it's aesthetically weird to have
functionality that's only accessible via creating
DDL objects and then using them, and not
accessible directly in a single SQL DML command.
Ie, it would be strange to have to create
a "temporary view" just in order to execute an
update because there's no equivalent syntax
available for use directly.
????
alter table (SELECT id,val FROM t)
alter column val set default 3;
????
General Restrictions!!!
---------------------------
- The column target list holds column fields only,
that are retrieved from one base relation / view
only. (NO joined views).I know there are other uses for updatable views (eg
implementing column-based security policies) but the
_only_ reason I ever found them useful in Oracle
was precisely for joined views.
The NOTE i included in my last post says that oracle
do that with user_updateable_columns view and i
suggest the creation (or the extension of
pg_attribute) of a catalog to implement this. And i
state that can be useful to create joined updateable
views.
They're the Oracle blessed method for achieving the
same performance win as Postgres's FROM clause.So in Oracle you can do:
UPDATE (select a.val as newval, b.b_id, b.val from
a,b where a.b_id = b.b_id) SET val = newval
I think Postgres's UPDATE ... FROM is a lot more clear
to understand.
regards,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Informaci�n de Estados Unidos y Am�rica Latina, en Yahoo! Noticias.
Vis�tanos en http://noticias.espanol.yahoo.com
Greg Stark <gsstark@mit.edu> writes:
I think you want to extend the SQL syntax to allow updating views, and
implement plan nodes and executor functionality to handle them. So things
like this works:
UPDATE (SELECT id,val FROM t) SET val=0 where id < 100
Then the rules you create on the views are just like the rules for SELECT,
they simply mechanically replace the view with the view definition.
I think this is the right approach because:
a) I think creating the general rules to transform an update into an update on
the underlying table will be extremely complex, and you'll only ever be
able to handle the simplest cases. By handling the view at planning time
you'll be able to handle arbitrarily complex cases limited only by whether
you can come up with reasonable semantics.
Please provide an existence proof. I don't really see any basis for the
claim that this will be simpler to implement --- the semantic problems
will be the same either way.
regards, tom lane