Lynna Landstreet <lynna@gallery44.org> writes:
> ... However, now I'm getting this:
> ERROR: key_art_temp_fk_key referential integrity violation - key
> referenced from key_art_temp not found in keywords
> lost synchronization with server, resetting connection
> Obviously there's some kind of problem with one or more of the keywords in
> the temp file not being found in the keywords file, but as far as I know
> there shouldn't be. All the keywords I can see in it look fine, and the
> stupid error message doesn't even give me a line number so I don't know
> where to look for the problem.
Yeah, referential constraints are only checked at the end of the
transaction, at which point the COPY is actually not running anymore,
so there's really no way to know which line of data generated the
problem row.
You could work around this by temporarily adding a custom check
function, since check constraints are applied immediately. The attached
example is pretty ugly but illustrates the idea.
regression=# create table foo(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
regression=# insert into foo values(1);
INSERT 429366 1
regression=# insert into foo values(3);
INSERT 429367 1
regression=# create table bar (f1 int references foo);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
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
>> 3
>> 7
>> 2
>> \.
ERROR: $1 referential integrity violation - key (f1)=(7) referenced from bar not found in foo
regression=# create function is_in_foo(int) returns bool as
regression-# 'select exists(select 1 from foo where f1 = $1)'
regression-# language sql;
CREATE FUNCTION
regression=# alter table bar add constraint c1 check (is_in_foo(f1));
ALTER TABLE
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
>> 3
>> 7
>> 2
>> \.
ERROR: CopyFrom: rejected due to CHECK constraint "c1" on "bar"
CONTEXT: COPY FROM, line 3
regression=#
This is with CVS tip but existing releases should work the same way.
regards, tom lane
