Обсуждение: PostgreSQL 8.2beta1 w/ VALUES
Greetings,
Was just playing with 8.2beta1 and importing some data from MySQL and found something rather annoying. Not *100%*
surethe best way to deal with this, if there even is a way, but...
When loading a rather large data set I started getting errors along these lines:
psql:/home/sfrost/school/cs750/reality/dump-anonymized.postgres.sql:262:
WARNING: nonstandard use of escape in a string literal
LINE 1: ...XXXXXXXXXX 9999,9:9:999'),(99999,'000000000000',0,'XXXXX XXX...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
psql:/home/sfrost/school/cs750/reality/dump-anonymized.postgres.sql:262:
WARNING: nonstandard use of escape in a string literal
LINE 1: ...9999999999',0,',9:9:999'),(99999,'000000000000',0,'XXXX XXXX...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
INSERT 0 20795
cs750=#
Which, by themselves, aren't really an issue *except* for the fact that I got an *insane* number of them. I don't
thinkit was quite one for every row (of which there were 20,795, you'll note) but it was more than enough to drive me
insane. Additionally, cancel requests were ignored. It's possible this was because of network lag and the server had
alreadyprocessed the request but I'm not sure that was the only reason. I know I held down ctrl-c for quite a while
duringthe spew of messages...
Anyhow, don't know if there's really a good solution but it'd be nice to only get one warning, or one of a given type,
orsomething, and to respond to cancel requests (if there was an issue there). Sorry this is more from a user's
perspective,I havn't got time atm to go digging through the code. I'd be curious about implementing a possible
error-aggregationsystem for reporting on large sets like this but that might be overkill anyway.
Thanks,
Stephen
> Anyhow, don't know if there's really a good solution but it'd be nice > to only get one warning, or one of a given type, or something, and to Except that one warning would not be accurate, because the warning is per tuple. How is postgresql going to know that the warning applies to the same set of data but just a different tuple? > respond to cancel requests (if there was an issue there). Sorry this > is more from a user's perspective, I havn't got time atm to go digging > through the code. I'd be curious about implementing a possible > error-aggregation system for reporting on large sets like this but > that might be overkill anyway. You could dial down client_min_messages, set it to ERROR, then you won't see warnings ;) Sincerely, Joshua D. Drake > > Thanks, > > Stephen
Josh, > > Anyhow, don't know if there's really a good solution but > it'd be nice > > to only get one warning, or one of a given type, or > something, and > > to > > Except that one warning would not be accurate, because the > warning is per tuple. How is postgresql going to know that > the warning applies to the same set of data but just a > different tuple? If it's going to roll back the entire load after that one warning, it should terminate there. This is a common problem with OLAP and based on the observation here, this needs to be fixed. Not being able to cancel out at this point is even worse, can you imagine the frustration of trying to load 10GB of data and having to wait until the end after seeing these warnings, while knowing that you're just going to have to try again anyway? Eventually we'll implement single row error handling, but even then there should be a selectable behavior to terminate the load on the first warning/error. - Luke
Luke, et al,
* Luke Lonergan (LLonergan@greenplum.com) wrote:
> > Except that one warning would not be accurate, because the
> > warning is per tuple. How is postgresql going to know that
> > the warning applies to the same set of data but just a
> > different tuple?
I didn't say it'd be easy. :)
> If it's going to roll back the entire load after that one warning, it
> should terminate there.
It didn't terminate it, though I agree that it would have been nice if I
could control if it would terminate on first warning or not.
> This is a common problem with OLAP and based on the observation here,
> this needs to be fixed. Not being able to cancel out at this point is
> even worse, can you imagine the frustration of trying to load 10GB of
> data and having to wait until the end after seeing these warnings, while
> knowing that you're just going to have to try again anyway?
Yes, rather frustrating even with only 20k rows.
> Eventually we'll implement single row error handling, but even then
> there should be a selectable behavior to terminate the load on the first
> warning/error.
It'd be nice to be able to do what (I believe..) Oracle and Access can
do- dump the warnings/error messages/rows into a seperate table and go
over them afterwards.. Probably wouldn't have helped me in this case
but I've been in other situations where it would have been nice. :)
Thanks,
Stephen
Hi, Luke, Luke Lonergan wrote: > If it's going to roll back the entire load after that one warning, it > should terminate there. AFAIK, a warning is no reason for PostgreSQL to roll back anything. That's the difference between a warning and an error. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
"Luke Lonergan" <LLonergan@greenplum.com> writes:
> If it's going to roll back the entire load after that one warning, it
> should terminate there.
This was a warning, not an error.
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
> When loading a rather large data set I started getting errors along
> these lines:
> psql:/home/sfrost/school/cs750/reality/dump-anonymized.postgres.sql:262:
> WARNING: nonstandard use of escape in a string literal
> LINE 1: ...XXXXXXXXXX 9999,9:9:999'),(99999,'000000000000',0,'XXXXX XXX...
> ^
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
> Which, by themselves, aren't really an issue *except* for the fact
> that I got an *insane* number of them. I don't think it was quite one
> for every row (of which there were 20,795, you'll note) but it was
> more than enough to drive me insane. Additionally, cancel requests
> were ignored.
That's not too surprising because I don't believe there are any
CHECK_FOR_INTERRUPTS calls in the basic lex/parse loop. That wouldn't
normally be a problem because that phase is pretty quick, but it is a
problem if the system is spitting tons of messages at you.
It seems like a reasonable thing to do would be to add a
CHECK_FOR_INTERRUPTS in elog.c just after sending a notice/warning
message to the client.
Comments?
regards, tom lane
That seems right, there won't be a performance impact unless the warnings are issued.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, September 30, 2006 01:48 PM Eastern Standard Time
To: Stephen Frost
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
Stephen Frost <sfrost@snowman.net> writes:
> When loading a rather large data set I started getting errors along
> these lines:
> psql:/home/sfrost/school/cs750/reality/dump-anonymized.postgres.sql:262:
> WARNING: nonstandard use of escape in a string literal
> LINE 1: ...XXXXXXXXXX 9999,9:9:999'),(99999,'000000000000',0,'XXXXX XXX...
> ^
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
> Which, by themselves, aren't really an issue *except* for the fact
> that I got an *insane* number of them. I don't think it was quite one
> for every row (of which there were 20,795, you'll note) but it was
> more than enough to drive me insane. Additionally, cancel requests
> were ignored.
That's not too surprising because I don't believe there are any
CHECK_FOR_INTERRUPTS calls in the basic lex/parse loop. That wouldn't
normally be a problem because that phase is pretty quick, but it is a
problem if the system is spitting tons of messages at you.
It seems like a reasonable thing to do would be to add a
CHECK_FOR_INTERRUPTS in elog.c just after sending a notice/warning
message to the client.
Comments?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend