Re: [BUGS] missing foreign key fails silently using COPY - Mailing list pgsql-hackers

I wrote:
> missive@frontiernet.net (Lee Harr) writes:
>> When COPYing data to a table which uses foreign keys, if there
>> is a reference to a key which is not there, the copy fails
>> (as expected) but there is no error message.

>> Hmm. Looking at it more, seems like there is an error message
>> when using:
>> COPY "f" FROM '/home/lee/f.dat';

>> but _not_ when using:
>> COPY "f" FROM stdin;
>> or
>> \copy f from f.dat

> This seems to be a libpq and/or psql bug.

After further investigation I'm still unsure where to pin the blame.

What the backend is actually sending back isC COPY        -- completion tag for COPYE errmsg    -- error detected
duringxact completionZ        -- backend now idle
 
which is perfectly reasonable.  What happens on the psql side is:

1. PQendcopy() eats the C COPY, stops there, and returns "success".

2. psql falls out to SendQuery, which tries to check to see if any async
NOTIFY messages came in with the command.

3. PQnotifies processes the E message while looking to see if there are
any N messages in the buffer.  It finds none, and returns NULL.  On
return from PQnotifies, there is a pending asynchronous PGresult with
the error message in the PGconn, and the 'Z' is still uneaten.

4. psql now goes back to sleep without any further calls to libpq.

5. On psql's next call to PQexec(), the pending error result is thrown
away, as is the 'Z'; the libpq sources have the comment   /*    * Silently discard any prior query result that
applicationdidn't    * eat. This is probably poor design, but it's here for backward    * compatibility.    */
 
However, reporting the error at this point would be far too late anyway;
from the user's perspective we are now executing the next command.
So I don't think PQexec is to be blamed.


We could make it work without any changes in libpq by having psql do
a PQgetResult after the PQendcopy, but this strikes me as an unpleasant
answer; that would suggest that every application that uses PQendcopy
is broken.

The other line of thought is that PQendcopy should eat input until it
sees the 'Z', and then return the error if there was one.  This would
localize the fix to PQendcopy, which would be a good thing.  A drawback
is that COPY TO STDIN or COPY FROM STDOUT would no longer work in the
context of multiple-query strings --- though I'm doubtful that anyone
uses that feature.  The implications for libpq's nonblocking input mode
may be bad too (though I'm unconvinced that that works at all with COPY,
anyway).

The existing libpq documentation says:
When using PQgetResult, the application should respond to aPGRES_COPY_OUT result by executing PQgetline
repeatedly,followedby PQendcopy after the terminator line is seen. Itshould then return to the PQgetResult loop until
PQgetResultreturnsNULL. Similarly a PGRES_COPY_IN result is processed by aseries of PQputline calls followed by
PQendcopy,then return tothe PQgetResult loop. This arrangement will ensure that a copyin or copy out command embedded
ina series of SQL commands willbe executed correctly.
 
Older applications are likely to submit a copy in or copy outvia PQexec and assume that the transaction is done
afterPQendcopy.This will work correctly only if the copy in/out isthe only SQL command in the command string.
 

This seems to lean more in the direction of thinking that psql should
do a PQgetResult after the PQendcopy.  Perhaps we should do that, and
add a warning to the docs that PQendcopy alone is insufficient to detect
end-of-transaction errors.

In any case it's a bit of a mess :-(  Comments anyone?
        regards, tom lane