Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES

2006-09-30 Thread Tom Lane
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

2006-09-30 Thread Luke Lonergan
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

2006-09-24 Thread Stephen Frost
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

2006-09-24 Thread Markus Schaber
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

2006-09-24 Thread Tom Lane
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


Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES

2006-09-23 Thread Joshua D. Drake



  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

2006-09-23 Thread Luke Lonergan
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