Subject: Re: More time spending with "delete pending"
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <c3427edf-d7c0-ff57-90f6-b5de3bb62709@gmail.com>
 <20201115011112.GW30691@telsasoft.com>
 <979f4ee6-9960-e37f-f3ee-f458e87777b0@gmail.com>
 <995713.1605725597@sss.pgh.pa.us> <1001775.1605731995@sss.pgh.pa.us>
 <3366bfde-183f-60be-62ab-8ae81b946bac@gmail.com>
 <1006364.1605738508@sss.pgh.pa.us>
From: Alexander Lakhin <exclusion@gmail.com>
Message-ID: <20729794-e2bf-bd7a-f01b-d132f89fd0d7@gmail.com>
Date: Sun, 22 Nov 2020 18:59:59 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <1006364.1605738508@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

19.11.2020 01:28, Tom Lane wrote:
> Alexander Lakhin <exclusion@gmail.com> writes:
>> 18.11.2020 23:39, Tom Lane wrote:
>>> Now, the ones on the 10 and 11 branches are all from pg_ctl, which
>>> does not use pgwin32_open() in those branches, only native open().
>>> So those aren't fair to count against it.  But we have nearly as
>>> many similar failures in HEAD, which surely is going through
>>> pgwin32_open().  So either we don't really have adequate protection
>>> against delete-pending files, or there is some other effect we haven't
>>> explained yet.
>> Can you confirm that there are no such failures on REL_12_STABLE and
>> REL_13_STABLE for last three (or maybe six) months? Maybe something
>> changed in HEAD then?
> Hmm, that is an interesting question isn't it.  Here's a search going
> back a full year.  There are a few in v12 --- interestingly, all on
> the statistics file, none from pg_ctl --- and none in v13.  Of course,
> v13 has only existed as a separate branch since 2020-06-07.
>
> There's also a buildfarm test-coverage artifact involved.  The bulk
> of the HEAD reports are from dory and walleye, neither of which are
> building v13 as yet, because of unclear problems [1].  I think those
> two animals build much more frequently than our other Windows animals,
> too, so the fact that they have more may be just because of that and
> not because they're somehow more susceptible.  Because of that, I'm not
> sure that we have enough evidence to say that v13 is better than HEAD.
> If there is some new bug, it's post-v12, but maybe not post-v13.  But
> v12 is evidently not perfect either.
Thanks for the complete list! As I can see from it, only lorikeet fails
on REL_12_STABLE. And it has a simple explanation.
lorikeet uses cygwin, not win32, but improved open() and fopen()
functions are not defined on cygwin:
#if defined(WIN32) && !defined(__CYGWIN__)

/*
 * open() and fopen() replacements to allow deletion of open files and
 * passing of other special options.
 */
#define        O_DIRECT    0x80000000
extern int    pgwin32_open(const char *, int,...);
extern FILE *pgwin32_fopen(const char *, const char *);
#define        open(a,b,c) pgwin32_open(a,b,c)
#define        fopen(a,b) pgwin32_fopen(a,b)
...


And aside from the "Permission denied" failures (not necessarily related
to the "delete pending" state), we can see the "Device or resource busy"
failures on the same global.stat file (e. g., [1], [2], [3]). All these
failures occur when VACUUM (or REINDEX [3]) tries to access statistics
simultaneously with the statistics collector.

I've tried to make open()/fopen() replacements for cygwin, but haven't
succeeded yet (I need more time to find out how to get original
GetLastError() [4], as knowing errno is not sufficient to implement
pgwin32_open()' logic). (And I'm inclined to file a separate bug related
to this cygwin behavior when I'll get more info.)

The failures on HEAD (on dory and walleye) need another investigation
(maybe they are caused by modified stat()...).

[1]
https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=lorikeet&dt=2020-09-07%2020%3A31%3A16&stg=install-check-C
[2]
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=lorikeet&dt=2019-10-27%2009%3A36%3A07
[3]
https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=lorikeet&dt=2020-09-02%2008%3A28%3A53&stg=install-check-C
[4] https://github.com/openunix/cygwin/blob/master/winsup/cygwin/errno.cc

Best regards,
Alexander