Hi, I've been playing around with the idea of supporting automatically updatable views, and I have a working proof of concept. I've taken a different approach than the previous attempts to implement this feature (e.g., http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php), instead doing all the work in the rewriter, substituting the view for its base relation rather than attempting to auto-generate any rules or triggers.
Basically what it does is this: in the first stage of query rewriting,
just after any non-SELECT rules are applied, the new code kicks in -
if the target relation is a view, and there were no unqualified
INSTEAD rules, and there are no INSTEAD OF triggers, it tests if the
view is simply updatable. If so, the target view is replaced by its
base relation and columns are re-mapped. Then the remainder of the
rewriting process continues, recursively handling any further
non-SELECT rules or additional simply updatable views. This handles
the case of views on top of views, with or without rules and/or
triggers.
Here's a simple example:
CREATE TABLE my_table(id int primary key, val text);
CREATE VIEW my_view AS SELECT * FROM my_table WHERE id > 0;
then any modifications to the view get redirected to underlying table:
EXPLAIN ANALYSE INSERT INTO my_view VALUES(1, 'Test row');
QUERY PLAN
------------------------------------------------------------------------------------------------
Insert on my_table (cost=0.00..0.01 rows=1 width=0) (actual
time=0.208..0.208 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.004..0.004 rows=1 loops=1)
Total runtime: 0.327 ms
(3 rows)
EXPLAIN ANALYSE UPDATE my_view SET val='Updated' WHERE id=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Update on my_table (cost=0.00..8.27 rows=1 width=10) (actual
time=0.039..0.039 rows=0 loops=1)
-> Index Scan using my_table_pkey on my_table (cost=0.00..8.27
rows=1 width=10) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: ((id > 0) AND (id = 1))
Total runtime: 0.090 ms
(4 rows)
EXPLAIN ANALYSE DELETE FROM my_view;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Delete on my_table (cost=0.00..1.01 rows=1 width=6) (actual
time=0.030..0.030 rows=0 loops=1)
-> Seq Scan on my_table (cost=0.00..1.01 rows=1 width=6) (actual
time=0.015..0.016 rows=1 loops=1)
Filter: (id > 0)
Total runtime: 0.063 ms
(4 rows)
The patch is currently very strict about what kinds of views can be
updated (based on SQL-92), and there is no support for WITH CHECK
OPTION, because I wanted to keep this patch as simple as possible.
The consensus last time seemed to be that backwards compatibility
should be offered through a new GUC variable to allow this feature to
be disabled globally, which I've not implemented yet.
I'm also aware that my new function ChangeVarAttnos() is almost
identical to the function map_variable_attnos() that Tom recently
added, but I couldn't immediately see a neat way to merge the two. My
function handles whole-row references to the view by mapping them to a
generic RowExpr based on the view definition. I don't think a
ConvertRowtypeExpr can be used in this case, because the column names
don't necessarily match.
Obviously there's still more work to do but the early signs seem to be
encouraging.
Thoughts?
Regards,
Dean
auto-update-views.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
