Re: Transaction question - Mailing list pgsql-general

On 07/11/2012 04:18 PM, Jeff Ross wrote:
> On 7/11/12 5:13 PM, Adrian Klaver wrote:
>> On 07/11/2012 04:02 PM, Jeff Ross wrote:
>>> On 7/11/12 3:52 PM, Adrian Klaver wrote:
>>
>>>>
>>>> Is there an index on this table?
>>>> If so have you tried a REINDEX?
>>>>
>>>
>>> Here's the table definition:
>>>
>>> jross@nirvana:/home/jross $ psql wykids
>>> psql (9.1.4, server 9.1.3)
>>> Type "help" for help.
>>>
>>> wykids=# \d survey_answers
>>>                                            Table "public.survey_answers"
>>>             Column            |  Type | Modifiers
>>> -----------------------------+---------+-------------------------------------------------------------------------
>>>
>>>
>>>   srv_answers_id              | integer | not null default
>>> nextval('survey_answers_srv_answers_id_seq'::regclass)
>>>   srv_answers_srv_id          | integer | not null
>>>   srv_answers_pp_id           | integer | not null
>>>   srv_answers_question_id     | integer | not null
>>>   srv_answers_answer          | integer |
>>>   srv_answers_answer_text     | text    |
>>>   srv_answers_rank            | integer |
>>>   srv_answers_sub_question_id | integer |
>>> Indexes:
>>>      "survey_answers_pkey" PRIMARY KEY, btree (srv_answers_id)
>>> Foreign-key constraints:
>>>      "survey_answers_srv_answers_answer_fkey" FOREIGN KEY
>>> (srv_answers_answer) REFERENCES
>>> survey_possible_answers(srv_pos_answers_id)
>>>      "survey_answers_srv_answers_pp_id_fkey" FOREIGN KEY
>>> (srv_answers_pp_id) REFERENCES people(pp_id)
>>>      "survey_answers_srv_answers_question_id_fkey" FOREIGN KEY
>>> (srv_answers_question_id) REFERENCES survey_questions(srv_question_id)
>>>      "survey_answers_srv_answers_srv_id_fkey" FOREIGN KEY
>>> (srv_answers_srv_id) REFERENCES surveys(srv_id)
>>>
>>> wykids=#
>>>
>>> I haven't re-indexed that table but somehow I find it hard to believe
>>> that a reindex can make those rows appear. I just tried it on my
>>> development server--no change.

To answer the above, see below. Not saying this is what happened in your
case, but it can happen.

http://www.postgresql.org/docs/9.1/static/release-9-1-3.html
Fix btree index corruption from insertions concurrent with vacuuming
(Tom Lane)

An index page split caused by an insertion could sometimes cause a
concurrently-running VACUUM to miss removing index entries that it
should remove. After the corresponding table rows are removed, the
dangling index entries would cause errors (such as "could not read block
N in file ...") or worse, silently wrong query results after unrelated
rows are re-inserted at the now-free table locations. This bug has been
present since release 8.2, but occurs so infrequently that it was not
diagnosed until now. If you have reason to suspect that it has happened
in your database, reindexing the affected index will fix things.


>>
>> The development server has the same issue with missing records?
>
> Yes, but it is db dropped and reloaded daily from the real server so
> that is to be expected.
>>
>>>
>>> Jeff
>>
>>>
>>
>>
>
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com