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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>, Bernd Helmle <mailings(at)oopsware(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Date: 2009-01-23 18:28:27
Message-ID: 15586.1232735307@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

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

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message User Achernow 2009-01-23 18:50:35 libpqtypes - libpqtypes: changed PGchar typedef to be signed char, AIX
Previous Message User Alp 2009-01-23 17:37:04 fb2pg - fb2pg: New Directory

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-01-23 18:33:21 Re: AIX 4.3 getaddrinfo busted
Previous Message Kevin Grittner 2009-01-23 18:17:17 Re: Controlling hot standby