Re: missing foreign key fails silently using COPY

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