Обсуждение: drop view seems to have made database unusable . . help!

Help!

I attempted to create a view like

    create view pscscan as select * from psc, scandat where . . .;

which failed with a message about duplicate entries.  I tried
another with

    create view pscscan2 as select j_m, j_pts_noise from psc, scandat where . . .;

which worked.

I then tried to drop them both and the backend crashed
I tried to vacuum and the vacuum crashed.  Now I can not
connect to the database at all (I deleted the lock file)
Now, postgres seems to create an empty file
"pg_internal.init" and crash when I try to connect.

What happened?  Is there any way to save this? I have nearly
100GB in this database . . . sigh.

--Martin


===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower
University of Massachusetts
Amherst, MA  01003-4525

Folks,

I managed to bail myself out of the "drop view" induced problems
(by copying back the pg_internal.init file, vacuuming the whole
database and then successfully dropping the bad tables/views . . .
yea postgres!!!!  But I still don't understand what happened.)

Howver, I'd like to mention the following repeatable
bugs/problems with views:

I have two tables that I wish to join.  I create the following
view:

    create view testview as select [40 or 50 some odd fields]
    from tab1, tab2,
    where x=x_s and y=y_s and z=z_s;

where x,y,z are in tab1 and x_s, y_s, z_s are in tab2.

This crashes the backend.  If I decrease the number of fields to
20 or so, then I get the "rule set too big" error message.  When
I decrease the number of fields down to 10 or so, the view is
successful.  I've tried both 6.5.1 and 6.5.2.

I've searched the archives and docs for mention of his.  This
problem seems related to some previous reports in this group
but I didn't see any final solution.

My work around is to make the join explicitly when I need it,
which works fine.

BTW, modulo this glitch the >=6.5.1 versions are working great on
my Linux box with a Raid array.  The full database is >100GB.

--Martin

===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower         weinberg@astro.umass.edu
University of Massachusetts         http://www.astro.umass.edu/~weinberg/
Amherst, MA  01003-4525