Patch to remove/report orphaned files

Bruce Momjian <pgman@candle.pha.pa.us> writes:

This patch removes old sort files on postmaster startup, and reports
orphaned database directory files during database vacuum.

I like the first part, but not the second. To name just one problem,
it will go berserk after OID wraparound. The "minimum startup OID"
approach would be ugly even if it weren't fundamentally broken.
That O(N^2) search will get a tad tedious with a lot of tables, too.

I really don't think we have a problem that needs fixing in this area;
surely not a problem bad enough to justify a band-aid as ugly as this.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

This patch removes old sort files on postmaster startup, and reports
orphaned database directory files during database vacuum.

I like the first part, but not the second. To name just one problem,
it will go berserk after OID wraparound. The "minimum startup OID"

I don't see a problem with oid wraparound. The code GetMinStartupOid()
initializes with the current oid:

+ min_oid = ShmemVariableCache->nextOid;

The only issue with oid wraparound is that it will not report orphaned
files from the last vacuum to the wraparound time, which seems OK.

approach would be ugly even if it weren't fundamentally broken.

I don't see why it is broken.

That O(N^2) search will get a tad tedious with a lot of tables, too.

Yes, I was going to add a comment about that. Is there a better way, or
should I run some timing tests on a very large number of tables to see
if the loop is significant compared to the total vacuum time?

I really don't think we have a problem that needs fixing in this area;
surely not a problem bad enough to justify a band-aid as ugly as this.

Sorry, I don't see it as that ugly. It was the only way I could think
of.

We don't know problems we have with orphaned files because we don't
detect them now. We know we have left over sort files because people
complain about them, so odds are we have these others out there too.
Seems we at least have to have some way of detecting them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The only issue with oid wraparound is that it will not report orphaned
files from the last vacuum to the wraparound time, which seems OK.

s/seems OK/means that it's completely useless once wrap has occurred/.
Moreover, it will start complaining about freshly-created files whose
pg_class rows aren't committed yet. Checking OID in this way is simply
wrong, and we should not install such a kluge without an overwhelming
reason to do it. I see no overwhelming need for this thing. In fact,
I'm not convinced that there's any need for it.

We don't know problems we have with orphaned files because we don't
detect them now. We know we have left over sort files because people
complain about them, so odds are we have these others out there too.

Two points about that. One, this won't detect them with any
reliability; what it will do is generate false, impossible-to-reproduce
problem reports --- maybe even induce DBAs to manually delete files that
they needed. ("The message says file 543242 is orphan, so I can get
rid of it without checking further.") Two, we don't know that we still
have such a problem in current sources. The orphaned-sort-file reports
that I remember are all from pre 7.0, when we didn't have a mechanism
that would clean them out after elog(ERROR). I don't think there is
evidence to justify that we still need such a detector. We certainly
don't need it bad enough to justify installing a not-quite-correct kluge.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The only issue with oid wraparound is that it will not report orphaned
files from the last vacuum to the wraparound time, which seems OK.

s/seems OK/means that it's completely useless once wrap has occurred/.
Moreover, it will start complaining about freshly-created files whose
pg_class rows aren't committed yet. Checking OID in this way is simply
wrong, and we should not install such a kluge without an overwhelming
reason to do it. I see no overwhelming need for this thing. In fact,
I'm not convinced that there's any need for it.

I don't get it. How would wrap cause it to complain about new files?
It is getting the minimum oid at startup plus the minimum current
counter. Are you saying it wraps while I am checking? That would be a
problem. I need to check that the oid counter is not wrapped before
printing each message.

After a wrap, you would have files greater than the current oid, but
that is not a problem because I would not be looking at them. In fact,
how do we handle wrap with filenames based on oid?

We don't know problems we have with orphaned files because we don't
detect them now. We know we have left over sort files because people
complain about them, so odds are we have these others out there too.

Two points about that. One, this won't detect them with any
reliability; what it will do is generate false, impossible-to-reproduce
problem reports --- maybe even induce DBAs to manually delete files that
they needed. ("The message says file 543242 is orphan, so I can get
rid of it without checking further.") Two, we don't know that we still
have such a problem in current sources. The orphaned-sort-file reports
that I remember are all from pre 7.0, when we didn't have a mechanism
that would clean them out after elog(ERROR). I don't think there is
evidence to justify that we still need such a detector. We certainly
don't need it bad enough to justify installing a not-quite-correct kluge.

Certainly we have cases where these files will be created no matter how
hard we check. Power off during table creation should produce them,
right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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