Re: [HACKERS] Orphaned files in base/[oid]

On Wed, Aug 16, 2017 at 7:15 PM, Andres Freund wrote: > > > I think this entirely is the wrong approach. We shouldn't add weird > check commands that require locks on pg_class, we should avoid leaving > the orphaned files in the first place. I've upthread outlined >

Re: [HACKERS] Orphaned files in base/[oid]

On 2017-08-16 14:20:02 +0200, Chris Travers wrote: > So having throught about this a bit more, and having had some real-world > experience with the script now, I have an idea that might work and some > questions to make it succeed. > > My thinking is to add a new form of vacuum called VACUUM

Re: [HACKERS] Orphaned files in base/[oid]

On Mon, Aug 14, 2017 at 2:56 PM, Andres Freund wrote: > I think there are some possibilities to close the gap here. We could > e.g. have .delete_on_crash marker files that get installed > when creating a new persistent relfilenode. If we set up things so they > get deleted

Re: [HACKERS] Orphaned files in base/[oid]

So having throught about this a bit more, and having had some real-world experience with the script now, I have an idea that might work and some questions to make it succeed. My thinking is to add a new form of vacuum called VACUUM FSCK. This would: 1. lock pg_class in exclusive mode (or do I

Re: [HACKERS] Orphaned files in base/[oid]

On Tue, Aug 15, 2017 at 3:32 PM, Tom Lane wrote: > Chris Travers writes: > > I wonder about a different solution. Would it be possible to special > case > > vacuum to check for and remove (or just move to where they can be > removed) > > files when

Re: [HACKERS] Orphaned files in base/[oid]

Chris Travers writes: > I wonder about a different solution. Would it be possible to special case > vacuum to check for and remove (or just move to where they can be removed) > files when vacuuming pg_class? At the point we are vacuuming pg_class, we > ought to be able

Re: [HACKERS] Orphaned files in base/[oid]

There's another side to this and that I am not sure it is a backend crash. Here is what I did to reproduce: 2 virtual disk images: 100mb for main data, 40 MB for WAL. work_mem set to 256MB. The idea is to test different out of space conditions. Create table as ...; drop table; select

Re: [HACKERS] Orphaned files in base/[oid]

On Tue, Aug 15, 2017 at 3:56 AM, Andres Freund wrote: > I think there are some possibilities to close the gap here. We could > e.g. have .delete_on_crash marker files that get installed > when creating a new persistent relfilenode. If we set up things so they > get deleted

Re: [HACKERS] Orphaned files in base/[oid]

On Mon, Aug 14, 2017 at 8:40 PM, Tom Lane 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 >

Re: [HACKERS] Orphaned files in base/[oid]

On 2017-08-14 14:40:46 -0400, Tom Lane wrote: > 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

Re: [HACKERS] Orphaned files in base/[oid]

Chris Travers writes: > On Mon, Aug 14, 2017 at 6:33 PM, Andres Freund wrote: >> I think the fix here is to call RemovePgTempFiles() during >> crash-restarts, instead of just full starts. The previously stated need >> to be able to inspect temp files

Re: [HACKERS] Orphaned files in base/[oid]

On Mon, Aug 14, 2017 at 6:33 PM, Andres Freund wrote: > Hi, > > On 2017-08-14 14:12:22 +0200, Chris Travers wrote: > > Problem: > > The system this came up on is PostgreSQL 9.6.3 and has had repeated > trouble > > with disk space. Querying pg_database_size, as well as du on

Re: [HACKERS] Orphaned files in base/[oid]

Hi, On 2017-08-14 14:12:22 +0200, Chris Travers wrote: > Problem: > The system this came up on is PostgreSQL 9.6.3 and has had repeated trouble > with disk space. Querying pg_database_size, as well as du on the > subdirectory of base/ show total usage to be around 3.8TB. Summing up the > size

Re: [HACKERS] Orphaned files in base/[oid]

On Aug 14, 2017 14:12, "Chris Travers" wrote: Hi all; I am trying to track down a problem we are seeing that looks very similar to bug #12050, and would certainly consider trying to contribute a fix if we agree on one. (I am not sure we can, so absent that, the next

[HACKERS] Orphaned files in base/[oid]

Hi all; I am trying to track down a problem we are seeing that looks very similar to bug #12050, and would certainly consider trying to contribute a fix if we agree on one. (I am not sure we can, so absent that, the next question is whether it makes sense to create a utility to fix the problem