Обсуждение: Re : Win32 binaries test / pg_dump problem
I reduced the pg_dump problem to a simple example. In fact, it seems to
be more a stdin / stdout problem
if you psql -f test.sql basetest from the windows shell to a windows or
linux database, you'll get a missing data error :
psql:test.sql:9: ERROR: missing data for column "b"
CONTEXT: COPY test, line 1: "a"
if you psql -f test.sql basetest from a linux shell to a windows or
linux database, you won't get an error
Another problem wich seems related to stdout occur while dumping a big
table (during a pg_dump, or manual COPY to STDOUT or COPY to FILE command).
At a point the backend crash :
LOG: server process (PID 3148) was terminated by signal 5
LOG: terminating any other active server processes
I haven't managed to reduce this problem to a simple case (the actual
table is 6GB).
Any idea where the problem can come from ?
BTW, I use the precompiled binaries from 23 may. Linux test are done with
7.4.1
cyril
> ----- Original Message -----
> From: Cyril VELTER
> To: pgsql-hackers-win32@postgresql.org
> Sent: Monday, May 24, 2004 7:58 PM
> Subject: Win32 binaries test / pg_dump problem
>
>
>
> I just gave a try to the win32 binaries in my developpement
environment.
> I first tested it with a small database and everything appeared to work
OK.
>
> Great Works !!!!!!
>
> After that I tried to restore a bigger one (~16Go)
>
> restore from a 7.1.3 dump (in -d mode) : restore is VERY slow. For a
> very simple table 3 int8 columns, I get 32 records per second (Bi Xeon 2.4
> Ghz / ide 7200 disk / 2 Go RAM) while I get around 1000 per second on a
> linux box (the box is similar hardware except it is 10000 rpm scsi disk,
but
> I don't think that explain all that difference). During the import, CPU is
> idle and the disk is working very hard.
>
>
> While running pg_dump (the windows one) from a linux 7.4.1 database to
> windows, the speed is comparable to linux->linux, but I got the following
> message :
>
> ERROR : missing data for column "p32134"
> CONTEXT: COPY c2674, line ....
>
> After analyzing the dump, it seems to be caused by a ctrl-Z (1A)
> character embeded into a text column which seem to confuse the win32 psql
> ...
>
Вложения
Cyril VELTER wrote: > I reduced the pg_dump problem to a simple example. In fact, it seems to > be more a stdin / stdout problem > > if you psql -f test.sql basetest from the windows shell to a windows or > linux database, you'll get a missing data error : > > psql:test.sql:9: ERROR: missing data for column "b" > CONTEXT: COPY test, line 1: "a" > > > if you psql -f test.sql basetest from a linux shell to a windows or > linux database, you won't get an error > > > Sounds like we should have psql open the file in binary mode on Windows. Would that cause problems? I doubt it, but I wonder. cheers andrew
From: "Andrew Dunstan" <andrew@dunslane.net>
> Cyril VELTER wrote:
>
> > if you psql -f test.sql basetest from the windows shell to a windows
or
> > linux database, you'll get a missing data error :
> >
> > psql:test.sql:9: ERROR: missing data for column "b"
> > CONTEXT: COPY test, line 1: "a"
> >
> > if you psql -f test.sql basetest from a linux shell to a windows or
> > linux database, you won't get an error
>
> Sounds like we should have psql open the file in binary mode on Windows.
> Would that cause problems? I doubt it, but I wonder.
>
you might be right, I just found some information on msdn that fseek for
example is influenced by ctrl-z when the file is opened in text mode.
I'm not sure that this is the cause of the second problem (backend crash
on copy to) though.
do you known where this modification needs to be done ?
cyril
Cyril VELTER said: > From: "Andrew Dunstan" <andrew@dunslane.net> > >> Cyril VELTER wrote: >> >> > if you psql -f test.sql basetest from the windows shell to a >> > windows > or >> > linux database, you'll get a missing data error : >> > >> > psql:test.sql:9: ERROR: missing data for column "b" >> > CONTEXT: COPY test, line 1: "a" >> > >> > if you psql -f test.sql basetest from a linux shell to a windows >> > or >> > linux database, you won't get an error >> >> Sounds like we should have psql open the file in binary mode on >> Windows. Would that cause problems? I doubt it, but I wonder. >> > > you might be right, I just found some information on msdn that fseek > for > example is influenced by ctrl-z when the file is opened in text mode. > > I'm not sure that this is the cause of the second problem (backend > crash > on copy to) though. > > do you known where this modification needs to be done ? > > cyril > probably in src/bin/psql/command.c::process_file() instead of mode "r" we should probably use the predefined constant PG_BINARY_R cheers andrew
----- Original Message -----
From: "Andrew Dunstan" <andrew@dunslane.net>
> Cyril VELTER said:
> > From: "Andrew Dunstan" <andrew@dunslane.net>
> >
> >> Cyril VELTER wrote:
> >>
> >> > if you psql -f test.sql basetest from the windows shell to a
> >> > windows
> > or
> >> > linux database, you'll get a missing data error :
> >> >
> >> > psql:test.sql:9: ERROR: missing data for column "b"
> >> > CONTEXT: COPY test, line 1: "a"
> >> >
> >> > if you psql -f test.sql basetest from a linux shell to a windows
> >> > or
> >> > linux database, you won't get an error
> >>
> >> Sounds like we should have psql open the file in binary mode on
> >> Windows. Would that cause problems? I doubt it, but I wonder.
> >>
>
> probably in src/bin/psql/command.c::process_file()
>
> instead of mode "r" we should probably use the predefined constant
> PG_BINARY_R
OK, that do the trick for that particular case. doing a more test.sql |
psql basetest lead to the same error but psql basetest < test.sql does works
so I think it is a windows shell or "more" problem (cat test.sql | psql
basetest under msys works).
Should I submit a patch, or somebody can commit this change ? (I just
replaced "r" by PG_BINARY_R in src/bin/psql/command.c::process_file().
BTW do you have any idea on the backend crash while doing a copy to
stdout ? (I reduced the case to copying a date column. Will try with a more
recent snapshot).
Andrew Dunstan wrote: > Cyril VELTER said: > > From: "Andrew Dunstan" <andrew@dunslane.net> > > > >> Cyril VELTER wrote: > >> > >> > if you psql -f test.sql basetest from the windows shell to a > >> > windows > > or > >> > linux database, you'll get a missing data error : > >> > > >> > psql:test.sql:9: ERROR: missing data for column "b" > >> > CONTEXT: COPY test, line 1: "a" > >> > > >> > if you psql -f test.sql basetest from a linux shell to a windows > >> > or > >> > linux database, you won't get an error > >> > >> Sounds like we should have psql open the file in binary mode on > >> Windows. Would that cause problems? I doubt it, but I wonder. > >> > > > > you might be right, I just found some information on msdn that fseek > > for > > example is influenced by ctrl-z when the file is opened in text mode. > > > > I'm not sure that this is the cause of the second problem (backend > > crash > > on copy to) though. > > > > do you known where this modification needs to be done ? > > > > cyril > > > > probably in src/bin/psql/command.c::process_file() > > instead of mode "r" we should probably use the predefined constant > PG_BINARY_R Uh, but it isn't a binary file, it is SQL commands. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: >Andrew Dunstan wrote: > > >>Cyril VELTER said: >> >> >>>From: "Andrew Dunstan" <andrew@dunslane.net> >>> >>> >>> >>>>Cyril VELTER wrote: >>>> >>>> >>>> >>>>> if you psql -f test.sql basetest from the windows shell to a >>>>> windows >>>>> >>>>> >>>or >>> >>> >>>>>linux database, you'll get a missing data error : >>>>> >>>>>psql:test.sql:9: ERROR: missing data for column "b" >>>>>CONTEXT: COPY test, line 1: "a" >>>>> >>>>> if you psql -f test.sql basetest from a linux shell to a windows >>>>> or >>>>>linux database, you won't get an error >>>>> >>>>> >>>>Sounds like we should have psql open the file in binary mode on >>>>Windows. Would that cause problems? I doubt it, but I wonder. >>>> >>>> >>>> >>> you might be right, I just found some information on msdn that fseek >>> for >>>example is influenced by ctrl-z when the file is opened in text mode. >>> >>> I'm not sure that this is the cause of the second problem (backend >>> crash >>>on copy to) though. >>> >>> do you known where this modification needs to be done ? >>> >>> cyril >>> >>> >>> >>probably in src/bin/psql/command.c::process_file() >> >>instead of mode "r" we should probably use the predefined constant >>PG_BINARY_R >> >> > >Uh, but it isn't a binary file, it is SQL commands. > > > If it can have an embedded ^Z it is not a legal Windows text file. Adding the binary flag will have exactly 2 effects: 1. It will inhibit the behaviour of the driver in translating CRLF to plain LF 2. It will not see ^Z as EOF. We don't care about the first - we handle CRLF just fine. But we do care about the second, and we don't want the library to interpret ^Z as EOF. cheers andrew
Would someone verify that having psql open the file in binary mode fixes the problem? --------------------------------------------------------------------------- Andrew Dunstan wrote: > Bruce Momjian wrote: > > >Andrew Dunstan wrote: > > > > > >>Cyril VELTER said: > >> > >> > >>>From: "Andrew Dunstan" <andrew@dunslane.net> > >>> > >>> > >>> > >>>>Cyril VELTER wrote: > >>>> > >>>> > >>>> > >>>>> if you psql -f test.sql basetest from the windows shell to a > >>>>> windows > >>>>> > >>>>> > >>>or > >>> > >>> > >>>>>linux database, you'll get a missing data error : > >>>>> > >>>>>psql:test.sql:9: ERROR: missing data for column "b" > >>>>>CONTEXT: COPY test, line 1: "a" > >>>>> > >>>>> if you psql -f test.sql basetest from a linux shell to a windows > >>>>> or > >>>>>linux database, you won't get an error > >>>>> > >>>>> > >>>>Sounds like we should have psql open the file in binary mode on > >>>>Windows. Would that cause problems? I doubt it, but I wonder. > >>>> > >>>> > >>>> > >>> you might be right, I just found some information on msdn that fseek > >>> for > >>>example is influenced by ctrl-z when the file is opened in text mode. > >>> > >>> I'm not sure that this is the cause of the second problem (backend > >>> crash > >>>on copy to) though. > >>> > >>> do you known where this modification needs to be done ? > >>> > >>> cyril > >>> > >>> > >>> > >>probably in src/bin/psql/command.c::process_file() > >> > >>instead of mode "r" we should probably use the predefined constant > >>PG_BINARY_R > >> > >> > > > >Uh, but it isn't a binary file, it is SQL commands. > > > > > > > > If it can have an embedded ^Z it is not a legal Windows text file. > > Adding the binary flag will have exactly 2 effects: > 1. It will inhibit the behaviour of the driver in translating CRLF to > plain LF > 2. It will not see ^Z as EOF. > > We don't care about the first - we handle CRLF just fine. But we do care > about the second, and we don't want the library to interpret ^Z as EOF. > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073