Re: Vacuum with Views

On Wed, Aug 03, 2005 at 04:09:06PM -0400, John J. Urbaniak wrote:
> Michael Fuhr wrote:
> >Could elaborate on how vacuum "seems to mess up" your views? What
> >are you doing, what are you expecting to happen, and what actually
> >does happen?
> >
> I am not sure. Somehow my views were destroyed. I have a Work Order
> table, and a Resources table (many Resources to one Work Order). These
> tables are joined by the Work Order number into a View, which I call
> ActiveWOs. I have a print module that prints from this view.

By "destroyed" do you mean the views disappeared, as though they
had been dropped? If so, do you have statement logging enabled so
you can see if a DROP VIEW command was executed?

> The print module worked, then all of a sudden stopped working. It
> caused a crash in my system which required me to reconnect to PGS, and
> maybe also to shut down the server and restart it.

Did the operating system crash, or just a particular application?
When you refer to "server" do you mean the entire box, or just a
particular server process? In any case, if the absence of a database
view has such adverse effects, then perhaps some part of the system
could use better error checking.

> The only thing I remember doing to this was use a vacuum procedure in a
> backup utility I am writing.

Can you reproduce the problem by doing whatever you think caused
it the first time?

> Can I be sure that if I delete some records from some tables, then
> vacuum the database, that views involving those tables are preserved?

Vacuuming isn't supposed to have damaging effects like dropping
views, and it certainly doesn't in ordinary operation or we'd hear
more reports about it. Are you vacuuming the entire cluster
regularly? Have you had any operating system or hardware problems
that might have damaged some disk pages? Are there any unusual
errors in your PostgreSQL logs?

What version of PostgreSQL are you using, and on what platform?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/