Re: pg_upgrade and log file output on Windows

Has anyone successfully used pg_upgrade 9.0 with -l (log) on Windows?

I received a private email bug report that pg_upgrade 9.0 does not work
with the -l/log option on Windows. The error is:

Analyzing all rows in the new cluster
""c:/MinGW/msys/1.0/home/edb/inst/bin/vacuumdb" --port 55445 --username "edb" --all --analyze
>> c:/MinGW/msys/1.0/home/edb/auxschedule/test.log 2>&1"
The process cannot access the file because it is being used by another
process.

What has me confused is this same code exists in pg_migrator, which was
fixed to work with -l on Windows by Hiroshi Saito with this change:

/*
* On Win32, we can't send both server output and pg_ctl output
* to the same file because we get the error:
* "The process cannot access the file because it is being used by another process."
* so we have to send pg_ctl output to 'nul'.
*/
sprintf(cmd, SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
"-o \"-p %d -c autovacuum=off -c autovacuum_freeze_max_age=2000000000\" "
"start >> \"%s\" 2>&1" SYSTEMQUOTE,
bindir, ctx->logfile, datadir, port,
#ifndef WIN32
ctx->logfile);
#else
DEVNULL);
#endif

The fix was not to use the same log file and output file for pg_ctl.
But as you can see, the pg_ctl and vacuumdb code is unchanged:

prep_status(ctx, "Analyzing all rows in the new cluster");
exec_prog(ctx, true,
SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --username \"%s\" "
"--all --analyze >> %s 2>&1" SYSTEMQUOTE,
ctx->new.bindir, ctx->new.port, ctx->user, ctx->logfile);

I can't figure out of there is something odd about this user's setup or
if there is a bug in pg_upgrade with -l on Windows.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, July 18, 2011 11:28 pm, Bruce Momjian wrote:
> Has anyone successfully used pg_upgrade 9.0 with -l (log) on Windows?
>
> I received a private email bug report that pg_upgrade 9.0 does not work
> with the -l/log option on Windows. The error is:
>
> Analyzing all rows in the new cluster
> ""c:/MinGW/msys/1.0/home/edb/inst/bin/vacuumdb" --port 55445 --username
> "edb" --all --analyze
> >> c:/MinGW/msys/1.0/home/edb/auxschedule/test.log 2>&1"
> The process cannot access the file because it is being used by another
> process.
>
> What has me confused is this same code exists in pg_migrator, which was
> fixed to work with -l on Windows by Hiroshi Saito with this change:
>
> /*
> * On Win32, we can't send both server output and pg_ctl output
> * to the same file because we get the error:
> * "The process cannot access the file because it is being used by
> another process."
> * so we have to send pg_ctl output to 'nul'.
> */
> sprintf(cmd, SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
> "-o \"-p %d -c autovacuum=off -c
> autovacuum_freeze_max_age=2000000000\" "
> "start >> \"%s\" 2>&1" SYSTEMQUOTE,
> bindir, ctx->logfile, datadir, port,
> #ifndef WIN32
> ctx->logfile);
> #else
> DEVNULL);
> #endif
>
> The fix was not to use the same log file and output file for pg_ctl.
> But as you can see, the pg_ctl and vacuumdb code is unchanged:
>
> prep_status(ctx, "Analyzing all rows in the new cluster");
> exec_prog(ctx, true,
> SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --username \"%s\" "
> "--all --analyze >> %s 2>&1" SYSTEMQUOTE,
> ctx->new.bindir, ctx->new.port, ctx->user, ctx->logfile);
>
> I can't figure out of there is something odd about this user's setup or
> if there is a bug in pg_upgrade with -l on Windows.
>

The Windows file system seems to have some asynchronicity regarding what
files are locked. For that reason, the buildfarm code has long had a
couple of "sleep(5)" calls where it calls pg_ctl. You might benefit from
doing something similar.

cheers

andrew.

Andrew Dunstan wrote:
> > I can't figure out of there is something odd about this user's setup or
> > if there is a bug in pg_upgrade with -l on Windows.
> >
>
>
> The Windows file system seems to have some asynchronicity regarding what
> files are locked. For that reason, the buildfarm code has long had a
> couple of "sleep(5)" calls where it calls pg_ctl. You might benefit from
> doing something similar.

Wow, I had no idea --- I can certainly add them. It is possible the
person testing this has a faster machine than other users. I will
report back after testing with a sleep(5).

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 07/19/2011 01:25 PM, Bruce Momjian wrote:
> Andrew Dunstan wrote:
>>> I can't figure out of there is something odd about this user's setup or
>>> if there is a bug in pg_upgrade with -l on Windows.
>>>
>>
>> The Windows file system seems to have some asynchronicity regarding what
>> files are locked. For that reason, the buildfarm code has long had a
>> couple of "sleep(5)" calls where it calls pg_ctl. You might benefit from
>> doing something similar.
> Wow, I had no idea --- I can certainly add them. It is possible the
> person testing this has a faster machine than other users. I will
> report back after testing with a sleep(5).

We need to work out a way to get pg_upgrade testing into the buildfarm.
It's becoming too important not to.

cheers

andrew

I have fixed the bug below with the attached patches for 9.0, 9.1, and
9.2. I did a minimal patch for 9.0 and 9.1, and a more thorough patch
for 9.2.

The use of -l/log was tested originally in pg_migrator (for 8.4) and
reported to be working, but it turns out it only worked in 'check' mode,
and threw an error during actual upgrade. This bug was reported to me
recently by EnterpriseDB testing of PG 9.0 on Windows. The 9.2 C
comments should make it clear that Windows doesn't allow multiple
processes to write to the same file and should avoid future breakage.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Has anyone successfully used pg_upgrade 9.0 with -l (log) on Windows?
>
> I received a private email bug report that pg_upgrade 9.0 does not work
> with the -l/log option on Windows. The error is:
>
> Analyzing all rows in the new cluster
> ""c:/MinGW/msys/1.0/home/edb/inst/bin/vacuumdb" --port 55445 --username "edb" --all --analyze
> >> c:/MinGW/msys/1.0/home/edb/auxschedule/test.log 2>&1"
> The process cannot access the file because it is being used by another
> process.
>
> What has me confused is this same code exists in pg_migrator, which was
> fixed to work with -l on Windows by Hiroshi Saito with this change:
>
> /*
> * On Win32, we can't send both server output and pg_ctl output
> * to the same file because we get the error:
> * "The process cannot access the file because it is being used by another process."
> * so we have to send pg_ctl output to 'nul'.
> */
> sprintf(cmd, SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
> "-o \"-p %d -c autovacuum=off -c autovacuum_freeze_max_age=2000000000\" "
> "start >> \"%s\" 2>&1" SYSTEMQUOTE,
> bindir, ctx->logfile, datadir, port,
> #ifndef WIN32
> ctx->logfile);
> #else
> DEVNULL);
> #endif
>
> The fix was not to use the same log file and output file for pg_ctl.
> But as you can see, the pg_ctl and vacuumdb code is unchanged:
>
> prep_status(ctx, "Analyzing all rows in the new cluster");
> exec_prog(ctx, true,
> SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --username \"%s\" "
> "--all --analyze >> %s 2>&1" SYSTEMQUOTE,
> ctx->new.bindir, ctx->new.port, ctx->user, ctx->logfile);
>
> I can't figure out of there is something odd about this user's setup or
> if there is a bug in pg_upgrade with -l on Windows.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +