bug fix request

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 the terminal emulator, and even
if they could typing tab for command completion and then going ahead and
typing the next character immediately 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