Hi,
Today the following happened:
Found this error in my production log:
< 2018-10-11 13:31:52.587 CDT >ERROR: insert or update on table "file"
violates foreign key constraint "$1"
< 2018-10-11 13:31:52.587 CDT >DETAIL: Key (volume)=(155303) is not present in
table "volume".
< 2018-10-11 13:31:52.587 CDT >STATEMENT:
INSERT INTO file
(sanity_crc,uid,pnfs_id,crc,deleted,cache_mod_time,drive,volume,sanity_size,cache_status,gid,location_cookie,cache_location,original_library,bfid,pnfs_path,size)
VALUES (
4002229874,0,'',256655919,'u','2018-10-11 13:31:52','',(SELECT
id FROM volume where label='A'),65536,'B',0,'C','D','E','F','',197243)
RETURNING *
file table references volume table on file.volume = volume.id and file.volume
is FK to volume.id. I doctored the query for privacy replacing string values
with 'A', 'B'. ...
(Queries similar to the above quoted are executed by an application and run
thousand of times every day for years)
So, the problem:
SELECT id FROM volume where label='A';
id
--------
155303
(1 row)
BUT:
select * from volume where id = 155303;
...
(0 rows)
?!
id is a sequence:
id | integer | not null default
nextval(('volume_seq'::text)::regclass)
This entry id = 155303 has existed for some time and has a lot of existing file
entries holding
FK reference to volume id = 155303
I "fixed" the issue just by:
update volume set id = 155303 where label='A';
BUT It did not work right away. Meaning I did this once:
update volume set id = 155303 where label='A';
no effect.
I did it again, I also did it;
update volume set id = (select id from volume where label='A');
and then again
update volume set id = 155303 where label='A';
eventually it worked. Now,
select count(*) from volume where label='A';
count
-------
1
(1 row)
What is this? Version 9.3.9,. running on Linux RH6.
Thanks,
Dmitry
