Re: BUG #5038: WAL file is pending deletion in pg_xlog folder, this interferes with WAL archiving. - Mailing list pgsql-bugs

For those of you who are still looking at this, I tried to reproduce the is=
sue by holding one of the WAL files open with another program (just opened =
it with the cygwin build of less.exe for windows).  That didn't do the tric=
k.  It prevented unlink or rename from working at all.  I wrote a program (=
open.exe) that opens the file using pgwin32_open() and passed in the same p=
arameters that postgres uses when opening a WAL file.  That allowed the fil=
e to be renamed.  And, when deleted, the open file went into the pending de=
letion state.

I used open.exe to hold onto a WAL file that was going to be recycled.  The=
 recycling worked, but what is going to happen down the road when the handl=
e is released, leaving a gap in the WAL file sequence.  Or if it is not rel=
eased, when a backend tries to open the WAL file and does not have access t=
o it?

When open.exe was holding onto a WAL file that was going to be deleted, the=
 deletion worked.  The file went into the deletion pending state.  The arch=
ive status for the WAL file went through the .ready =3D=3D> .done =3D=3D> {=
no status file} =3D=3D> .ready sequence.  At that point Postgres repeatedly=
 tries to archive the WAL file.

I reported earlier that I believe postgres leaked the file handle to the WA=
L file.  I don't believe that is the case.  We have a process that only che=
cks data in the database for integrity.  It is only reading.  I think it op=
ened the WAL file initially, perhaps the backend had some maintenance work =
to do when that session started and had to write something to the WAL and t=
hen never moved on to a new one.

Now that I can reproduce the pending deletion case, I'm working on code to =
detect it reliably and, hopefully, efficiently.

-Luke
> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Luke Koops
> Sent: Monday, September 07, 2009 4:30 PM
> To: 'Tom Lane'; Heikki Linnakangas
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion
> in pg_xlog folder, this interferes with WAL archiving.
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Monday, September 07, 2009 1:17 PM
> > To: Heikki Linnakangas
> > Cc: Luke Koops; pgsql-bugs@postgresql.org
> > Subject: Re: [BUGS] BUG #5038: WAL file is pending deletion
> in pg_xlog
> > folder, this interferes with WAL archiving.
> >
> > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> > > Perhaps we should try to close the old WAL file sooner.
> >
> > There is zero hope of making that work.  What we probably
> need to do
> > is fix the code that scans pg_xlog so that it ignores files
> that are
> > pending deletion.  (I assume there's some way to find that out on
> > Windows.)
> On *nux systems, unlink removes the link from the directory.
> When other processes get a directory listing, the file will
> no longer be listed.  On Windows, the file name continues to
> show up in directory listings.  The file is in a state called
> pending deletion.  Windows documentation doesn't give a
> specific test for this state.  Perhaps you could use _access().
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> From http://support.microsoft.com/kb/159199
>
> This file is in a state known as pending deletion. This file
> has been deleted, but there are still handles open to it.
> NTFS will wait until all handles to this file are closed
> before updating the index. If an attempt is made to access
> the file, however, NTFS will deny the attempt. Because the
> file is listed in the index, but is effectively deleted, you
> can see the file but you cannot access it.
>
> Windows NT returns an "Access Denied" error message when you
> attempt to manipulate the file. You are not able to view the
> permissions, the owner, or the contents of the file. The file
> does, however, show up in a DIR listing in File Manager and
> in Explorer. This occurs even though the user trying to
> access the file has permissions to the file. Even an
> administrator will be unable to take ownership of this file.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> >                       regards, tom lane
> >
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>