Re: Vacuum with Views

Michael Fuhr wrote:

>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?
>

No, I did not have logging enabled. On my OS (eCS) redirecting the log
messages to go to a file doesn't work and I don't want to see all those
messages popping up in stderr, so I turned off most logging. But the
only place I have a DROP VIEW command is in the module that builds the
views.

>>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?
>

By "server" I meant the PGS server, not the OS.

>>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?
>

I re-ran my backup procedure, which vacuums the database. It did not
reproduce the problem, so it must have been something else.

>>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.
>

This is what I wanted to know; I will look elsewhere for what caused the
original problem.

>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?

>
>
Version 8.0, on eComStation 1.1, with (Object) Rexx as the programming
language.

John