Thread: vacumm error

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 month=
s.
I get:

ERROR:  No one parent tuple was found

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

test=3D> vacuum full analyze verbose entities;
NOTICE:  --Relation entities--
NOTICE:  Pages 312833: Changed 107130, reaped 273241, Empty 0, New 0; Tup=
=20
9895954: Vac 9005277, Keep/VTL 0/0, UnUsed 44715182, MinLen 36, MaxLen 36;=
=20
Re-using: Free/Avail. Space 1945751280/1945279044; EndEmpty/Avail. Pages=20
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=3Dfalse 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=3D# \d entities
                         Table "entities"
 Column |  Type   |                   Modifiers=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
--------+---------+------------------------------------------------
 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=20
(table entities has 312K pages and this one has 1.3M pages)

TIA

<<V13>>

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>

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

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