On Mon, Aug 14, 2017 at 8:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It would be possible to have orphaned non-temp tables if you'd suffered a crash during the transaction that created those tables. Ordinarily a newly-created table file wouldn't be that large, but if your workflow created tables and shoved boatloads of data into them in the same transaction, it's not so hard to see this becoming an issue.
I think the working theory is that these were very like a number of very large (multi-hundred-GB materialised views).
The core problem with zapping non-temp table files is that you can't do that unless you're sure you have consistent, up-to-date pg_class data that nobody else is busy adding to. It's hard to see an external application being able to do that safely. You certainly can't do it at the point in the postmaster startup cycle where we currently do the other things --- for those, we rely only on filesystem naming conventions to identify what to zap.
Yeah that occurred to me. At this point I would settle for something I could run with Postgres in single user mode. Although that is very far from ideal. So what I wonder is if at least a short-term solution might be a utility that starts Postgres in single user mode and we insist that PostgreSQL is otherwise not running before the run.
I am certainly not feeling qualified at present for more advanced solutions but that I might be able to do.