Re: Pinned files at Windows - Mailing list pgsql-hackers

On 27.05.2019 12:26, Konstantin Knizhnik wrote:
> Hi, hackers.
>
> There is the following problem with Postgres at Windows: files of 
> dropped relation can be blocked for arbitrary long amount of time.
> Such behavior is caused by two factors:
> 1. Windows doesn't allow deletion of opened file.
> 2. Postgres backend caches opened descriptors and this cache is not 
> updated if backend is idle.
>
> So the problem can be reproduced quite easily: create some table in 
> once client, then drop it in another client and try to do something 
> with relation files.
> Segments of dropped relation are visible but any attempt to copy this 
> file is rejected.
> And this state persists until you perform some command in first client.
>
> I wonder if we are going to address this windows specific issue?
> It will cause problems with file backup utilities which are not able 
> to copy this file.
> And situation when backend can be idle for long amount of time are not 
> so rare.
>

I have investigated the problem more and looks like the source of the 
problem is in pgwin32_safestat function:

int
pgwin32_safestat(const char *path, struct stat *buf)
{
     int            r;
     WIN32_FILE_ATTRIBUTE_DATA attr;

     r = stat(path, buf);
     if (r < 0)
     {
         if (GetLastError() == ERROR_DELETE_PENDING)
         {
             /*
              * File has been deleted, but is not gone from the 
filesystem yet.
              * This can happen when some process with FILE_SHARE_DELETE 
has it
              * open and it will be fully removed once that handle is 
closed.
              * Meanwhile, we can't open it, so indicate that the file just
              * doesn't exist.
              */
             errno = ENOENT;
             return -1;
         }

         return r;
     }

     if (!GetFileAttributesEx(path, GetFileExInfoStandard, &attr))
     {
         _dosmaperr(GetLastError());
         return -1;
     }

     /*
      * XXX no support for large files here, but we don't do that in 
general on
      * Win32 yet.
      */
     buf->st_size = attr.nFileSizeLow;

     return 0;
}

Postgres is opening file with FILE_SHARE_DELETE  flag which makes it 
possible to unlink opened file.
But unlike Unixes, the file is not actually deleted. You can see it 
using "dir" command.
And stat() function also doesn't return error in this case:

https://stackoverflow.com/questions/27270374/deletefile-or-unlink-calls-succeed-but-doesnt-remove-file

So first check in  pgwin32_safestat (r < 0) is not working at all: 
stat() returns 0, but subsequent call of GetFileAttributesEx
returns 5 (ERROR_ACCESS_DENIED).
It seems to me that pgwin32_safestat function should be rewritten in 
this way:

int
pgwin32_safestat(const char *path, struct stat *buf)
{
     int            r;
     WIN32_FILE_ATTRIBUTE_DATA attr;

     r = stat(path, buf);
     if (r < 0)
         return r;

     if (!GetFileAttributesEx(path, GetFileExInfoStandard, &attr))
     {
         errno = ENOENT;
         return -1;
     }

     /*
      * XXX no support for large files here, but we don't do that in 
general on
      * Win32 yet.
      */
     buf->st_size = attr.nFileSizeLow;

     return 0;
}


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company