
Just has a support call where the client couldn't drop a table, giving him
a 'No such file or directory' error...yet a \d showed him that the table
existed...

To "fix" the problem, we got him to shutdown his server, touch the file
that it says is missing, bring the server back up and then drop
it...which, of course, succeeded...

But...does it make sense to error-out in this case?  The user wants to get
rid of the table, the table is already gone physically, just not
virtually...so why not just get rid of the virtual entries also?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 



Fri, 7 Jan 2000 16:44:16 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Table drop that fails ... "No such file or directory"
> 
> Just has a support call where the client couldn't drop a table, giving him
> a 'No such file or directory' error...yet a \d showed him that the table
> existed...
> 
> To "fix" the problem, we got him to shutdown his server, touch the file
> that it says is missing, bring the server back up and then drop
> it...which, of course, succeeded...
> 
> But...does it make sense to error-out in this case?  The user wants to get
> rid of the table, the table is already gone physically, just not
> virtually...so why not just get rid of the virtual entries also?

It shows something very strange happened to him.  We don't want this
kind of thing to just happen.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 7 Jan 2000 16:05:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Table drop that fails ... "No such file or directory"
On Fri, 7 Jan 2000, Bruce Momjian wrote:

> > But...does it make sense to error-out in this case?  The user wants to get
> > rid of the table, the table is already gone physically, just not
> > virtually...so why not just get rid of the virtual entries also?
> 
> It shows something very strange happened to him.  We don't want this
> kind of thing to just happen.

Okay...what should be done?  How do you trace something like this back?

The scenario for this particular table, as it was explained to me, was
that its the result of a join of two other tables...they find that its
easier to do teh join into one table periodically and use that for
selects, then doing SELECT/JOINS on the fly ... my thought was that what
may have happened is they ran out of disk space on the JOIN, the file was
removed, but not the traces in the systems files, is this a possibility?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Fri, 7 Jan 2000 17:13:50 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Table drop that fails ... "No such file or directory"
> On Fri, 7 Jan 2000, Bruce Momjian wrote:
> 
> > > But...does it make sense to error-out in this case?  The user wants to get
> > > rid of the table, the table is already gone physically, just not
> > > virtually...so why not just get rid of the virtual entries also?
> > 
> > It shows something very strange happened to him.  We don't want this
> > kind of thing to just happen.
> 
> Okay...what should be done?  How do you trace something like this back?

That is the big question.  We need to hear about these problems, because
the represent something very strange happening.  Somehow, the physical
table was deleted, but it still existed in the system tables.

> 
> The scenario for this particular table, as it was explained to me, was
> that its the result of a join of two other tables...they find that its
> easier to do teh join into one table periodically and use that for
> selects, then doing SELECT/JOINS on the fly ... my thought was that what
> may have happened is they ran out of disk space on the JOIN, the file was
> removed, but not the traces in the systems files, is this a possibility?

Maybe a SELECT INTO failed.  You would think it could delete the entries
too, or at least the transaction that created the table would be marked
as aborted.

Not sure how to debug that one.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 7 Jan 2000 16:16:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Table drop that fails ... "No such file or directory"
On Fri, 7 Jan 2000, Bruce Momjian wrote:

> > On Fri, 7 Jan 2000, Bruce Momjian wrote:
> > 
> > > > But...does it make sense to error-out in this case?  The user wants to get
> > > > rid of the table, the table is already gone physically, just not
> > > > virtually...so why not just get rid of the virtual entries also?
> > > 
> > > It shows something very strange happened to him.  We don't want this
> > > kind of thing to just happen.
> > 
> > Okay...what should be done?  How do you trace something like this back?
> 
> That is the big question.  We need to hear about these problems, because
> the represent something very strange happening.  Somehow, the physical
> table was deleted, but it still existed in the system tables.

Hmmm...problem is, I would think, in most cases it wouldn't be noticed
until a later time, this case as an example...

> > The scenario for this particular table, as it was explained to me, was
> > that its the result of a join of two other tables...they find that its
> > easier to do teh join into one table periodically and use that for
> > selects, then doing SELECT/JOINS on the fly ... my thought was that what
> > may have happened is they ran out of disk space on the JOIN, the file was
> > removed, but not the traces in the systems files, is this a possibility?
> 
> Maybe a SELECT INTO failed.  You would think it could delete the entries
> too, or at least the transaction that created the table would be marked
> as aborted.

Or it could be nothing more then a hard crash of the server :(  

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Fri, 7 Jan 2000 17:59:25 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Table drop that fails ... "No such file or directory"
> > > The scenario for this particular table, as it was explained to me, was
> > > that its the result of a join of two other tables...they find that its
> > > easier to do teh join into one table periodically and use that for
> > > selects, then doing SELECT/JOINS on the fly ... my thought was that what
> > > may have happened is they ran out of disk space on the JOIN, the file was
> > > removed, but not the traces in the systems files, is this a possibility?
> > 
> > Maybe a SELECT INTO failed.  You would think it could delete the entries
> > too, or at least the transaction that created the table would be marked
> > as aborted.
> 
> Or it could be nothing more then a hard crash of the server :(  

That could not mark the transaction that completed the table as
committed.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 7 Jan 2000 17:01:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Table drop that fails ... "No such file or directory"
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of The Hermit
> Hacker
>
> On Fri, 7 Jan 2000, Bruce Momjian wrote:
>
> > > On Fri, 7 Jan 2000, Bruce Momjian wrote:
> > >
> > > > > But...does it make sense to error-out in this case?  The
> user wants to get
> > > > > rid of the table, the table is already gone physically, just not
> > > > > virtually...so why not just get rid of the virtual entries also?
> > > >
> > > > It shows something very strange happened to him.  We don't want this
> > > > kind of thing to just happen.
> > >
> > > Okay...what should be done?  How do you trace something like
> this back?
> >
> > That is the big question.  We need to hear about these problems, because
> > the represent something very strange happening.  Somehow, the physical
> > table was deleted, but it still existed in the system tables.
>
> Hmmm...problem is, I would think, in most cases it wouldn't be noticed
> until a later time, this case as an example...
>

Isn't it the result of a DROP TABLE failure ?
mdunlink() removes the base file of a relation immediately and
the file couldn't be rollbacked in case of abort.

I have already made it possible to DROP TABLE even though there
aren't base files of relation/indexes in current tree 2 months ago.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


Sat, 8 Jan 2000 08:41:47 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] Table drop that fails ... "No such file or directory"
Hiroshi Inoue wrote:
> > > That is the big question.  We need to hear about these problems, because
> > > the represent something very strange happening.  Somehow, the physical
> > > table was deleted, but it still existed in the system tables.
> >
> > Hmmm...problem is, I would think, in most cases it wouldn't be noticed
> > until a later time, this case as an example...
> >
> 
> Isn't it the result of a DROP TABLE failure ?
> mdunlink() removes the base file of a relation immediately and
> the file couldn't be rollbacked in case of abort.
> 
> I have already made it possible to DROP TABLE even though there
> aren't base files of relation/indexes in current tree 2 months ago.
> 
> Regards.
> 
> Hiroshi Inoue
>

Sounds like it was caused by DDL statements in aborted
transactions to me....

Mike (implicit commit, again) Mascari

Fri, 07 Jan 2000 20:11:06 -0500
Mike Mascari <mascarm@mascari.com>
Re: [HACKERS] Table drop that fails ... "No such file or directory"