Обсуждение: [ADMIN] Why does the WAL writer sit on completed segments (on Windows)?

Hello,

I just encountered a problem with my archive_command when using 7-Zip to
compress the WAL file. PostgreSQL is 9.6.2, the EDB x64 build.

archive_command is set to a batch file that effectively runs:

     7z a -tgzip M:\y\archive\%f.gz %p

Whenever a segment is ready to be archived, I get three log entries like
this:

     LOG:  archive command failed with exit code 2
     DETAIL:  The failed archive command was: G:\data-9.6\archive.cmd
pg_xlog\000000010000000000000011 000000010000000000000011

     WARNING: The process cannot access the file because it is being
used by another process.

The reason, as Process Monitor shows, is that the WAL writer process
keeps the file open for about one second after the
archive_status\*.ready file has been created. The archiver runs the
archive_command "too early", resulting in the sharing [1] violation seen
above.

It gives up after three attempts and tries again a minute later. That
attempt then succeeds (except for when the checkpointer is _still_
sitting on the file; I've seen that twice now, too).

When 7-Zip starts, it tries to open the file with FILE_SHARE_READ only,
which is sensible for an archiver that wants to prevent other people
changing the input file while it is being read. Since the WAL writer has
it open with write access, the attempt fails.

Is this a known, er, feature, or should I consider it a bug in PostgreSQL?

There is a workaround; it works when I let 7z read the file through an
input redirection:

     7z a -tgzip -si M:\y\archive\%f.gz < %p

This always works, presumably because cmd opens the file with a more
generous share mode.


[1] Nothing to do with file shares; pg_xlog and archive are on the same
     system. Share modes are the reason open files usually cannot be
     deleted on Windows.


Thanks,

--
Christian

Christian Ullrich <chris@chrullrich.net> writes:
> The reason, as Process Monitor shows, is that the WAL writer process
> keeps the file open for about one second after the
> archive_status\*.ready file has been created. The archiver runs the
> archive_command "too early", resulting in the sharing [1] violation seen
> above.

I'm not sure why that happens, but even if we changed the logic inside
walwriter to avoid it, you'd still need to adapt your archiving script
to deal with the case.  The reason is that the walwriter isn't necessarily
the only process writing WAL --- regular backends do it too, depending on
timing.  In such a case a backend would be holding an open-file reference
until the next time it tries to write WAL, which might be awhile.  It's
not really practical to change that behavior; we certainly wouldn't want
to open and close WAL files for every write.

            regards, tom lane

On 2017-04-17 17:28, Tom Lane wrote:

> Christian Ullrich <chris@chrullrich.net> writes:

>> The reason, as Process Monitor shows, is that the WAL writer process
>> keeps the file open for about one second after the
>> archive_status\*.ready file has been created. The archiver runs the
>> archive_command "too early", resulting in the sharing [1] violation seen
>> above.
>
> I'm not sure why that happens, but even if we changed the logic inside
> walwriter to avoid it, you'd still need to adapt your archiving script
> to deal with the case.  The reason is that the walwriter isn't necessarily
> the only process writing WAL --- regular backends do it too, depending on

Such as in my test with pg_switch_xlog(), yes.

> timing.  In such a case a backend would be holding an open-file reference
> until the next time it tries to write WAL, which might be awhile.  It's
> not really practical to change that behavior; we certainly wouldn't want
> to open and close WAL files for every write.

So, just to be sure, you're saying that when the ".ready" file is
created, it is guaranteed that no process will write to a location
within that segment anymore even if it happens to have the file open?
(That's actually completely obvious, but bear with me, please.)

And further, that each such process will close the open file as soon as
it, intending to write WAL, finds out that the next available location
is not within the file?

Thanks,

--
Christian

Christian Ullrich <chris@chrullrich.net> writes:
> So, just to be sure, you're saying that when the ".ready" file is
> created, it is guaranteed that no process will write to a location
> within that segment anymore even if it happens to have the file open?
> (That's actually completely obvious, but bear with me, please.)

Right.

> And further, that each such process will close the open file as soon as
> it, intending to write WAL, finds out that the next available location
> is not within the file?

Yes, I believe it will typically hold the file open until that happens.
(If short of file descriptors, it might close it sooner, but that's
probably not common.)

            regards, tom lane

Magnus Hagander <magnus@hagander.net> writes:
> But all our files are opened with (FILE_SHARE_READ | FILE_SHARE_WRITE |
> FILE_SHARE_DELETE). So shouldn't this allow 7zip (or whatever) to open up
> the file, regardless of us holding it open? (Looking at
> https://msdn.microsoft.com/en-us/library/windows/desktop/aa363874(v=vs.85).aspx
> at least that's how I read it?)

My understanding of what Christian wrote is that 7zip is using flags
that specifically disallow "sharing".  The flags we use will allow other
programs to open the file(s) with default options, but a program that
is deliberately trying to be the sole user of the file can still notice
our opens.

            regards, tom lane

On 2017-04-17 18:55, Tom Lane wrote:

> Magnus Hagander <magnus@hagander.net> writes:

>> But all our files are opened with (FILE_SHARE_READ | FILE_SHARE_WRITE |
>> FILE_SHARE_DELETE). So shouldn't this allow 7zip (or whatever) to open up
>> the file, regardless of us holding it open? (Looking at
>> https://msdn.microsoft.com/en-us/library/windows/desktop/aa363874(v=vs.85).aspx
>> at least that's how I read it?)

No, I'm afraid not. The share modes have to be compatible across all
CreateFile() calls (that result in concurrently open handles). After
Postgres has opened the file with all three share modes, any later calls
must use all three as well.

The share mode you use on your CreateFile() call expresses your
requirement for what others can do to the file while you have it open.
If you successfully open with FILE_SHARE_READ, you are assured that no
one else has the file open for write or delete, and no one _can_ open it
that way, until you close it.

 From that MSDN page:

    When a process uses CreateFile to attempt to open a file that
    has already been opened in a sharing mode (dwShareMode set to a
    valid nonzero value), the system compares the requested access
    and sharing modes to those specified when the file was opened.
    If you specify an access or sharing mode that conflicts with the
    modes specified in the previous call, CreateFile fails.

"[T]hat conflicts with" means "does not allow at least all modes that
have already been requested on existing calls".

> My understanding of what Christian wrote is that 7zip is using flags
> that specifically disallow "sharing".  The flags we use will allow other
> programs to open the file(s) with default options, but a program that
> is deliberately trying to be the sole user of the file can still notice
> our opens.

Not quite; there are no "default options" for that argument of
CreateFile(). The only way for another program to open the
otherwise-open segment file is to specify compatible share modes, in
this case, all of them.

Otherwise, yes, that is correct. There is essentially no way for a
process to keep the fact that it has a file open completely hidden from
others.

At least the potential issue of someone deleting active segment files
(open with FILE_SHARE_DELETE as per above) is one of being on the other
side of this airtight hatchway, to quote Raymond Chen. In a properly set
up cluster, you have to have admin rights to get at the data directory
at all, and then you can also just kill the database server if you want
to delete something.

--
Christian

On 2017-04-17 19:07, Christian Ullrich wrote:
> On 2017-04-17 18:55, Tom Lane wrote:
>
>> Magnus Hagander <magnus@hagander.net> writes:
>
>>> But all our files are opened with (FILE_SHARE_READ | FILE_SHARE_WRITE |
>>> FILE_SHARE_DELETE). So shouldn't this allow 7zip (or whatever) to
>>> open up
>>> the file, regardless of us holding it open? (Looking at
>>> https://msdn.microsoft.com/en-us/library/windows/desktop/aa363874(v=vs.85).aspx
>>>
>>> at least that's how I read it?)
>
> No, I'm afraid not. The share modes have to be compatible across all
> CreateFile() calls (that result in concurrently open handles). After
> Postgres has opened the file with all three share modes, any later calls
> must use all three as well.

Um, no, not exactly. You can open the file _READ|_WRITE as long as no
one else actually has it open for delete (by setting the delete-on-close
flag?).

And here I thought I understood share modes ... sorry.

I just ran through it in procmon again, and with the input redirection,
cmd successfully opens it _READ|_WRITE.

--
Christian