Josh Berkus <josh@agliodbs.com> writes:
> On 03/19/2014 10:37 AM, Alvaro Herrera wrote:
>> I wonder about suggesting other versions of ALTER TABLE that can do
>> heap rewrites.
> I don't want to recommend any version of ALTER TABLE until someone
> actually tests it on a corrupted database.
A test would be a good idea, yes. But in principle it ought to work.
> What about simply CREATE TABLE AS SELECT? Presumably that kind of
> manual cleanup would work, no?
Well, it would leave you with a whole lot of error-prone manual cleanup
to do, like repointing foreign key linkages, remaking indexes, etc.
And what's possibly more relevant to this discussion, there's no very
strong reason to believe that it'd result in data that's any cleaner than
the ALTER TABLE way.
Note that if you've already suffered some of the potential indirect
consequences, like duplicate/missing keys, then there isn't going to be
any automatic fix; you're gonna have to clean up the data by hand.
But assuming that that hasn't happened, any seqscan-based data extraction
ought to do the trick; and ALTER TABLE (as long as you avoid the no-op
transformation pitfall) should be as good as other ways, with a lot less
risk of human error than a manual recipe.
regards, tom lane