Postgres Pro
Downloads
Home   >   mailing lists

Thread: Vacuum full?

Vacuum full?

From
Dmitry Tkach
Date:
Hi, everybody!

I am having a weird problem with one of the tables in the database...
It seems to be working fine, whatever I do with it, EXCEPT, when I try
to to VACUUM FULL, it sits there for a while, and then fails with a message:

ERROR:  No one parent tuple was found

Does anybody know what it means, and, more importantly, what could have caused it, and
how it can be fixed (dropping and recreating the table is not an option - it is fairly
large, and there is a whole bunch of other tables that are referencing this one)...

Any help would be greatly appreciated!

Thanks a lot!

Dima

Re: Vacuum full?

From
Tom Lane
Date:
Dmitry Tkach <dmitry@openratings.com> writes:
> ERROR:  No one parent tuple was found

Closing out other open transactions will probably make that go away.
You should also consider updating to 7.2.3, if you're not there already.
(IIRC, there is a partial fix in 7.2.3.  7.3 will fix all the causes we
know about.)

            regards, tom lane

Re: Vacuum full?

From
Dmitry Tkach
Date:
Thanks, Tom!

By closing other transactions, did you mean ALL transactions open in
that database, or just the ones, having anything to do with that table
(reading/writing)?

Thanks again!

Dima

Tom Lane wrote:

>Dmitry Tkach <dmitry@openratings.com> writes:
>
>>ERROR:  No one parent tuple was found
>>
>
>Closing out other open transactions will probably make that go away.
>You should also consider updating to 7.2.3, if you're not there already.
>(IIRC, there is a partial fix in 7.2.3.  7.3 will fix all the causes we
>know about.)
>
>            regards, tom lane
>

Re: Vacuum full?

From
Tom Lane
Date:
Dmitry Tkach <dmitry@openratings.com> writes:
> By closing other transactions, did you mean ALL transactions open in
> that database, or just the ones, having anything to do with that table
> (reading/writing)?

I mean ALL.

You can start some new ones right away, but your current open
transactions are determining what VACUUM thinks might be a still-visible
tuple.  You need to make the "oldest open transaction number" move
forward.

            regards, tom lane

Re: Vacuum full?

From
Vivek Khera
Date:
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> Dmitry Tkach <dmitry@openratings.com> writes:
>> ERROR:  No one parent tuple was found

TL> Closing out other open transactions will probably make that go away.
TL> You should also consider updating to 7.2.3, if you're not there already.
TL> (IIRC, there is a partial fix in 7.2.3.  7.3 will fix all the causes we
TL> know about.)

Funny, I was just about to ask this question...  I took down my DB
system (made it inaccessible to all external apps by the access list)
then ran vacuum full verbose analyze.  This is what I got:

NOTICE:  --Relation user_list--
NOTICE:  Pages 644320: Changed 1273, reaped 627556, Empty 0, New 0; Tup 9668482: Vac 290, Keep/VTL 0/0, UnUsed
26051805,MinLen 100, MaxLen 528; Re-using: Free/Avail. Space 3645214756/3644567436; EndEmpty/Avail. Pages 0/630623. 
        CPU 56.31s/1.01u sec elapsed 178.60 sec.
NOTICE:  Index user_list_pkey: Pages 51806; Tuples 9668482: Deleted 290.
        CPU 5.08s/14.16u sec elapsed 118.28 sec.
ERROR:  No one parent tuple was found


It looks like it did reap a bunch of pages, which is good, but the
error gives me the willies.

Curiously, last time I did this (July 4), I got the same error, so I
really doubt it is an open transaction, unless such a thing survives
db shutdown and restart ;-)

When 7.3 comes out, I'll do a dump/reload so the problem *should* go
away then... right now I'm at 7.2.1.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: Vacuum full?

From
Tom Lane
Date:
Vivek Khera <khera@kcilink.com> writes:
> Curiously, last time I did this (July 4), I got the same error, so I
> really doubt it is an open transaction, unless such a thing survives
> db shutdown and restart ;-)

Yes, there is one known variant that survives a shutdown.  I am certain
that that form of the problem is patched in 7.2.3-RH (Red Hat's
release), but don't recall if it's in the community 7.2.3 release or
not.  In any case the fix is in 7.3, along with fixes for other cases
that can produce the same error message.

            regards, tom lane

Re: Vacuum full?

From
Vivek Khera
Date:
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> Vivek Khera <khera@kcilink.com> writes:
>> Curiously, last time I did this (July 4), I got the same error, so I
>> really doubt it is an open transaction, unless such a thing survives
>> db shutdown and restart ;-)

TL> Yes, there is one known variant that survives a shutdown.  I am certain
TL> that that form of the problem is patched in 7.2.3-RH (Red Hat's
TL> release), but don't recall if it's in the community 7.2.3 release or
TL> not.  In any case the fix is in 7.3, along with fixes for other cases
TL> that can produce the same error message.

Wow... even if I cleanly shut down all my applications, then shut down
the server I can run into this?

I guess 7.3 can't come soon enough! ;-)

I suppose it is high time to pull down the 7.3b5 and give it a whirl
on the development box.

Thanks.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/