Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-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

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-01-23 18:33:21
Subject: Re: AIX 4.3 getaddrinfo busted
Previous:From: Kevin GrittnerDate: 2009-01-23 18:17:17
Subject: Re: Controlling hot standby

pgsql-committers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group