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

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 to worry 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 values in the table and couldnt find one. This sounds like a corrupted index. Vacuum analyse does alter the table but reindex does 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 are stored in rows. They are looked up and sorted/reconnected with the main row at query time. It sounds like an index used here 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 make sure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularly prone 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

--
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/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 make sure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularly prone 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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; 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
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 has to 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 values in the table and couldnt find one. This sounds like a corrupted index. Vacuum analyse does alter the table but reindex does 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 are stored in rows. They are looked up and sorted/reconnected with the main row at query time. It sounds like an index used here 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 make sure memory, CPU's and hard drives are all happy and not reporting problems. I have found overheating servers to be particularly prone 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

--
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/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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; 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
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; 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general