Re: Database query: Notification about change? - Mailing list pgsql-sql

Bruno Wolff III wrote:

>On Mon, Dec 05, 2005 at 19:22:22 +0100,
>  Erik Sigra <sigra@kth.se> wrote:
>  
>
>>Hi,
>>I plan to develop an application that is somewhat like a spreadsheet
>>with cells containing formulas. When a cell value is changed, things
>>must be updated. But the formulas can contain database queries, which
>>means that the cell has to be notified when the database changes in such 
>>a way that the result of the query changes. How is this done? I would 
>>really like to avoid recalculating the whole thing for each change to 
>>the database.
>>
>>I looked in my database book and read about materialized views and 
>>triggers. It seems like I should do the following:
>>1. Make a materialized view from the query.
>>2. Add a trigger for changes to this view.
>>3. Make the trigger notify the application program when it is trigged.
>>
>>Would this be possible? (I was planning to use Qt for application 
>>programming and database access.)
>>    
>>
>
>Postgres provides the NOTIFY command
>(http://developer.postgresql.org/docs/postgres/sql-notify.html)
>and you could use that in appropiate triggers to let your application know
>that it needs to refresh that values in at least some cells.
>You shouldn't need to use materialized views though. Just put the triggers
>on the base tables.
>
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:   select distinct t1.person_id--, t1.sport_id, t2.sport_id   from       (select distinct
sport_id,person_id        from            (--  hitta sporten för varje lopp_match             select *             from
               (select lopp_match_id, sport_id                  from                      ensamlopp_match
      natural join                      ensamgrentävling                      natural join
grentävling                     natural join                      gren)                 as t                 union
       (select lopp_match_id, sport_id                  from                      laglopp_match
naturaljoin                      laggrentävling                      natural join                      grentävling
natural                     join gren))            as t         natural join         resursbokning         natural join
       personresurs         natural join         resurstyp        where resurstyp_namn = 'domare')       as t1,
(selectdistinct sport_id, person_id        from            (--  hitta sporten för varje lopp_match             select *
           from                 (select lopp_match_id, sport_id                  from
ensamlopp_match                     natural join                      ensamgrentävling                      natural
join                     grentävling                      natural join                      gren)                 as t
              union              (select lopp_match_id, sport_id                  from
laglopp_match                     natural join                      laggrentävling                      natural join
                 grentävling natural                      join gren))            as t         natural join
resursbokning        natural join         personresurs         natural join         resurstyp        where
resurstyp_namn= 'domare')       as t2   where t1.sport_id != t2.sport_id and t1.person_id = t2.person_id;
 

? (Example query taken from a university course project.) I do not want
the application program to do anything more complex with the query than
adding "create view <some-name> as" in front of it and then add some
trigger or whatever.

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
might look like this:
select sum(price * fraction_deductible) from yearxxxx_expenditures;

Thanks. I hope it is possible to understand what I wrote eventhough I do
not know that much about practical database programming.
Erik