Re: Do Views execute underlying query everytime ??

On 6/21/05, PFC <lists(at)boutiquenumerique(dot)com> wrote:
...
> In your case I don't think that is the solution, because you do big
> updates. With triggers this would mean issuing one update of your
> materialized view per row in your big update. This could be slow.
>
> In this case you might want to update the cache table in one request
> rather than doing an awful lot of updates.
>
> So you have two solutions :
>
> 1- Junk it all and rebuild it from scratch (this can be faster than it
> seems)
> 2- Put the rows to be added in a temporary table, update the cache table
> considering the difference between this temporary table and your big
> table, then insert the rows in the big table.
>
> This is the fastest solution but it requires a bit more coding (not THAT
> much though).
>
Amit,

I understand your desire to not need any manual intervention...

I don't know what OS you use, but here are two practical techniques
you can use to achieve the above solution suggested by PFC:

a: If you are on a Unix like OS such as Linux of Free BSD you have the
beautiful cron program that will run commands nightly.

b: If you are on Windows you have to do something else. The simplest
solution I've found is called "pycron" (easily locatable by google)
and is a service that emulates Unix cron on windows (bypassing a lot
of the windows scheduler hassle).

Now, using either of those solutions, let's say at 6:00 am you want to
do your batch query.

1. Put the queries you want into a text file EXACTLY as you would type
them using psql and save the text file. For example, the file may be
named "create_mat_view.txt".
2. Test them by doing this from a command prompt: psql dbname <
create_mat_view.txt
3. Create a cron entry to run the command once a day, it might look like this:
0 6 * * * /usr/bin/psql dbname < /home/admin/create_mat_view.txt
or maybe like this:
0 6 * * * "C:\Program Files\PostgreSQL\8.0\psql.exe" dbname <
"C:\create_mat_view.txt"

I hope this helps,
--
Matthew Nuzum
www.bearfruit.org