Thread: One more keywords problem (hopefully the last)
OK, can you stand one more question from me about this whole keyword thing? I've now got them all sorted onto separate lines in a text file the way I want them, and while I initially ran into a few problems with duplicate entries due to a few keywords being repeated in two categories, I've fixed that. 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. Isn't there some kind of unwritten law that all error messages must include line number? If not, there should be. So... does anyone know how I could figure out what's causing this? The exact command I used was: \copy key_art_temp from key_art_join.txt using delimiters '|' with null as '' The script used to create the table was: CREATE TABLE key_art_temp ( artist_id SMALLINT NOT NULL, keyword_id SMALLINT , keyword VARCHAR(40) NOT NULL, CONSTRAINT key_art_temp_pk PRIMARY KEY (artist_id,keyword), CONSTRAINT key_art_temp_fk_art FOREIGN KEY (artist_id) REFERENCES artists(artist_id), CONSTRAINT key_art_temp_fk_key FOREIGN KEY (keyword) REFERENCES keywords(keyword) ); And some sample lines from the text file I'm trying to import: 100||industry 100||nature 105||activist 105||aging/mortality 105||body The empty column in the middle is because I ultimately want the key_art_join able to reference the keyword ID numbers, not the keywords themselves, but given how the data from FMP was formatted, it was easier to import the keywords into a temporary table and then match them up with the ID numbers in the real join table afterwards by looking up those from the keyword table. Any ideas? Is there some really simple reason this is screwing up that I'm just somehow missing? Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
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
on 7/3/03 4:57 PM, Lynna Landstreet at lynna@gallery44.org wrote: > 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 Whoops, figured it out... A couple of keywords had actually changed between different iterations of the database. That sort of thing would be why I want to use the numbers instead of the actual words, but in the meantime, it tripped me up while I was trying to get to the numbers! All good now. Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Tom Lane wrote: >Lynna Landstreet <lynna@gallery44.org> writes: > > >>... However, now I'm getting this: >> >> But Tom, why is this resetting the connection in this case? Shouldn't this just abort the Transaction, but keep up the connection? Cheers, Dani
Dani Oderbolz <oderbolz@ecologic.de> writes:
> why is this resetting the connection in this case?
Shortcoming of the COPY protocol: there's no good way to recover from an
error except to abandon the connection. (This is fixed in the protocol
redesign upcoming for 7.4, but there's just no way around it in the old
protocol.)
regards, tom lane