Re: something weird happened - can select by column value althoughcolumn value exist

On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote:
> 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
queryfor 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.

Index corruption?  Maybe rebuild the FK.


-- 
Angular momentum makes the world go 'round.