Re: Rows missing from table despite FK constraint

Konrad Garus <konrad(dot)garus(at)gmail(dot)com> writes:
> 2010/1/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Just to confirm, if you try to select any of these rows by ctid, ie
>> select * from tablename where ctid = '(603713,1)';
>> you get nothing? What *should* happen is that you get the row if you
>> mention offset 1, 3, or 5, but nothing if you say 2 or 4.

> How about this?

> # select attachment_id from attachment where ctid = '(603713,1)';
> attachment_id
> ---------------
> 15460683
> (1 row)

> # select attachment_id from attachment where attachment_id = 15460683;
> attachment_id
> ---------------
> (0 rows)

Oh, so the row *is* there. What the above says is that you have a
corrupt index on attachment_id, which you should be able to fix via
REINDEX. However, I'm still a bit confused, because corrupt indexes
don't normally cause a problem for pg_dump (which is just doing SELECT *
or COPY, so the index wouldn't be consulted). Are the dumps you are
talking about perhaps made with something other than pg_dump?

regards, tom lane