vacumm error

Started by Stefanos Harhalakisover 23 years ago4 messagesbugs
Jump to latest
#1Stefanos Harhalakis
v13@it.teithe.gr

I'm running postgresql 7.2.1 on linux.

I cannot run vacuumm on a table in a database i'm running for about 7 months.
I get:

ERROR: No one parent tuple was found

I've found an older posting about that but the poster said that after
restarting it was fixed. In my case this is not true. I've restarted
postgresql but it didn't work. Noone else is trying to use this database
(other databases are used).

test=> vacuum full analyze verbose entities;
NOTICE: --Relation entities--
NOTICE: Pages 312833: Changed 107130, reaped 273241, Empty 0, New 0; Tup
9895954: Vac 9005277, Keep/VTL 0/0, UnUsed 44715182, MinLen 36, MaxLen 36;
Re-using: Free/Avail. Space 1945751280/1945279044; EndEmpty/Avail. Pages
0/273480.
CPU 31.30s/9.81u sec elapsed 138.29 sec.
NOTICE: Index entities_pkey: Pages 277210; Tuples 9895954: Deleted 9005277.
CPU 33.33s/75.02u sec elapsed 401.49 sec.
ERROR: No one parent tuple was found

I had fsync=false in postgresql.conf and the machine crashed but i didn't
notice anything strange since then. I've seen the database to grow up each
day after i did a kill -9 to two db backends. At that time postgre restarted
telling me that the shared memory area is possibly corrupted.

The table entities is an one column table (don't ask why :):

test=# \d entities
Table "entities"
Column | Type | Modifiers
--------+---------+------------------------------------------------
id | integer | not null default nextval('seq_entities'::text)
Primary key: entities_pkey
Triggers: RI_ConstraintTrigger_26872259,
RI_ConstraintTrigger_26872261,
RI_ConstraintTrigger_26872268,
RI_ConstraintTrigger_26872270,
RI_ConstraintTrigger_26872277,
RI_ConstraintTrigger_26872279,
RI_ConstraintTrigger_26872286,
RI_ConstraintTrigger_26872288,
RI_ConstraintTrigger_26872295,
RI_ConstraintTrigger_26872297,
RI_ConstraintTrigger_26872304,
RI_ConstraintTrigger_26872306,
RI_ConstraintTrigger_26872313,
RI_ConstraintTrigger_26872315,
RI_ConstraintTrigger_26872323,
RI_ConstraintTrigger_26872325,
RI_ConstraintTrigger_26872333,
RI_ConstraintTrigger_26872335,
RI_ConstraintTrigger_26872343,
RI_ConstraintTrigger_26872345,
RI_ConstraintTrigger_26872353,
RI_ConstraintTrigger_26872355,
RI_ConstraintTrigger_26872250,
RI_ConstraintTrigger_26872252,
RI_ConstraintTrigger_101919110,
RI_ConstraintTrigger_101919112

I'm currently running vacumm full on another (larger) table
(table entities has 312K pages and this one has 1.3M pages)

TIA

<<V13>>

#2Rod Taylor
rbt@rbt.ca
In reply to: Stefanos Harhalakis (#1)
Re: vacumm error

On Fri, 2002-11-22 at 15:51, Stefanos Harhalakis wrote:

I'm running postgresql 7.2.1 on linux.

I cannot run vacuumm on a table in a database i'm running for about 7 months.
I get:

ERROR: No one parent tuple was found

Your best bet, since you've crashed with fsync off, is to dump and
reload the database. This should clean up any data issues you currently
have -- assuming pg_dump functions on it.

That said, 7.2.3 has several vacuum fixes, but I don't think they'll fix
your problem.

--
Rod Taylor <rbt@rbt.ca>

#3Stefanos Harhalakis
v13@it.teithe.gr
In reply to: Rod Taylor (#2)
Re: vacumm error

On Tuesday 26 November 2002 16:43, Rod Taylor wrote:

On Fri, 2002-11-22 at 15:51, Stefanos Harhalakis wrote:

I'm running postgresql 7.2.1 on linux.

I cannot run vacuumm on a table in a database i'm running for about 7
months. I get:

ERROR: No one parent tuple was found

Your best bet, since you've crashed with fsync off, is to dump and
reload the database. This should clean up any data issues you currently
have -- assuming pg_dump functions on it.

That said, 7.2.3 has several vacuum fixes, but I don't think they'll fix
your problem.

I did a SELECT * FROM table FOR UPDATE; and it solved the problem...

thnx for your answer.

<<V13>>

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stefanos Harhalakis (#1)
Re: vacumm error

Stefanos Harhalakis <v13@it.teithe.gr> writes:

I'm running postgresql 7.2.1 on linux.
I cannot run vacuumm on a table in a database i'm running for about 7 months.
I get:

ERROR: No one parent tuple was found

I've found an older posting about that but the poster said that after
restarting it was fixed. In my case this is not true.

There is a known form of this problem that does not go away on restart.
I can't recall at the moment if it's fixed in 7.2.3 or not (been a long
day ...) It is definitely fixed for 7.3 though.

If you don't want to update to 7.3 in the near future, I think there is
a workaround involving doing SELECT FOR UPDATE to clear out the bogus
state. Check the pghackers archives from back around August for
details.

regards, tom lane