Обсуждение: bug fix request
I would like to beg for some sort of fix to psql's handling of COPY data if the COPY fails. Presently you get a million lines of '\N command not recognised' and various other random things because if a line of the copy fails due to say a FK constraint, or even if the COPY is run in an aborted transaction, it tries to execute all the stdin data as actual statements. It's really, really, really annoying. Also, sometimes when you copy and paste SQL into a psql window, it executes help on commands for each line, although it doesn't affect the paste. That is also really annoying. I'll add to this email when it happens to me again, cos I tried a few pastes and couldn't reproduce it :/ Chris
Christopher Kings-Lynne wrote: > > Also, sometimes when you copy and paste SQL into a psql window, it > executes help on commands for each line, although it doesn't affect the > paste. That is also really annoying. I'll add to this email when it > happens to me again, cos I tried a few pastes and couldn't reproduce it :/ That last one is probably due to tabs in the pasted text. Happens to me all the time... > Chris Tim -- ----------------------------------------------- Tim Allen tim@proximity.com.au Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I would like to beg for some sort of fix to psql's handling of COPY data
> if the COPY fails.
> Presently you get a million lines of '\N command not recognised' and
> various other random things because if a line of the copy fails due to
> say a FK constraint, or even if the COPY is run in an aborted
> transaction, it tries to execute all the stdin data as actual
> statements.
I'd like to see a test case for this in v3 protocol. It was definitely
a problem before that, but I thought I fixed it.
> Also, sometimes when you copy and paste SQL into a psql window, it
> executes help on commands for each line, although it doesn't affect the
> paste. That is also really annoying.
Disable tab completion, or don't paste tabs. I don't think psql can be
expected to recognize that a tab is coming from pasted input.
regards, tom lane
>>Presently you get a million lines of '\N command not recognised' and >>various other random things because if a line of the copy fails due to >>say a FK constraint, or even if the COPY is run in an aborted >>transaction, it tries to execute all the stdin data as actual >>statements. > > I'd like to see a test case for this in v3 protocol. It was definitely > a problem before that, but I thought I fixed it. This is with 7.4 and it still does it.... Here is an example: Make file called test.sql: BEGIN; CREATE TABLE blah (a int4); COPY blah FROM STDIN; 1 2 3 \. COMMIT; Now, execute it TWICE on a clean database: -bash-2.05b$ psql -f test.sql test BEGIN CREATE TABLE COMMIT -bash-2.05b$ psql -f test.sql test BEGIN psql:test.sql:2: ERROR: relation "blah" already exists psql:test.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:test.sql:7: invalid command \. psql:test.sql:8: ERROR: syntax error at or near "1" at character 1 -bash-2.05b$ >>Also, sometimes when you copy and paste SQL into a psql window, it >>executes help on commands for each line, although it doesn't affect the >>paste. That is also really annoying. > > Disable tab completion, or don't paste tabs. I don't think psql can be > expected to recognize that a tab is coming from pasted input. How about if it's in a literal, eg. function definition. Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Presently you get a million lines of '\N command not recognised' and
> various other random things because if a line of the copy fails due to
> say a FK constraint, or even if the COPY is run in an aborted
> transaction, it tries to execute all the stdin data as actual
> statements.
>>
>> I'd like to see a test case for this in v3 protocol. It was definitely
>> a problem before that, but I thought I fixed it.
> This is with 7.4 and it still does it.... Here is an example:
Hmm. This error is not coming from "a line of the copy", it is occurring
because the COPY command itself fails, and so the server never tells
psql to shift into COPY mode. I'm not sure that a reasonable fix for
this is possible. As a counterexample, if you misspelled COPY as COPZ,
would you expect the software to decide that following lines up to
\. should be ignored? If you manually misentered a COPY command and got
an error, would you be surprised to have psql ignore everything you
typed until you typed \. ? (I can bet we'd get bug reports about that.)
regards, tom lane
> Hmm. This error is not coming from "a line of the copy", it is occurring > because the COPY command itself fails, and so the server never tells > psql to shift into COPY mode. I'm not sure that a reasonable fix for > this is possible. As a counterexample, if you misspelled COPY as COPZ, > would you expect the software to decide that following lines up to > \. should be ignored? If you manually misentered a COPY command and got > an error, would you be surprised to have psql ignore everything you > typed until you typed \. ? (I can bet we'd get bug reports about that.) Hmmm...doesn't stop it being annoying, however. I presumed I was replicating the same problem I get when running SQL scripts that insert a few million rows. Basically I start it running, then maybe some command before the COPY fails, then it gets to the COPY anyway and start barfing millions of lines. Then I have to change my terminal settings to record heaps of lines and then try to ctrl-C the query before it scrolls too far off, just to find out the line that caused the error. I guess the kind of difference in this case to me is that it's in a transaction, and the only "error" that the COPY command causes is that it's runnning in a rolled-back transaction. Low tech I know...but it's kind of annoying :) Chris
Tom Lane <tgl@sss.pgh.pa.us> writes: > Disable tab completion, or don't paste tabs. I don't think psql can be > expected to recognize that a tab is coming from pasted input. Hm, this also bother me all the time. It doesn't sound like it would be very hard to detect pasted tabs actually. Two options come to mind: . If there's any input available it's probably not a typed tab since typists usually can't type fast enough to out type theterminal emulator, and even if they could typing tab for command completion and then going ahead and typing the next characterimmediately would reasonably cancel the tab completion. . Pasted tabs are normally at the start of a line for indentation. Simply ignoring tab completion after white-space, ie,unbounded tab completion on an empty token, would eliminate 99.9% of the problem. -- greg
On Monday 29 November 2004 02:58, Christopher Kings-Lynne wrote:
> > Hmm. This error is not coming from "a line of the copy", it is occurring
> > because the COPY command itself fails, and so the server never tells
> > psql to shift into COPY mode. I'm not sure that a reasonable fix for
> > this is possible. As a counterexample, if you misspelled COPY as COPZ,
> > would you expect the software to decide that following lines up to
> > \. should be ignored? If you manually misentered a COPY command and got
> > an error, would you be surprised to have psql ignore everything you
> > typed until you typed \. ? (I can bet we'd get bug reports about that.)
>
> Hmmm...doesn't stop it being annoying, however.
>
> I presumed I was replicating the same problem I get when running SQL
> scripts that insert a few million rows. Basically I start it running,
> then maybe some command before the COPY fails, then it gets to the COPY
> anyway and start barfing millions of lines. Then I have to change my
> terminal settings to record heaps of lines and then try to ctrl-C the
> query before it scrolls too far off, just to find out the line that
> caused the error.
>
Chris, does the problem manifest itself if one of your COPY'd data lines
violates a primary key with existing data in the table ?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL