vacuumdb is failing with NUMBER OF INDEX TUPLES NOT THE SAME AS HEAP

[Denis Braekhus]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lonni Friedman wrote:
|>>Then nothing for a month after going to 7.3.4, and now its happening
|>>every vacuumdb run again.
|>Once the complaint starts appearing, I'd expect it to continue until you
|>reindex the index.
| That's exactly what happens. It consistantly errors until reindexed.
| Any suggestions? thanks.

A long shot, but we experienced a similar kind of issue, though our
dataset was a bit different (not so many large objects).
After having reindexed a couple of times with about the same results
(working fine for some time, then complaining again), I dropped the
index and recreated it. That seemed to fix the issue at our
installation, we had no such problems after that. Ran for some months
after that before I took the plunge and moved to 7.4..

This might not at all work for you though, and as removing the index
will probably kill your performance for a while I cannot promise you a
fix with this workaround..

Best Regards
- --
Denis
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2-nr2 (Windows XP)

iD8DBQFAmqdovsC A6eRGOOARAptYAJ 0YF6+tGtyA55hqO f8UaF+q2JnN4wCf dQtV
r4nBoDljvFDGlXh 3OhNSPcM=
=6xLx
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Lonni Friedman]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT THE SAME AS HEAP

Thanks for your reply. I thought (perhaps erroneously) that there
wasn't any real difference between dropping an index then recreating
it, and just reindexing an index?

On Thu, 06 May 2004 23:00:25 +0200, Denis Braekhus <denis@startsid en.no> wrote:[color=blue]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Lonni Friedman wrote:
> |>>Then nothing for a month after going to 7.3.4, and now its happening
> |>>every vacuumdb run again.
> |>Once the complaint starts appearing, I'd expect it to continue until you
> |>reindex the index.
> | That's exactly what happens. It consistantly errors until reindexed.
> | Any suggestions? thanks.
>
> A long shot, but we experienced a similar kind of issue, though our
> dataset was a bit different (not so many large objects).
> After having reindexed a couple of times with about the same results
> (working fine for some time, then complaining again), I dropped the
> index and recreated it. That seemed to fix the issue at our
> installation, we had no such problems after that. Ran for some months
> after that before I took the plunge and moved to 7.4..
>
> This might not at all work for you though, and as removing the index
> will probably kill your performance for a while I cannot promise you a
> fix with this workaround..
>
> Best Regards
> - --
> Denis[/color]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Denis Braekhus]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lonni Friedman wrote:

| Thanks for your reply. I thought (perhaps erroneously) that there
| wasn't any real difference between dropping an index then recreating
| it, and just reindexing an index?

I am definitely not sure, and I agree it sounds logical that they would
produce the same results. However my experience was that dropping and
re-creating the index worked.

The docs say :
"Another approach to dealing with a corrupted user-table index is just
to drop and recreate it. This may in fact be preferable if you would
like to maintain some semblance of normal operation on the table
meanwhile. REINDEX acquires exclusive lock on the table, while CREATE
INDEX only locks out writes not reads of the table."

Indicating that they should produce the same results, but that they work
differently. I am not sure what that implies, but maybe someone else knows ?

Regards
- --
Denis
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2-nr2 (Windows XP)

iD8DBQFAnPzivsC A6eRGOOARAl1OAK C0zcgN409n7ylgy HV61J9/o4LsBgCgqEpJ
yT24Y03fQItzhbR lxHyUg8s=
=YBoz
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

Denis Braekhus <denis@startsid en.no> writes:[color=blue]
> Indicating that they should produce the same results, but that they work
> differently. I am not sure what that implies, but maybe someone else knows ?[/color]

The only difference the docs are talking about is what kind of lock is
held while the rebuild proceeds.

