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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
