Re: missing foreign key fails silently using COPY

I wrote:
> missive(at)frontiernet(dot)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 is
C COPY -- completion tag for COPY
E errmsg -- error detected during xact completion
Z -- 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 application didn'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 a
PGRES_COPY_OUT result by executing PQgetline repeatedly,
followed by PQendcopy after the terminator line is seen. It
should then return to the PQgetResult loop until PQgetResult
returns NULL. Similarly a PGRES_COPY_IN result is processed by a
series of PQputline calls followed by PQendcopy, then return to
the PQgetResult loop. This arrangement will ensure that a copy
in or copy out command embedded in a series of SQL commands will
be executed correctly.

Older applications are likely to submit a copy in or copy out
via PQexec and assume that the transaction is done after
PQendcopy. This will work correctly only if the copy in/out is
the 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