Thread: something weird happened - can select by column value although columnvalue exist

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
forprivacy 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

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.

Ron <ronljohnsonjr@gmail.com> writes:
> On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote:
>> Today the following happened:
>> < 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".
>> [ but it is present ]
>>
>> What is this? Version 9.3.9,. running on Linux RH6.

> Index corruption?  Maybe rebuild the FK.

Yeah, I'd suspect index corruption as well, particularly seeing that this
is a three-year-old point release in a branch that had more than its share
of data corruption issues.  My suggestion is first update to 9.3.latest,
then reindex.

BTW, 9.3.x will be EOL next month, so you should be thinking about getting
onto a later release branch ...

            regards, tom lane