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