Thread: 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
Bug report on views [was: drop view seems to have made database unusable]
From
Martin Weinberg
Date:
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