Thread: missing foreign key fails silently using COPY

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 is on:
 PostgreSQL 7.2 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3


SELECT version();

DROP TABLE p;
DROP TABLE f;

CREATE TABLE p (
 pk int primary key
);

CREATE TABLE f (
 fk int references p
);

COPY "p" FROM stdin;
1
2
3
4
\.

COPY "f" FROM stdin;
1
2
3
4
5
\.

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.  The error message is reported
in the postmaster log and sent to the frontend --- but libpq evidently
isn't catching it.  I suspect that the problem is that the error is
detected during transaction completion, not while the copy is in
progress, and either libpq or psql is dropping the ball about reporting
the error to the user.

I did:

regression=# create table foo(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# create table bar(f2 int references foo);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
regression=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> \.
regression=#

Looking in postmaster log, I now see
ERROR:  <unnamed> referential integrity violation - key referenced from bar not found in foo
but psql sure didn't tell me about it.

            regards, tom lane

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

I wrote:
> [ In psql, COPY from stdin fails to report referential integrity errors ]

> 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.

After further thought I have decided that the above behavior is probably
not so reasonable after all.  PQendcopy essentially assumes that it will
get back *either* an error message or a command-complete message from
a COPY command ... not both.  libpq has historically masked similar odd
behavior by looping in PQexec until it gets a 'Z' response, and
returning only the last status.  However, applications that use
PQgetResult directly may well assume that they get either a
command-complete or an error per command, not both.

It is fairly simple to tweak the backend so that finish_xact_command is
called before we report command-complete, rather than after; that would
allow end-of-transaction errors to be detected and reported in place of
issuing the command-complete message.  I'm now thinking that's the most
reasonable way to resolve this problem.  If we insist that it's a client
bug and not a backend misbehavior, we're going to have a lot of clients
to fix.

Any objections?
        regards, tom lane