Re: something weird happened - can select by column value although column 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 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.

Index corruption?  Maybe rebuild the FK.

--
Angular momentum makes the world go 'round.