Thread: Zeroing single tuple in data file.

Hello,

I have a problem with one of the tables in my database. The thing is
that one of the rows is present in corresponding data file yet I'm
unable to perform any actions on it including simple SELECT statement.
Is there a way of deleting the tuple from the file by zeroing it and
then reindexing the table. I tried other ways of dealing with the
problem like vacuming and clustering the table but it pops out the
error and terminates when processing the given row. I have several
data folders so when I find the exact error message I will post it.

--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

Found the message but all I get is null value in column <name>
violates non-null constraint. As I said before the tuple is in the
physical file and it contains all the data which is also in other
tuples.

2010/11/21 Lukasz Brodziak <lukasz.brodziak@gmail.com>:
> Hello,
>
> I have a problem with one of the tables in my database. The thing is
> that one of the rows is present in corresponding data file yet I'm
> unable to perform any actions on it including simple SELECT statement.
> Is there a way of deleting the tuple from the file by zeroing it and
> then reindexing the table. I tried other ways of dealing with the
> problem like vacuming and clustering the table but it pops out the
> error and terminates when processing the given row. I have several
> data folders so when I find the exact error message I will post it.
>
> --
> Łukasz Brodziak
> "What if everyting around You isn't quite as it seems,
> What if all the world You think You know is an inelaborate dream
> When You look at Your reflection is that all you want it to be
> What if You could look right through the cracks
> Would You find Yourself...... Find Yourself afraid to see"
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

Can you post the select and the exact error?

A few more information requests if you don't mind.

Can you analyze the table ie "ANALYZE USERS" without error?

Could you please paste the output from "\d+ users".

And try SELECT COUNT(*) FROM users WHERE id_usr IS NULL;

Is your database large?  Can you shut down the instance cleanly and take a backup of it before making any further changes?

Could you also copy the mailing list into your reply please to ensure others can see and possibly assist?

Oh and could we please have the output from SELECT version(); too please.

Hello,

I got access to corrupted DB... The SELECT COUNT(*) FROM users where
id_usr IS NULL returned error of invalid page header in one of the
blocks...

And the ANALYZE users got me an error that one of the pg_clog files is
missing...

2010/11/21 Matthew Walden <matthew.walden@bcs.org>:
> Oh and could we please have the output from SELECT version(); too please.
>
> On Sun, Nov 21, 2010 at 7:56 PM, Matthew Walden <matthew.walden@bcs.org>
> wrote:
>>
>> A few more information requests if you don't mind.
>>
>> Can you analyze the table ie "ANALYZE USERS" without error?
>>
>> Could you please paste the output from "\d+ users".
>>
>> And try SELECT COUNT(*) FROM users WHERE id_usr IS NULL;
>>
>> Is your database large?  Can you shut down the instance cleanly and take a
>> backup of it before making any further changes?
>>
>> Could you also copy the mailing list into your reply please to ensure
>> others can see and possibly assist?
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

And the version is 8.2.4

2010/11/22 Lukasz Brodziak <lukasz.brodziak@gmail.com>:
> Hello,
>
> I got access to corrupted DB... The SELECT COUNT(*) FROM users where
> id_usr IS NULL returned error of invalid page header in one of the
> blocks...
>
> And the ANALYZE users got me an error that one of the pg_clog files is
> missing...
>
> 2010/11/21 Matthew Walden <matthew.walden@bcs.org>:
>> Oh and could we please have the output from SELECT version(); too please.
>>
>> On Sun, Nov 21, 2010 at 7:56 PM, Matthew Walden <matthew.walden@bcs.org>
>> wrote:
>>>
>>> A few more information requests if you don't mind.
>>>
>>> Can you analyze the table ie "ANALYZE USERS" without error?
>>>
>>> Could you please paste the output from "\d+ users".
>>>
>>> And try SELECT COUNT(*) FROM users WHERE id_usr IS NULL;
>>>
>>> Is your database large?  Can you shut down the instance cleanly and take a
>>> backup of it before making any further changes?
>>>
>>> Could you also copy the mailing list into your reply please to ensure
>>> others can see and possibly assist?
>>
>
>
>
> --
> Łukasz Brodziak
> "What if everyting around You isn't quite as it seems,
> What if all the world You think You know is an inelaborate dream
> When You look at Your reflection is that all you want it to be
> What if You could look right through the cracks
> Would You find Yourself...... Find Yourself afraid to see"
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

And the \d+ on users?  Also would you mind pasting the output exactly as it is appears on screen, assuming it is in English?

I'm trying to build up a picture here of how many problems you have and whether they can be fixed conventionally with the database interface or whether you need to start messing around with the data files.  If the case is the latter, you won't be able to just remove the single rows that are causing the problem - you will loose full pages.  It could be that it is actually a corrupt index rather than the table - in this case the \d+ will help me assist you in disabling that index.

You can take a look at this guide if you suspect block corruption -

http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html

BUT I would try everything else first.  You will loose data if you follow that approach (but it may not be avoidable).  If your database is a production one (I don't know the environment you are using this database in) then this would need to be a last ditch attempt to salvage otherwise lost data.  In any case it sounds like you have some kind of hardware issue and should consider a full backup restore if possible and then afterwards running frequent ANALYZE; commands to quickly detect further corruptions.

\d+ what do You mean by that?

2010/11/22 Matthew Walden <matthew.walden@bcs.org>:
> And the \d+ on users?  Also would you mind pasting the output exactly as it
> is appears on screen, assuming it is in English?
>
> I'm trying to build up a picture here of how many problems you have and
> whether they can be fixed conventionally with the database interface or
> whether you need to start messing around with the data files.  If the case
> is the latter, you won't be able to just remove the single rows that are
> causing the problem - you will loose full pages.  It could be that it is
> actually a corrupt index rather than the table - in this case the \d+ will
> help me assist you in disabling that index.
>
> You can take a look at this guide if you suspect block corruption -
>
> http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html
>
> BUT I would try everything else first.  You will loose data if you follow
> that approach (but it may not be avoidable).  If your database is a
> production one (I don't know the environment you are using this database in)
> then this would need to be a last ditch attempt to salvage otherwise lost
> data.  In any case it sounds like you have some kind of hardware issue and
> should consider a full backup restore if possible and then afterwards
> running frequent ANALYZE; commands to quickly detect further corruptions.
>
> On Mon, Nov 22, 2010 at 7:36 AM, Lukasz Brodziak <lukasz.brodziak@gmail.com>
> wrote:
>>
>> And the version is 8.2.4
>>
>> 2010/11/22 Lukasz Brodziak <lukasz.brodziak@gmail.com>:
>> > Hello,
>> >
>> > I got access to corrupted DB... The SELECT COUNT(*) FROM users where
>> > id_usr IS NULL returned error of invalid page header in one of the
>> > blocks...
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

You can describe an object with the psql command \d.  \d+ gives you extended information.  So at the psql command line if you type the following -

\d+ users

You should see a description of the layout of the table along with associated indexes.

The output is in Polish :) is there anything particular to look for?

2010/11/22 Matthew Walden <matthew.walden@bcs.org>:
> You can describe an object with the psql command \d.  \d+ gives you extended
> information.  So at the psql command line if you type the following -
>
> \d+ users
>
> You should see a description of the layout of the table along with
> associated indexes.
>
> On Mon, Nov 22, 2010 at 8:56 AM, Lukasz Brodziak <lukasz.brodziak@gmail.com>
> wrote:
>>
>> \d+ what do You mean by that?
>



--
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"

I might be being optimistic but I should be able to make sense of the \d+ output if you can post that.

Depends on your OS.  Are you using Linux or Windows?

If you're using a linux terminal window you can usually highlight it and ctrl-shift-c.  Or use the menus.