Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, selectquery does not return the expected results.

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, selectquery does not return the expected results.
Дата
Msg-id CAKFQuwbLt5k1sQWz6JhaYmq+uHG8BYkbfppbOQEkC8g+r7vc_A@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #15755: After upgrading to 9.6.12 using pg_upgrade, select query does not return the expected results.  (PG Bug reporting form <noreply@postgresql.org>)
Ответы Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, selectquery does not return the expected results.  (Suresh Thelkar <suresh.thelkar@altair.com>)
Список pgsql-bugs
Дерево обсуждения
On Mon, Apr 15, 2019 at 1:03 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15755
Logged by:          pg_upgrade fails intermittently from version 9.3.12 to 9.6.12
Email address:      suresh.thelkar@altair.com
PostgreSQL version: 9.6.12
Operating system:   RHEL 7.5
[...]
One of the workarounds suggested is to rebuild the indexes of the corrupted
table. We tried this workaround, reindex at table level did not help us

You didn't actually succeed in forming valid and executable REINDEX command so whether or not it would have worked at the table/index level is an unsolved question.  Given that it worked at the whole database level what I suspect happens is the whole DB command succeeded and when it got to the problem index it fixed the problem.  Had the original table/index commands been written correctly they would have also resulted in a fixed setup and the need to reindex the whole database would have been mitigated (though given the possibility of other indexes having problems a whole database reindex was probably a worthwhile exercise anyway).

Appreciate your help in letting us know the following.
1. It looks like ONLY indexes are corrupted in our case and reindexing on
whole database solved our issue. Just eager to know is there any possibility
that similar corruption can happen for other database objects like tables,
sequences apart from indexes?

Corruption is always a possibility though indexes, because they are ordered, are the most vulnerable.

/* However REINDEX is not successful neither against the table nor the index
*/

broken_db=# REINDEX TABLE xyz.job_attr;
ERROR:  syntax error at or near "QUERY"
LINE 1: QUERY PLANREINDEX TABLE xyz.job_attr;


You got a syntax error "at or near QUERY"; something you did entering the command (LINE 1: is the whole command the server saw) was problematic and the error has no bearing on whether "REINDEX TABLE xyz.job_attr" would have worked without the typo (it likely would have)
 
broken_db=# REINDEX INDEX job_attr_idx;
ERROR:  relation "job_attr_idx" does not exist

The index was never found, in the search_path, and so no reindexing was attempted.  Whether it would have worked had you correctly added the necessary schema to the identifier (or search_path) remains unknown (it likely would have).

Its not enough to say/know that something "didn't work" - understanding "why" it failed is necessary before drawing conclusions.

David J.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Suresh Thelkar
Дата:
Сообщение: Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, selectquery does not return the expected results.
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15758: regr_* return wrong answers for some valid data