Thread: Postgres 14.2 Windows can't rename temporary statistics file

Postgres 14.2 Windows can't rename temporary statistics file

From
Ranier Vilela
Date:
Hi,

I've reported this issue, but without success in fixing it.
Now I have installed 14.2 and used in development environment, and
the log still reports:
2022-02-13 18:33:20.502 -03 [7976] LOG:  could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat": Permission denied
2022-02-13 19:33:22.648 -03 [7976] LOG:  could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat": Permission denied

This have consequences in performance or stability of the server?

After applying the patch attached, the log does not report this issue
and dir command show this:

C:\postgres\data>dir *.stat /s
 O volume na unidade C é hd
 O Número de Série do Volume é F470-0E50

 Pasta de C:\postgres\data\pg_stat_tmp

14/02/2022  21:15             1.670 db_0.stat
14/02/2022  21:15            12.215 db_25214.stat
14/02/2022  21:15             1.335 global.stat
               3 arquivo(s)         15.220 bytes


The point is we can not relies on the "rename" function at Windows Side.
The local installation is made with user with full rights and permissions, but
Postgres Windows still can't rename correctly.

thoughts?

regards,
Ranier Vilela

Attachment

Re: Postgres 14.2 Windows can't rename temporary statistics file

From
Justin Pryzby
Date:
On Mon, Feb 14, 2022 at 09:19:54PM -0300, Ranier Vilela wrote:
> I've reported this issue, but without success in fixing it.

It'd be helpful to provide a link to the prior discussions, and summarize it.

https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items
https://www.postgresql.org/message-id/CAEudQAoR5e7=uMZ0otzuCVb25zTC8QQBe+2Dt1JRsa3u+XuwJg@mail.gmail.com

See also e2f0f8ed251d02c1eda79e1ca3cb3db2681e7a86

-- 
Justin



Re: Postgres 14.2 Windows can't rename temporary statistics file

From
Ranier Vilela
Date:
Em seg., 14 de fev. de 2022 às 21:58, Justin Pryzby <pryzby@telsasoft.com> escreveu:
On Mon, Feb 14, 2022 at 09:19:54PM -0300, Ranier Vilela wrote:
> I've reported this issue, but without success in fixing it.

It'd be helpful to provide a link to the prior discussions, and summarize it.

https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items
https://www.postgresql.org/message-id/CAEudQAoR5e7=uMZ0otzuCVb25zTC8QQBe+2Dt1JRsa3u+XuwJg@mail.gmail.com

See also e2f0f8ed251d02c1eda79e1ca3cb3db2681e7a86
I can't be sure if this is a case of DELETE_PENDING,
but FlieMoveEx apparently solves it.

regards,
Ranier Vilela


Re: Postgres 14.2 Windows can't rename temporary statistics file

From
Thomas Munro
Date:
On Tue, Feb 15, 2022 at 2:09 PM Ranier Vilela <ranier.vf@gmail.com> wrote:
> Em seg., 14 de fev. de 2022 às 21:58, Justin Pryzby <pryzby@telsasoft.com> escreveu:
>> On Mon, Feb 14, 2022 at 09:19:54PM -0300, Ranier Vilela wrote:
>> > I've reported this issue, but without success in fixing it.
>>
>> It'd be helpful to provide a link to the prior discussions, and summarize it.
>>
>> https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items
>> https://www.postgresql.org/message-id/CAEudQAoR5e7=uMZ0otzuCVb25zTC8QQBe+2Dt1JRsa3u+XuwJg@mail.gmail.com
>>
>> See also e2f0f8ed251d02c1eda79e1ca3cb3db2681e7a86
>
> I can't be sure if this is a case of DELETE_PENDING,
> but FlieMoveEx apparently solves it.

Can you explain why it solves it?

Have you seen this?

https://commitfest.postgresql.org/37/3347/

