X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 897229DCAF0 for ; Mon, 5 Dec 2005 23:30:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24546-09 for ; Mon, 5 Dec 2005 23:30:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id CD29F9DCAB1 for ; Mon, 5 Dec 2005 23:30:34 -0400 (AST) Received: (qmail 17444 invoked by uid 500); 6 Dec 2005 03:35:03 -0000 Date: Mon, 5 Dec 2005 21:35:03 -0600 From: Bruno Wolff III To: Erik Sigra Cc: pgsql-sql@postgresql.org Subject: Re: Database query: Notification about change? Message-ID: <20051206033503.GA13660@wolff.to> Mail-Followup-To: Bruno Wolff III , Erik Sigra , pgsql-sql@postgresql.org References: <4394855E.6060201@kth.se> <20051205204053.GB19356@wolff.to> <4394E2A2.1050301@kth.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4394E2A2.1050301@kth.se> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.023 required=5 tests=[AWL=0.023] X-Spam-Score: 0.023 X-Spam-Level: X-Archive-Number: 200512/34 X-Sequence-Number: 23576 On Tue, Dec 06, 2005 at 02:00:18 +0100, Erik Sigra wrote: > Thanks for the hint! I thought I need to put the triggers on > materialized views. If I would have to put them on the base tables, the > application program would have to understand the query to figure out > which tables it uses. I really do not want to make a parser for SQL. > What if the user of the spreadsheet-like application enters a formula > containing a query like this: I would say, that this is a really odd thing to do. If you are going to allow any old user written query, you are going to find it hard to update the cells at the time the data changes. It might be better to have the user hit a refresh button or to have timed refreshes. > And I think it will be much more efficient to watch a materialized view > than to rerun the query whenever one of the base tables change. That is > what materialized view optimization is about, right? A typical query Its pretty much the same thing. The materialized view is going to be maintained by triggers. Postgres doesn't have a built in way to automate this, so the application would have to parse the queries and create the trigger functions.