Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle

petere(at)postgresql(dot)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 while processing 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 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 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 IS NULL) 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.07 rows=1 width=16)
Filter: ((q1 > 1000) AND (q1 = 42))
-> Seq Scan on int8_tbl (cost=0.00..1.05 rows=5 width=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