Обсуждение: using index "pg_toast_..." despite IgnoreSystemIndexes
Hi After an upgrade to 8.4.12 from 8.4.10 we vacuum/analyzed the db. Postgres is running in standalone mode at this point. vacuumdb --echo --analyze --all --verbose -U sysdba 07/31/12 04:09:57 INFO: analyzing "information_schema.sql_features" 07/31/12 04:09:57 INFO: "sql_features": scanned 7 of 7 pages, containing 649 live rows and 0 dead rows; 649 rows in sample, 649 estimated total rows 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" despite IgnoreSystemIndexes 07/31/12 04:09:57 INFO: vacuuming "information_schema.sql_implementation_info" 07/31/12 04:09:57 INFO: "sql_implementation_info": found 0 removable, 12 nonremovable row versions in 1 out of 1 pages 07/31/12 04:09:57 DETAIL: 0 dead row versions cannot be removed yet. 07/31/12 04:09:57 There were 1 unused item pointers. 07/31/12 04:09:57 0 pages are entirely empty. 07/31/12 04:09:57 CPU 0.00s/0.00u sec elapsed 0.00 sec. 07/31/12 04:09:57 INFO: vacuuming "pg_toast.pg_toast_11452" I see the warning generated in systable_beginscan_ordered as a warning, and then it proceeds to do the work anyway. It appears as if this is benign. Is that the case? Thanks
On Mon, Aug 27, 2012 at 10:33:22AM -0400, lsq@nym.hush.com wrote: > Hi > > After an upgrade to 8.4.12 from 8.4.10 we vacuum/analyzed the db. You downgraded a server? Depending on the fixes in minor releases, that might not work. --------------------------------------------------------------------------- > Postgres is running in standalone mode at this point. > > > vacuumdb --echo --analyze --all --verbose -U sysdba > > 07/31/12 04:09:57 INFO: analyzing > "information_schema.sql_features" > 07/31/12 04:09:57 INFO: "sql_features": scanned 7 of 7 pages, > containing 649 live rows and 0 dead rows; 649 rows in sample, 649 > estimated total rows > 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" > despite IgnoreSystemIndexes > 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" > despite IgnoreSystemIndexes > 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" > despite IgnoreSystemIndexes > 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" > despite IgnoreSystemIndexes > 07/31/12 04:09:57 INFO: vacuuming > "information_schema.sql_implementation_info" > 07/31/12 04:09:57 INFO: "sql_implementation_info": found 0 > removable, 12 nonremovable row versions in 1 out of 1 pages > 07/31/12 04:09:57 DETAIL: 0 dead row versions cannot be removed > yet. > 07/31/12 04:09:57 There were 1 unused item pointers. > 07/31/12 04:09:57 0 pages are entirely empty. > 07/31/12 04:09:57 CPU 0.00s/0.00u sec elapsed 0.00 sec. > 07/31/12 04:09:57 INFO: vacuuming "pg_toast.pg_toast_11452" > > > I see the warning generated in systable_beginscan_ordered as a > warning, and then it proceeds to do the work anyway. > > It appears as if this is benign. Is that the case? > > Thanks > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
No, you misread that, but Tom has already replied to his other message. On 8/29/12, Bruce Momjian <bruce@momjian.us> wrote: > On Mon, Aug 27, 2012 at 10:33:22AM -0400, lsq@nym.hush.com wrote: >> Hi >> >> After an upgrade to 8.4.12 from 8.4.10 we vacuum/analyzed the db. > > You downgraded a server? Depending on the fixes in minor releases, that > might not work. > > --------------------------------------------------------------------------- > > >> Postgres is running in standalone mode at this point. >> >> >> vacuumdb --echo --analyze --all --verbose -U sysdba >> >> 07/31/12 04:09:57 INFO: analyzing >> "information_schema.sql_features" >> 07/31/12 04:09:57 INFO: "sql_features": scanned 7 of 7 pages, >> containing 649 live rows and 0 dead rows; 649 rows in sample, 649 >> estimated total rows >> 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" >> despite IgnoreSystemIndexes >> 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" >> despite IgnoreSystemIndexes >> 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" >> despite IgnoreSystemIndexes >> 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" >> despite IgnoreSystemIndexes >> 07/31/12 04:09:57 INFO: vacuuming >> "information_schema.sql_implementation_info" >> 07/31/12 04:09:57 INFO: "sql_implementation_info": found 0 >> removable, 12 nonremovable row versions in 1 out of 1 pages >> 07/31/12 04:09:57 DETAIL: 0 dead row versions cannot be removed >> yet. >> 07/31/12 04:09:57 There were 1 unused item pointers. >> 07/31/12 04:09:57 0 pages are entirely empty. >> 07/31/12 04:09:57 CPU 0.00s/0.00u sec elapsed 0.00 sec. >> 07/31/12 04:09:57 INFO: vacuuming "pg_toast.pg_toast_11452" >> >> >> I see the warning generated in systable_beginscan_ordered as a >> warning, and then it proceeds to do the work anyway. >> >> It appears as if this is benign. Is that the case? >> >> Thanks >> >> >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- Michael Wood <esiotrot@gmail.com>
not sure why the second message posted a day after the first. It had bounced... sorry about that. On Wed, 29 Aug 2012 17:51:05 -0400 Michael Wood <esiotrot@gmail.com> wrote: >No, you misread that, but Tom has already replied to his other >message. > >On 8/29/12, Bruce Momjian <bruce@momjian.us> wrote: >> On Mon, Aug 27, 2012 at 10:33:22AM -0400, lsq@nym.hush.com >wrote: >>> Hi >>> >>> After an upgrade to 8.4.12 from 8.4.10 we vacuum/analyzed the >db. >> >> You downgraded a server? Depending on the fixes in minor >releases, that >> might not work. >> >> ----------------------------------------------------------------- >---------- >> >> >>> Postgres is running in standalone mode at this point. >>> >>> >>> vacuumdb --echo --analyze --all --verbose -U sysdba >>> >>> 07/31/12 04:09:57 INFO: analyzing >>> "information_schema.sql_features" >>> 07/31/12 04:09:57 INFO: "sql_features": scanned 7 of 7 >pages, >>> containing 649 live rows and 0 dead rows; 649 rows in sample, >649 >>> estimated total rows >>> 07/31/12 04:09:57 WARNING: using index >"pg_toast_2619_index" >>> despite IgnoreSystemIndexes >>> 07/31/12 04:09:57 WARNING: using index >"pg_toast_2619_index" >>> despite IgnoreSystemIndexes >>> 07/31/12 04:09:57 WARNING: using index >"pg_toast_2619_index" >>> despite IgnoreSystemIndexes >>> 07/31/12 04:09:57 WARNING: using index >"pg_toast_2619_index" >>> despite IgnoreSystemIndexes >>> 07/31/12 04:09:57 INFO: vacuuming >>> "information_schema.sql_implementation_info" >>> 07/31/12 04:09:57 INFO: "sql_implementation_info": found 0 >>> removable, 12 nonremovable row versions in 1 out of 1 pages >>> 07/31/12 04:09:57 DETAIL: 0 dead row versions cannot be >removed >>> yet. >>> 07/31/12 04:09:57 There were 1 unused item pointers. >>> 07/31/12 04:09:57 0 pages are entirely empty. >>> 07/31/12 04:09:57 CPU 0.00s/0.00u sec elapsed 0.00 sec. >>> 07/31/12 04:09:57 INFO: vacuuming "pg_toast.pg_toast_11452" >>> >>> >>> I see the warning generated in systable_beginscan_ordered as a >>> warning, and then it proceeds to do the work anyway. >>> >>> It appears as if this is benign. Is that the case? >>> >>> Thanks >>> >>> >>> >>> -- >>> Sent via pgsql-novice mailing list (pgsql- >novice@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-novice >> >> -- >> Bruce Momjian <bruce@momjian.us> http://momjian.us >> EnterpriseDB >http://enterprisedb.com >> >> + It's impossible for everything to be true. + >> >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice >> > > >-- >Michael Wood <esiotrot@gmail.com>
Hi We use standalone mode because we have found that (anecdotally) it completes faster. We do the procedure because it's a scripted operation on servers of different point releases and its easier to always do it than to code for which ones need and which ones don't - same script every time (and we also reindex in a separate step) Thanks On Tue, 28 Aug 2012 12:24:51 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: >lsq@nym.hush.com writes: >> After an upgrade to 8.4.12 from 8.4.10 we vacuum/analyzed the >db. >> Postgres is running in standalone mode at this point. > >Why were you using standalone mode? And why were you using >ignore_system_indexes? This whole procedure seems like overkill >for a routine minor-version update. > >> 07/31/12 04:09:57 WARNING: using index "pg_toast_2619_index" > >> despite IgnoreSystemIndexes > >> I see the warning generated in systable_beginscan_ordered as a >> warning, and then it proceeds to do the work anyway. >> It appears as if this is benign. Is that the case? > >It is unless you have some reason to think that that index is >corrupt... > > regards, tom lane