Обсуждение: 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?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> > 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
> > > 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, Pennsylvania19026
> -----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
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