Re: Potential RC1-stoppers - Mailing list pgsql-hackers

On Thu, 22 Mar 2001, Tom Lane wrote:

> "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> >> * Joel Burton's report of disappearing files, 3/20.  This is 
> >> real scary, but no one else has reported anything like it.
> 
> > Can please you remind that report?
> 
> It's the "pg_inherits: not found, but visible" thread in pghackers
> on 3/20 and 3/21.  Briefly, he had two separate occurrences of a table
> file disappearing while the pg_class row remained (and he hadn't
> tried to delete it, either).  The only idea I can come up with is that
> a removal of some other table removed the wrong file.  Ugly.
> 
> Joel, can you give us any more info?  Do you have a postmaster log of
> the queries that were issued while this was happening?

Sorry; I've been at client sites for the past day.

I rebooted my machine, and it didn't happen again that night. Yesterday,
my staff reinstalled Pg straight from the CVS but without (!) tarring up
the old Pg install, so I'm afraid I don't have any logs. I run Pg w/debug
switches on my development machine; this machine did not have such.

After rebooting, and since reinstalling Pg
beta-6-or-whatever-we're-at-now, it hasn't happened again. I'm afraid I
can't think of anything unusual about the PC.

Unbranded, decent-quality components AMD K6-III/550
256MB RAM
Linux-Mandrake 7.2 w/the secure version of the kernel (2.2.17, IIRC)
Pg beta4


I don't have a log, but do have the query that was issued, multiple times,
overlapping:

SELECT * FROM zope_facinst LIMIT 1000;

where zope_facinst is the view

SELECT DISTINCT ON (t.lname,                    t.fname,                    c.fulltitle, c.classcode,
t.trainid)        c.classcode,         t.trainid,         scw_namecode(t.fname, t.lname) AS namecode,
t.fullname,        c.fulltitle,         c.descrip,         t.descripshort AS train_descripshort,         c.descripshort
ASclass_descripshort 
 
FROM     vlkpclass c,         vlkptrain t,         tblinst i,         trelinsttrain it 
WHERE    (((c.classcode = i.classcode) AND           (i.instid = it.instid)) 
AND       (it.trainid = t.trainid)) 
ORDER BY t.lname,         t.fname,        c.fulltitle,         c.classcode,         t.trainid;

So it's pretty complicated, but not terrible.

The classes starting w/'t' are tables; those starting with 'v' are
views; none of the views are too complex.

scw_namecode() is a simple pl/pgsql routine that just joins the strings
together in a particular way.

There are about 400 records returned by the view.



EXPLAIN for it looks like this:

reg2=# explain select * from zope_Facinst;
NOTICE:  QUERY PLAN:

Subquery Scan zope_facinst  (cost=339.93..356.42 rows=132 width=141) ->  Unique  (cost=339.93..356.42 rows=132
width=141)      ->  Sort  (cost=339.93..339.93 rows=1319 width=141)             ->  Merge Join  (cost=261.33..271.56
rows=1319width=141)                   ->  Sort  (cost=223.52..223.52 rows=597 width=92)                         ->
MergeJoin  (cost=131.72..195.99 rows=597
 
width=92)                               ->  Index Scan using tblinst_pkey on
tblinst i  (cost=0.00..53
.69 rows=769 width=16)                               ->  Sort  (cost=131.72..131.72 rows=78
width=76)                                     ->  Merge Join  (cost=52.15..129.28
rows=78 width=76)                                           ->  Merge Join
(cost=52.15..59.96 rows=976 width=
68)                                                 ->  Sort
(cost=27.28..27.28 rows=316 width=
40)                                                       ->  Seq Scan on
tblpers p  (cost=0.00.
.14.16 rows=316 width=40)                                                 ->  Sort
(cost=24.87..24.87 rows=309 width=
28)                                                       ->  Seq Scan on
tbltrain t  (cost=0.00
..12.09 rows=309 width=28)                                           ->  Index Scan using
trelinsttrain_trainid_idx on
trelinsttrain it  (cost=0.00..42.75 rows=795 width=8)                   ->  Sort  (cost=37.82..37.82 rows=221 width=49)
                       ->  Seq Scan on tblclass c  (cost=0.00..29.21
 
rows=221 width=49)



I can provide a dump of the database if anyone would like, or copies of
the Zope scripts (very, very simple: they just call the ZSQL method
'select * from zope_facinst limit 1000')


Sorry I can't provide much more, and, yes, I know it sucks to have a
problem I can't replicate. Err. Computers can be like that.

I hope this helps.


-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington