DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=SMBZRO+yrXiuVXHILeRwA1P0lqfxckkDiqbIrSinkSBATGbpBG4OZq0pVS9RbxmwYbfprnKdLASkmQhHWwr0I7kNPf+neBL4FjJ86Qe7NH/dDnVwR/KHvID8PvimsU1UomJHjsxJZ4M0MqXAXiiy/dQT9Z11RnftnJyeJQQf3gU=
Message-ID: <f3c0b408050621105752054fe6@mail.gmail.com>
Date: Tue, 21 Jun 2005 12:57:17 -0500
From: Matthew Nuzum <mattnuzum@gmail.com>
Reply-To: newz@bearfruit.org
To: Amit V Shah <ashah@tagaudit.com>
Subject: Re: Do Views execute underlying query everytime ??
Cc: John A Meinel <john@arbash-meinel.com>,
	pgsql-performance@postgresql.org, PFC <lists@boutiquenumerique.com>
In-Reply-To: <op.ssqb5ymuth1vuj@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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.
>=20
> In this case you might want to update the cache table in one request
> rather than doing an awful lot of updates.
>=20
> So you have two solutions :
>=20
> 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.
>=20
> This is the fastest solution but it requires a bit more coding (not THAT
> much though).
>=20
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 th=
is:
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,
--=20
Matthew Nuzum
www.bearfruit.org