On 6/21/05, PFC <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly