Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
Stephen Frost [EMAIL PROTECTED] 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: ...XX ,9:9:999'),(9,'',0,'X 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
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: ...XX ,9:9:999'),(9,'',0,'X 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
Luke, et al, * Luke Lonergan ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
Luke Lonergan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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%* sure the 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: ...XX ,9:9:999'),(9,'',0,'X 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: ...99',0,',9:9:999'),(9,'',0,' ... ^ 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 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. It's possible this was because of network lag and the server had already processed the request but I'm not sure that was the only reason. I know I held down ctrl-c for quite a while during the 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, or something, 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-aggregation system for reporting on large sets like this but that might be overkill anyway. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
