Обсуждение: FW: getting error while running sql on mm_activealrm table
Hi all,
We are getting following error message on doing any action on the table like(Select or open from pgadmin).
Please suggest.
ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619
********** Error **********
ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619
SQL state: XX000
CREATE TABLE mm_activealarm
(
alarm_id integer NOT NULL,
source_address character varying(255) NOT NULL,
alarm_instance_id integer NOT NULL,
alarm_raise_time bigint,
alarm_update_time bigint,
alarm_cease_time bigint,
alarm_count integer,
alarm_severity integer NOT NULL,
source_type character varying(40) NOT NULL,
alarm_state integer NOT NULL,
event_type integer,
notification_id integer NOT NULL,
probable_cause integer NOT NULL,
specific_problem integer NOT NULL,
alarm_additional_text character varying(10240),
alarm_ack_time bigint,
alarm_ack_user character varying(100) NOT NULL,
alarm_ack_system character varying(100) NOT NULL,
alarm_proposed_repair_action character varying(10240) NOT NULL,
CONSTRAINT mm_activealarm_pk PRIMARY KEY (alarm_id, source_address)
USING INDEX TABLESPACE mgrdata
)
WITH (
OIDS=FALSE
)
TABLESPACE mgrdata;
ALTER TABLE ss_activealarm
OWNER TO ss_super;
Regards
Tarkeshwar
On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the table > like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, Thanks Adrian for you quick reply. I am looking in this that why db came into this stage. --I have found overheating servers to be particularly prone to index corruption and if indexes can get corrupt one has toworry about data becoming ---corrupt too. Is the above statement true? What is the meaming of toasted values? What is this pg_toast? Can you please share any link on this? Note from the shared link -------------------------------------- Given that what you did was a reindex, what probably happened was it used an index scan to try to locate the toasted valuesin the table and couldnt find one. This sounds like a corrupted index. Vacuum analyse does alter the table but reindexdoes not and the changes are very minor. The way to think about this is that TOASTed attributes are actually broken into chunks of about 4k in size and these arestored in rows. They are looked up and sorted/reconnected with the main row at query time. It sounds like an index usedhere was corrupted and so the reindex solved the problem. I have found corrupted indexes are usually a sign that something is not well with the server. It is good to check and makesure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularlyprone to index corruption and if indexes can get corrupt one has to worry about data becoming corrupt too. Regards Tarkeshwar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: 03 December 2014 20:22 To: M Tarkeshwar Rao; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the > table like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/3/2014 9:30 PM, M Tarkeshwar Rao wrote: > I have found corrupted indexes are usually a sign that something is not well with the server. It is good to check and makesure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularlyprone to index corruption and if indexes can get corrupt one has to worry about data becoming corrupt too. do your servers not have ECC memory? the number one source of data corruption is cached data getting transient one-bit errors, which occur at a more common rate than you might expect if you have many gigabytes of ram... ECC will fix and report these single bit errors. -- john r pierce 37N 122W somewhere on the middle of the left coast
Hi, We performed the following actions to recover 1. Restart the DB 2. Rebuild the index 3. Vacume the index mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# REINDEX table pg_toast.pg_toast_2619; REINDEX mgrdb=# VACUUM ANALYZE mm_activealarm; VACUUM mgrdb=# commit; WARNING: there is no transaction in progress COMMIT mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# 4. Finally we drop the MM_ActiveAlarm table.and recreated it 5. Still after that we were not able to see the column even after recreating the table with columns there are no columns present in table itself Can you please suggest more on this ? Regards Tarkeshwar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: 03 December 2014 20:22 To: M Tarkeshwar Rao; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the > table like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com
Hi all, Finally DB was restored from the backup and the issue was solved. Is the data corrupted? I feel that data is corrupted as well as its some of the data dictionary also corrupted. That is the reason it is not displaying any columns for the table even the table is present. Regards Tarkeshwar -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of M Tarkeshwar Rao Sent: 04 December 2014 16:38 To: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi, We performed the following actions to recover 1. Restart the DB 2. Rebuild the index 3. Vacume the index mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# REINDEX table pg_toast.pg_toast_2619; REINDEXmgrdb=# VACUUM ANALYZE mm_activealarm; VACUUM mgrdb=# commit; WARNING: there is no transaction in progress COMMIT mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# 4. Finally we drop the MM_ActiveAlarm table.and recreated it 5. Still after that we were not able to see the column evenafter recreating the table with columns there are no columns present in table itself Can you please suggest more on this ? Regards Tarkeshwar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: 03 December 2014 20:22 To: M Tarkeshwar Rao; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the > table like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 12/03/2014 09:30 PM, M Tarkeshwar Rao wrote: > Hi Adrian, > > Thanks Adrian for you quick reply. I am looking in this that why db came into this stage. > > --I have found overheating servers to be particularly prone to index corruption and if indexes can get corrupt one hasto worry about data becoming ---corrupt too. > > Is the above statement true? All I can say is that equipment is designed to run in a range of temperatures. The temperature goes outside that range and the chance increases that something will go wrong. Whether that affects indexes more is not something I could say. > What is the meaming of toasted values? > What is this pg_toast? Can you please share any link on this? http://www.postgresql.org/docs/9.3/static/storage-toast.html > > Note from the shared link > -------------------------------------- > > Given that what you did was a reindex, what probably happened was it used an index scan to try to locate the toasted valuesin the table and couldnt find one. This sounds like a corrupted index. Vacuum analyse does alter the table but reindexdoes not and the changes are very minor. > > The way to think about this is that TOASTed attributes are actually broken into chunks of about 4k in size and these arestored in rows. They are looked up and sorted/reconnected with the main row at query time. It sounds like an index usedhere was corrupted and so the reindex solved the problem. > > I have found corrupted indexes are usually a sign that something is not well with the server. It is good to check and makesure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularlyprone to index corruption and if indexes can get corrupt one has to worry about data becoming corrupt too. > > > Regards > Tarkeshwar -- Adrian Klaver adrian.klaver@aklaver.com
On 12/04/2014 03:08 AM, M Tarkeshwar Rao wrote: > Hi, > > We performed the following actions to recover > > 1. Restart the DB > 2. Rebuild the index > 3. Vacume the index > > mgrdb=# select count(*) from mm_activealarm; > ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 > mgrdb=# REINDEX table pg_toast.pg_toast_2619; > REINDEX > mgrdb=# VACUUM ANALYZE mm_activealarm; > VACUUM > mgrdb=# commit; > WARNING: there is no transaction in progress > COMMIT > mgrdb=# select count(*) from mm_activealarm; > ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 > mgrdb=# > > 4. Finally we drop the MM_ActiveAlarm table.and recreated it > 5. Still after that we were not able to see the column > even after recreating the table with columns there are no columns present in table itself I am not following. There was no data in the column or the actual column did not exist? Also you say column(s) in the above so was more than one column missing? What was the data type(s) of the missing columns? So for example: DROP some_table; CREATE TABLE some_table (id serial, fld_1 int, fld_2 varchar, fld_3 boolean); When you did a \d some_table you did not see an entry for, say fld_3? > > > Can you please suggest more on this ? > > Regards > Tarkeshwar -- Adrian Klaver adrian.klaver@aklaver.com
Hi all, We have done all the suggested things from reindex and vaccume to hardware heat issue. But do not get the clue why this happened? Can you please suggest what are the checkpoints we follow so that we can avoid this kind of issue in future? Regards Tarkeshwar -----Original Message----- From: M Tarkeshwar Rao Sent: 04 December 2014 17:32 To: M Tarkeshwar Rao; Adrian Klaver; pgsql-general@postgresql.org Subject: RE: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi all, Finally DB was restored from the backup and the issue was solved. Is the data corrupted? I feel that data is corrupted as well as its some of the data dictionary also corrupted. That is the reason it is not displaying any columns for the table even the table is present. Regards Tarkeshwar -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of M Tarkeshwar Rao Sent: 04 December 2014 16:38 To: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi, We performed the following actions to recover 1. Restart the DB 2. Rebuild the index 3. Vacume the index mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# REINDEX table pg_toast.pg_toast_2619; REINDEXmgrdb=# VACUUM ANALYZE mm_activealarm; VACUUM mgrdb=# commit; WARNING: there is no transaction in progress COMMIT mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# 4. Finally we drop the MM_ActiveAlarm table.and recreated it 5. Still after that we were not able to see the column evenafter recreating the table with columns there are no columns present in table itself Can you please suggest more on this ? Regards Tarkeshwar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: 03 December 2014 20:22 To: M Tarkeshwar Rao; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the > table like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Is column size less than 8 kb will help us? -----Original Message----- From: M Tarkeshwar Rao Sent: 10 December 2014 11:51 To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi all, We have done all the suggested things from reindex and vaccume to hardware heat issue. But do not get the clue why this happened? Can you please suggest what are the checkpoints we follow so that we can avoid this kind of issue in future? Regards Tarkeshwar -----Original Message----- From: M Tarkeshwar Rao Sent: 04 December 2014 17:32 To: M Tarkeshwar Rao; Adrian Klaver; pgsql-general@postgresql.org Subject: RE: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi all, Finally DB was restored from the backup and the issue was solved. Is the data corrupted? I feel that data is corrupted as well as its some of the data dictionary also corrupted. That is the reason it is not displaying any columns for the table even the table is present. Regards Tarkeshwar -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of M Tarkeshwar Rao Sent: 04 December 2014 16:38 To: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table Hi, We performed the following actions to recover 1. Restart the DB 2. Rebuild the index 3. Vacume the index mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# REINDEX table pg_toast.pg_toast_2619; REINDEXmgrdb=# VACUUM ANALYZE mm_activealarm; VACUUM mgrdb=# commit; WARNING: there is no transaction in progress COMMIT mgrdb=# select count(*) from mm_activealarm; ERROR: missing chunk number 0 for toast value 54787 in pg_toast_2619 mgrdb=# 4. Finally we drop the MM_ActiveAlarm table.and recreated it 5. Still after that we were not able to see the column evenafter recreating the table with columns there are no columns present in table itself Can you please suggest more on this ? Regards Tarkeshwar -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: 03 December 2014 20:22 To: M Tarkeshwar Rao; pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: getting error while running sql on mm_activealrm table On 12/03/2014 03:07 AM, M Tarkeshwar Rao wrote: > Hi all, > > We are getting following error message on doing any action on the > table like(Select or open from pgadmin). > > Please suggest. > http://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value Though the question still remains, what got it into that state? Any recent hardware/software issues? > > Regards > > Tarkeshwar > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general