A reindex builds a new index file from scratch, and AFAICS should give
the same results as dropping/recreating the index --- at least in terms
of what's in the file proper. The only theory I can come up with for
your experience is that there was some corruption in the system catalog
rows describing the index. That would not get fixed by a reindex.
However, I haven't the foggiest idea what sort of corruption might
allow the index to seem to work (and not, say, crash the reindex itself
which is going to use that information...) yet allow problems to appear
much later on. Too bad the evidence is gone now.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Denis Braekhus]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
|>Indicating that they should produce the same results, but that they work
|>differently. I am not sure what that implies, but maybe someone else
knows ?
| The only difference the docs are talking about is what kind of lock is
| held while the rebuild proceeds.

Yes I understood that, but the docs didn't (as you do now) excplicitly
explain the different ways they work. I expected it to be as you say,
however I was not 100% sure. Thanks for clarifying.

| A reindex builds a new index file from scratch, and AFAICS should give
| the same results as dropping/recreating the index --- at least in terms
| of what's in the file proper. The only theory I can come up with for
| your experience is that there was some corruption in the system catalog
| rows describing the index. That would not get fixed by a reindex.
| However, I haven't the foggiest idea what sort of corruption might
| allow the index to seem to work (and not, say, crash the reindex itself
| which is going to use that information...) yet allow problems to appear
| much later on. Too bad the evidence is gone now.

Yes, sorry about not bringing up the issue at the right time, however my
main focus at that time was to bring the production system back to normal..

Regards
- --
Denis
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2-nr2 (Windows XP)

iD8DBQFAnh+gvsC A6eRGOOARAlivAK Cl8aIuii8GeSFLe tWn+exBVXnptwCe KMUr
wjAEgS7gP1LQeS/xZdiC03g=
=ZRI6
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Andrew Sullivan]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

On Sat, May 08, 2004 at 12:31:28PM -0400, Tom Lane wrote:[color=blue]
> However, I haven't the foggiest idea what sort of corruption might
> allow the index to seem to work (and not, say, crash the reindex itself
> which is going to use that information...) yet allow problems to appear
> much later on. Too bad the evidence is gone now.[/color]

Dunno if this is any help, but on a 7.2 system I saw a REINDEX which
was interrupted leave the index at least partially working. We ended
up with an index which seemed fine, but which didn't contain certain
rows (so those rows were not visible when the SELECT criterion was
the indexed field). This was extremely puzzling, but a DROP INDEX;
CREATE INDEX pair solved it.

A

--
Andrew Sullivan | ajs@crankycanuc k.ca
The plural of anecdote is not data.
--Roger Brinner

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

Andrew Sullivan <ajs@crankycanu ck.ca> writes:[color=blue]
> Dunno if this is any help, but on a 7.2 system I saw a REINDEX which
> was interrupted leave the index at least partially working. We ended
> up with an index which seemed fine, but which didn't contain certain
> rows (so those rows were not visible when the SELECT criterion was
> the indexed field). This was extremely puzzling, but a DROP INDEX;
> CREATE INDEX pair solved it.[/color]

Hmm, I would expect that behavior for an overwrite-in-place REINDEX,
but 7.2 only seems to use overwrite-in-place for critical system
catalogs. What were you reindexing exactly? Were you running a
standalone backend?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Andrew Sullivan]

Re: vacuumdb is failing with NUMBER OF INDEX TUPLES NOT

On Mon, May 10, 2004 at 07:49:42PM -0400, Tom Lane wrote:[color=blue]
>
> Hmm, I would expect that behavior for an overwrite-in-place REINDEX,
> but 7.2 only seems to use overwrite-in-place for critical system
> catalogs. What were you reindexing exactly? Were you running a
> standalone backend?[/color]

Not as far as I know (I didn't cause the problem, I only fixed it
later, so I'm relying on the report of the person who ticked the
problem to understand what happened). It was definitely on a user
column, and IIRC it was a two-field unique index. My memory is hazy
on it, though -- I didn't report it because it wasn't a system I
could leave in that state for debugging.

A

--
Andrew Sullivan | ajs@crankycanuc k.ca

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend