From: Matthew Nuzum <mattnuzum@gmail.com>
To: Amit V Shah <ashah@tagaudit.com>
Cc: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org, PFC <lists@boutiquenumerique.com>
Subject: Re: Do Views execute underlying query everytime ??
Date: Tue, 21 Jun 2005 12:57:17 -0500
Message-ID: <f3c0b408050621105752054fe6@mail.gmail.com> (raw)
In-Reply-To: <op.ssqb5ymuth1vuj@localhost>
References: <0C072E7CC947D511AC9600A0CC7341200256CF53@vpn.tagaudit.com>
<op.ssqb5ymuth1vuj@localhost>
On 6/21/05, PFC <lists@boutiquenumerique.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mattnuzum@gmail.com, ashah@tagaudit.com, john@arbash-meinel.com, lists@boutiquenumerique.com
Subject: Re: Do Views execute underlying query everytime ??
In-Reply-To: <f3c0b408050621105752054fe6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox