Обсуждение: 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