Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B62AC6326E2 for ; Fri, 9 Jan 2009 08:21:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43257-07 for ; Fri, 9 Jan 2009 08:20:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from dd1514.kasserver.com (dd1514.kasserver.com [85.13.128.107]) by mail.postgresql.org (Postfix) with ESMTP id EC1746326DD for ; Fri, 9 Jan 2009 08:20:58 -0400 (AST) Received: from [10.211.55.6] (dslb-088-077-003-025.pools.arcor-ip.net [88.77.3.25]) by dd1514.kasserver.com (Postfix) with ESMTP id 1200418740DF; Fri, 9 Jan 2009 13:20:54 +0100 (CET) Date: Fri, 09 Jan 2009 13:20:57 +0100 From: Bernd Helmle To: Bernd Helmle , Jaime Casanova cc: Robert Haas , pgsql-hackers@postgresql.org Subject: Re: WIP: Automatic view update rules Message-ID: In-Reply-To: <61043.91.48.33.48.1230474598.squirrel@tooney.at.xencon.de> References: <2849137C693B65CC8E86411C@imhotep.credativ.de> <94CF655A8D0DC7D5B4B81D8B@imhotep.credativ.de> <603c8f070811112006q906b3a6o59a66228b7fc997@mail.gmail.com> <7F84532C73CB3279DE8436E0@imhotep.credativ.de> <603c8f070811131344n5c087057p11f94780dae80885@mail.gmail.com> <603c8f070811252043r75db3e83v2eba1611452ee62e@mail.gmail.com> <3073cc9b0812250943x23547116ud956d8ab111e55bf@mail.gmail.com> <61043.91.48.33.48.1230474598.squirrel@tooney.at.xencon.de> X-Mailer: Mulberry/4.0.8 (Linux/x86) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/676 X-Sequence-Number: 131320 --On Sonntag, Dezember 28, 2008 15:29:58 +0100 Bernd Helmle wrote: >> maybe the better solution is to not allow such a view to be updatable >> > > Yes, it seems we have to check for target lists having negative attnums in > checkTree(). Another solution would be to simply ignore those columns > (extract them from the target list and include all updatable columns > only). While looking at this it turned out that the problem of updatability is more complex than i originally thought: Consider a relation tree of the following views/relations: View1 -> View2 -> View3 -> Table1 That means, View1 consists of View2 and so on. What happens now if someone is going to change View3, so that it's not updatable anymore? What the patch actually does is, scanning all relations/views involved in a current view (and cascading updates) und reject update rules as soon as it finds more than one relation within a view definition. Unfortunately this seems not to be enough, we had really check all involved views for updatability recursively. The infrastructure for this is already there, but i wonder if it could be made easier when we are going to maintain a separate is_updatable flag somewhere in the catalog, which would make checking the relation tree for updatability more easier. Comments? -- Thanks Bernd