Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle - Mailing list pgsql-hackers

petere@postgresql.org (Peter Eisentraut) writes:
> Automatic view update rules

This patch is still a few bricks shy of a load ...  within a few moments
of starting to look at it I'd noticed two different failure conditions

regression=# \d box_tbl Table "public.box_tbl"Column | Type | Modifiers 
--------+------+-----------f1     | box  | 

regression=# create view v1 as select * from box_tbl;
ERROR:  could not identify an equality operator for type box
regression=# create view v1 as select box_tbl from box_tbl;
server closed the connection unexpectedly       This probably means the server terminated abnormally       before or
whileprocessing the request.
 
The connection to the server was lost. Attempting reset: Failed.

and I'm sure there are quite a few more.  These things are not that hard
to fix in themselves, but what disturbs me more is the basic nature of
the generated rules.

regression=# create view v1 as select * from int8_tbl where q1 > 1000;
NOTICE:  CREATE VIEW has created automatic view update rules
CREATE VIEW
regression=# \d v1     View "public.v1"Column |  Type  | Modifiers 
--------+--------+-----------q1     | bigint | q2     | bigint | 
View definition:SELECT int8_tbl.q1, int8_tbl.q2  FROM int8_tbl WHERE int8_tbl.q1 > 1000;
Rules:"_DELETE" AS   ON DELETE TO v1 DO INSTEAD  DELETE FROM int8_tbl WHERE (old.q1 IS NULL AND int8_tbl.q1 IS NULL OR
old.q1= int8_tbl.q1) AND (old.q2 IS NULL AND int8_tbl.q2 IS NULL OR old.q2 = int8_tbl.q2) RETURNING old.q1,
old.q2"_INSERT"AS   ON INSERT TO v1 DO INSTEAD  INSERT INTO int8_tbl (q1, q2)  VALUES (new.q1, new.q2) RETURNING
new.q1,new.q2"_UPDATE" AS   ON UPDATE TO v1 DO INSTEAD  UPDATE int8_tbl SET q1 = new.q1, q2 = new.q2 WHERE (old.q1 IS
NULLAND int8_tbl.q1 IS NULL OR old.q1 = int8_tbl.q1) AND (old.q2 IS NULL AND int8_tbl.q2 IS NULL OR old.q2 =
int8_tbl.q2)RETURNING new.q1, new.q2
 

This has got two big problems.  The first is the incredibly inefficient
nature of the resulting plans, e.g,

regression=# explain update v1 set q1 = q1 + 1000 where q1 = 42;
                                                                 QUERY PLAN
                                                                      
 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------Nested Loop  (cost=0.00..2.20 rows=1 width=22)  Join Filter: ((((public.int8_tbl.q1
ISNULL) AND (public.int8_tbl.q1 IS NULL)) OR (public.int8_tbl.q1 = public.int8_tbl.q1)) AND (((public.int8_tbl.q2 IS
NULL)AND (public.int8_tbl.q2 IS NULL)) OR (public.int8_tbl.q2 = public.int8_tbl.q2)))  ->  Seq Scan on int8_tbl
(cost=0.00..1.07rows=1 width=16)        Filter: ((q1 > 1000) AND (q1 = 42))  ->  Seq Scan on int8_tbl  (cost=0.00..1.05
rows=5width=22)
 
(5 rows)

If we ship this, we will be a laughingstock.  The other problem (which
is related to the first failure condition exhibited above) is the
assumption that the default btree equality operator for a data type is
"real" equality.  Even if it exists, that's a bad assumption --- it
falls down for float8 and numeric let alone any more-interesting
datatypes such as the geometric types.

It would probably be better if we insisted that the view's base be a
plain relation and used ctid equality in the update rules (which will in
turn require supporting TidScan as an inner join indexscan, but that's
doable).

In short, I don't feel that this was ready to be applied.  It's probably
fixable with a week or so's work, but do we want to be expending that
kind of effort on it at this stage of the release cycle?
        regards, tom lane