Do you know if FileMoveEx might be magically doing POSIX-style
operations when NTFS is used, on some recent version of Windows, as
was described at [1][2] with respect to DeleteFileEx?  CF #3347 is
about explicitly asking for those semantics, but [1] says that the
need to do that might be going away.

One of the reasons I have been putting off committing #3347 (other
than my desire to remove the pre-Win10 support) is (1) the hypothesis
that the problem might become simpler to solve as of some Windows 10
version as you might be seeing with your FileMoveEx test and (2) I'm
not sure how to handle the fact that once all the build farm animals
and CI systems are using POSIX semantics on NTFS, we'll have no
testing of the non-POSIX/traditional Windows semantics.  I mean stuff
like e2f0f8ed.  I suppose we really should have a non-NTFS build farm
animal, and I think we should have something like pg_test_dirmod.exe
that runs through a bunch of  standalone tests.

[1]
https://stackoverflow.com/questions/60424732/did-the-behaviour-of-deleted-files-open-with-fileshare-delete-change-on-windows
[2] https://www.postgresql.org/message-id/20210905214437.y25j42yigwnbdvtg@alap3.anarazel.de



Re: Postgres 14.2 Windows can't rename temporary statistics file

From
Ranier Vilela
Date:
Em seg., 14 de fev. de 2022 às 22:58, Thomas Munro <thomas.munro@gmail.com> escreveu:
On Tue, Feb 15, 2022 at 2:09 PM Ranier Vilela <ranier.vf@gmail.com> wrote:
> Em seg., 14 de fev. de 2022 às 21:58, Justin Pryzby <pryzby@telsasoft.com> escreveu:
>> On Mon, Feb 14, 2022 at 09:19:54PM -0300, Ranier Vilela wrote:
>> > I've reported this issue, but without success in fixing it.
>>
>> It'd be helpful to provide a link to the prior discussions, and summarize it.
>>
>> https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items
>> https://www.postgresql.org/message-id/CAEudQAoR5e7=uMZ0otzuCVb25zTC8QQBe+2Dt1JRsa3u+XuwJg@mail.gmail.com
>>
>> See also e2f0f8ed251d02c1eda79e1ca3cb3db2681e7a86
>
> I can't be sure if this is a case of DELETE_PENDING,
> but FlieMoveEx apparently solves it.

Can you explain why it solves it?
Because the parameter MOVEFILE_COPY_ALLOWED,
which leads Windows to use another algorithm, perhaps.
Only MOVEFILE_REPLACE_EXISTING does not solve it.

 

Have you seen this?

https://commitfest.postgresql.org/37/3347/
Postgres really need an atomic rename in Windows?
I think working, rename can be not atomic and take 1s.
 


Do you know if FileMoveEx might be magically doing POSIX-style
operations when NTFS is used, on some recent version of Windows, as
was described at [1][2] with respect to DeleteFileEx?  CF #3347 is
about explicitly asking for those semantics, but [1] says that the
need to do that might be going away.
My local environment is:
Windows 10 Home (64 bits)
21H2 build 19044.1526

With Postgres v14.2 and HEAD,
enough starting the server, without any user connected
to log appears.

With the patch, the log no longer occurs
and the renamed file appears in the dir command listing.
Does this answer the question about FileMoveEx?
 

One of the reasons I have been putting off committing #3347 (other
than my desire to remove the pre-Win10 support) is (1) the hypothesis
that the problem might become simpler to solve as of some Windows 10
version as you might be seeing with your FileMoveEx test and (2) I'm
not sure how to handle the fact that once all the build farm animals
and CI systems are using POSIX semantics on NTFS, we'll have no
testing of the non-POSIX/traditional Windows semantics.  I mean stuff
like e2f0f8ed.  I suppose we really should have a non-NTFS build farm
animal, and I think we should have something like pg_test_dirmod.exe
that runs through a bunch of  standalone tests.
 
1) Apparently solves, but not tested with heavy load and many users.

regards,
Ranier Vilela