Re: [ADMIN] Vacuum error on database postgres

I'm running postgreSQL 8.1.4 on Red Hat Enterprise Linux 3.

Things have been working well for a while but in the last few days, I've
gotten the following error during a nightly vacuum.

postgres=# vacuum analyze;
ERROR: duplicate key violates unique constraint
"pg_statistic_relid_att_index"

I can vacuum that table individually without problems.

postgres=# vacuum pg_statistic;
VACUUM
postgres=# vacuum analyze pg_statistic;
VACUUM
postgres=#

I found a posting from 2004 that suggested the following query.

postgres=# select starelid, staattnum, count(*) from pg_statistic
group by 1,2 having count(*) > 1;
starelid | staattnum | count
----------+-----------+-------
2608 | 3 | 2
10723 | 7 | 2
10723 | 4 | 2
10723 | 5 | 2
10723 | 2 | 2
10723 | 3 | 2
10728 | 1 | 2
10728 | 2 | 2
10728 | 3 | 2
10728 | 4 | 2
10728 | 5 | 2
10738 | 1 | 2
(12 rows)

I did delete exactly one of each of these using ctid and the query then
shows no duplicates. But, the problem comes right back in the next
database-wide vacuum.

I think the objects are as given below.

postgres=# select relname,oid,reltype from pg_class where oid in
(2608,10723,10728,10738);
relname | oid | reltype
-------------------------+-------+---------
sql_features | 10723 | 10724
sql_implementation_info | 10728 | 10729
sql_packages | 10738 | 10739
pg_depend | 2608 | 10277
(4 rows)

I also tried reindexing the table.

postgres=# reindex table pg_statistic;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
postgres=#

Help!

Paul

"Paul B. Anderson" <paul(dot)a(at)pnlassociates(dot)com> writes:
> I did delete exactly one of each of these using ctid and the query then
> shows no duplicates. But, the problem comes right back in the next
> database-wide vacuum.

That's pretty odd --- I'm inclined to suspect index corruption.

> I also tried reindexing the table.

Get rid of the duplicates (actually, I'd just blow away all the
pg_statistic entries for each of these tables) and *then* reindex.
Then re-analyze and see what happens.

Worst case you could just delete everything in pg_statistic, reindex it,
do a database-wide ANALYZE to repopulate it. By definition there's not
any original data in that table...

regards, tom lane

I removed the duplicates and then immediately reindexed. All is well.
The vacuum analyze on the postgres database works now too. Thanks.

It is good to know the pg_statistic table can be emptied in case this
ever happens again.

Paul

Tom Lane wrote:
> "Paul B. Anderson" <paul(dot)a(at)pnlassociates(dot)com> writes:
>
>> I did delete exactly one of each of these using ctid and the query then
>> shows no duplicates. But, the problem comes right back in the next
>> database-wide vacuum.
>>
>
> That's pretty odd --- I'm inclined to suspect index corruption.
>
>
>> I also tried reindexing the table.
>>
>
> Get rid of the duplicates (actually, I'd just blow away all the
> pg_statistic entries for each of these tables) and *then* reindex.
> Then re-analyze and see what happens.
>
> Worst case you could just delete everything in pg_statistic, reindex it,
> do a database-wide ANALYZE to repopulate it. By definition there's not
> any original data in that table...
>
> regards, tom lane
>
> .
>
>

Tom Lane wrote:
> "Paul B. Anderson" <paul(dot)a(at)pnlassociates(dot)com> writes:
>> I did delete exactly one of each of these using ctid and the query then
>> shows no duplicates. But, the problem comes right back in the next
>> database-wide vacuum.
>
> That's pretty odd --- I'm inclined to suspect index corruption.
>
>> I also tried reindexing the table.
>
> Get rid of the duplicates (actually, I'd just blow away all the
> pg_statistic entries for each of these tables) and *then* reindex.
> Then re-analyze and see what happens.
>
> Worst case you could just delete everything in pg_statistic, reindex it,
> do a database-wide ANALYZE to repopulate it. By definition there's not
> any original data in that table...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Hello, newbe here..

I seem to have run across this same error. And I believe I can
re-create it too.

I'm running 8.1.4 on slackware-(almost 11).

I have two scripts, one create's tables and indexes, the other has lots
of "copy from stdin" statements. The very last line is

VACUUM VERBOSE ANALYZE;

which eventually gives me the same error. This is a test box, with test
data, and this seemed to work:

delete from pg_statistic;
reindex table pg_statistic;
vacuum analyze;

So I'm ok, but I tried it again, by dropping the database and re-running
both scripts and got the same error again. So thought I'd offer a test
case if there was interest.

The data is 46 meg compressed and 500'ish meg uncompressed. Its a
little bit sensitive, so I'd like to be a little discreet with it, but
can put it on a website for a developer to download.

Please contact me privately for a link:

andy(at)squeakycode(dot)net

...Oh, also, this box has locked up on me a few times, so its not the
most stable thing in the world. It did not lock up during import of
this data, but there is flaky hardware in there someplace, so it could
very well be a hardware issue.

-Andy

andy <andy(at)squeakycode(dot)net> writes:
> So I'm ok, but I tried it again, by dropping the database and re-running
> both scripts and got the same error again. So thought I'd offer a test
> case if there was interest.

Absolutely. I've seen just enough of these reports to make me think
there's an underlying bug.

regards, tom lane

andy <andy(at)squeakycode(dot)net> writes:
> Tom Lane wrote:
>> andy <andy(at)squeakycode(dot)net> writes:
>>> So I'm ok, but I tried it again, by dropping the database and re-running
>>> both scripts and got the same error again. So thought I'd offer a test
>>> case if there was interest.
>>
>> Absolutely. I've seen just enough of these reports to make me think
>> there's an underlying bug.

> Here are some urls: ...

Doh ... I think the critical bit is here:

autovacuum = on # enable autovacuum subprocess?

The problem is that ANALYZE takes only AccessShareLock on a table,
so it's entirely possible for two backends to try to ANALYZE the
same table concurrently, and in particular for autovacuum to try to
do so while your foreground VACUUM ANALYZE is running. That leads
to concurrent insertion attempts into pg_statistic for the same key.

This behavior dates from a time when there was no good alternative.
One possible fix today would be to make ANALYZE take
ShareUpdateExclusive lock instead, thus ensuring there is only one
ANALYZE at a time on a table. However I'm a bit concerned by the
possibility that ANALYZE-inside-a-transaction could accumulate a
whole bunch of such locks in a random order, leading at least to
a risk of deadlocks against other ANALYZEs. (We have to hold the
lock till commit, else we aren't fixing the problem.) Do we need a
specialized lock type just for ANALYZE? Would sorting the target
list of rel OIDs be enough? Perhaps it's not worth worrying about?

regards, tom lane

> This behavior dates from a time when there was no good alternative.
> One possible fix today would be to make ANALYZE take
> ShareUpdateExclusive lock instead, thus ensuring there is only one
> ANALYZE at a time on a table. However I'm a bit concerned by the
> possibility that ANALYZE-inside-a-transaction could accumulate a
> whole bunch of such locks in a random order, leading at least to
> a risk of deadlocks against other ANALYZEs. (We have to hold the
> lock till commit, else we aren't fixing the problem.) Do we need a
> specialized lock type just for ANALYZE? Would sorting the target
> list of rel OIDs be enough? Perhaps it's not worth worrying about?
>

Why not an internal lock that people don't see? The behavior would the
following:

conn1: analyze foo;

conn2: analyze foo;

ERROR: analyze already running on foo

conn1: analyze foo;
conn2: analyze;

NOTICE: analyze full started, analyze running on foo, skipping foo

Sincerely,

Joshua D. Drake

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> One possible fix today would be to make ANALYZE take
>> ShareUpdateExclusive lock instead, thus ensuring there is only one
>> ANALYZE at a time on a table.

> Why not an internal lock that people don't see?

We could add another LockTagType just for ANALYZE, but that seems like
rather a lot of infrastructure to support an extremely narrow corner
case, namely two people doing database-wide ANALYZE at the same time
inside transaction blocks. (If they do it outside a transaction block
then the ANALYZE is divided into multiple xacts and so doesn't try to
hold locks on multiple tables concurrently. autovacuum won't try to do
that either.) There's no such animal as "an internal lock people don't
see" --- if we went this way it'd propagate into user-visible entries in
pg_locks, for example.

ISTM it should be sufficient to use ShareUpdateExclusiveLock. The only
real argument I can see against it is you couldn't ANALYZE and VACUUM
a table at the same time ... but that's probably a bad idea anyway,
especially if we extend ANALYZE to estimate dead-tuple statistics.

regards, tom lane

On Thu, 2006-09-14 at 11:20 -0400, Tom Lane wrote:
> andy <andy(at)squeakycode(dot)net> writes:
> > Tom Lane wrote:
> >> andy <andy(at)squeakycode(dot)net> writes:
> This behavior dates from a time when there was no good alternative.
> One possible fix today would be to make ANALYZE take
> ShareUpdateExclusive lock instead, thus ensuring there is only one
> ANALYZE at a time on a table. However I'm a bit concerned by the
> possibility that ANALYZE-inside-a-transaction could accumulate a
> whole bunch of such locks in a random order, leading at least to
> a risk of deadlocks against other ANALYZEs. (We have to hold the
> lock till commit, else we aren't fixing the problem.) Do we need a
> specialized lock type just for ANALYZE? Would sorting the target
> list of rel OIDs be enough? Perhaps it's not worth worrying about?
>

How would creating a new lock type avoid deadlocks when an ANALYZE is
accumulating the locks in random order?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> How would creating a new lock type avoid deadlocks when an ANALYZE is
> accumulating the locks in random order?

In itself it wouldn't. Josh Drake sketched the idea in more detail
later: if there is a lock type used *only* for ANALYZE, then you can do
ConditionalLockAcquire on it, and if you fail, skip the table on the
assumption that someone else is already doing what you came to do.

The whole thing seems a bit too cute/complicated though; it'd open
various corner cases such as: ANALYZE, run complex query, query takes a
week because it's using out-of-date stats because previous ANALYZE-r
hadn't committed yet. I'd rather ANALYZE always analyzed than sometimes
fell through without doing anything.

regards, tom lane

On Thu, 2006-09-14 at 18:25 -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > How would creating a new lock type avoid deadlocks when an ANALYZE is
> > accumulating the locks in random order?
>
> In itself it wouldn't. Josh Drake sketched the idea in more detail
> later: if there is a lock type used *only* for ANALYZE, then you can do
> ConditionalLockAcquire on it, and if you fail, skip the table on the
> assumption that someone else is already doing what you came to do.
>
> The whole thing seems a bit too cute/complicated though; it'd open
> various corner cases such as: ANALYZE, run complex query, query takes a
> week because it's using out-of-date stats because previous ANALYZE-r
> hadn't committed yet. I'd rather ANALYZE always analyzed than sometimes
> fell through without doing anything.
>

Couldn't you just sort by the table names, and ANALYZE the tables in
that order? Would that effectively prevent the deadlocks?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> Couldn't you just sort by the table names, and ANALYZE the tables in
> that order? Would that effectively prevent the deadlocks?

That'd work too, I think (I suggested the variant of ordering by OID,
which is simpler and more reliable). Not sure if it's really worth the
trouble though --- how many people do you think are doing concurrent
whole-database ANALYZEs inside transaction blocks?

As-is the code will do the analyzes in pg_class physical row order,
which is almost good enough --- only if someone did a schema change that
forced a pg_class row update between the starts of the two ANALYZE runs
would it possibly fail. So the use-case for a fix is really kinda narrow.

regards, tom lane

Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> Couldn't you just sort by the table names, and ANALYZE the tables in
>> that order? Would that effectively prevent the deadlocks?
>
> That'd work too, I think (I suggested the variant of ordering by OID,
> which is simpler and more reliable). Not sure if it's really worth the
> trouble though --- how many people do you think are doing concurrent
> whole-database ANALYZEs inside transaction blocks?
>
> As-is the code will do the analyzes in pg_class physical row order,
> which is almost good enough --- only if someone did a schema change that
> forced a pg_class row update between the starts of the two ANALYZE runs
> would it possibly fail. So the use-case for a fix is really kinda narrow.
>
> regards, tom lane

Honestly, its not that big a problem, and if there were some doc's,
faq's, etc (and people on the newsgroups) I dont think you should even
worry about it.

It makes sense to me, and if Tom had come back and said, yeah, here is
why, cuz you run autovacuum and at then end of the script you did a
vacuum... they are conflicting... dont do that. I'd be cool with that.
As soon as its common knowledge I think it could be avoided.

Really, isn't it just bulk loads anyway where a person might do this?

-Andy

On Thu, Sep 14, 2006 at 06:25:42PM -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > How would creating a new lock type avoid deadlocks when an ANALYZE is
> > accumulating the locks in random order?
>
> In itself it wouldn't. Josh Drake sketched the idea in more detail
> later: if there is a lock type used *only* for ANALYZE, then you can do
> ConditionalLockAcquire on it, and if you fail, skip the table on the
> assumption that someone else is already doing what you came to do.

Wouldn't it be useful for ANALYZE to do a conditional lock anyway and
skip if it can't acquire. Especially for the analyse-from-autovacuum
case, perhaps an ANALYSE NOLOCK or whatever.

For stuff run from autovacuum, would it be reasonable for the
automatically run version to just abort if it sees someone doing the
same thing?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hi, Tom,

Tom Lane wrote:
> We could add another LockTagType just for ANALYZE, but that seems like
> rather a lot of infrastructure to support an extremely narrow corner
> case, namely two people doing database-wide ANALYZE at the same time
> inside transaction blocks. (If they do it outside a transaction block
> then the ANALYZE is divided into multiple xacts and so doesn't try to
> hold locks on multiple tables concurrently. autovacuum won't try to do
> that either.)

Is there any reason to allow ANALYZE run insinde a transaction at all?

Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

On 2006-09-15, Markus Schaber <schabi(at)logix-tt(dot)com> wrote:
> Is there any reason to allow ANALYZE run insinde a transaction at all?

Absolutely. In a large transaction that radically changes the content of
the database, it is often necessary to analyze in order to avoid getting
extremely bad query plans for later commands in the transaction.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Hi, Andrew,

Andrew - Supernews wrote:

>> Is there any reason to allow ANALYZE run insinde a transaction at all?
>
> Absolutely. In a large transaction that radically changes the content of
> the database, it is often necessary to analyze in order to avoid getting
> extremely bad query plans for later commands in the transaction.

OK, I see.

But this leads to the danger that, should the transaction abort
afterwards, we're left with borked stats, or are those rolled back
accordingly?

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Markus Schaber <schabi(at)logix-tt(dot)com> writes:

> Is there any reason to allow ANALYZE run insinde a transaction at all?

I had a script to run explain over a set of queries, then run analyze, then
run explain again and check the plans for unexpected changes. It would roll
back the analyze if any production queries had changed plans and email the
diff to the DBA to review.

Actually I never finished the script but that was the plan :)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> For stuff run from autovacuum, would it be reasonable for the
> automatically run version to just abort if it sees someone doing the
> same thing?

Not especially --- there's no guarantee that the other guy is going to
commit at all. And autovac is only holding one lock at a time so it's
not a factor in the deadlock issue anyway.

regards, tom lane

Here are a couple of new data points on this issue.

Deleting all records in pg_statistic and then reindexing clears the
problem but I've had the problem in two of my own databases in two
separate postgresql instances as well as the postgres database in both
instances.

I have a cluster of machines and the databases are on shared disk
storage. I'm just getting this arrangement working and, while I've been
debugging my scripts, I've accidentally had two copies of postgresql
running against the same initdb directories at the same time on two
different machines. Needless to say, this causes problems and I'm
thinking that vacuuming in this condition may be at or near the root of
the problem.

Paul

Tom Lane wrote:
> andy <andy(at)squeakycode(dot)net> writes:
>
>> So I'm ok, but I tried it again, by dropping the database and re-running
>> both scripts and got the same error again. So thought I'd offer a test
>> case if there was interest.
>>
>
> Absolutely. I've seen just enough of these reports to make me think
> there's an underlying bug.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
> .
>
>

"Paul B. Anderson" <paul(dot)a(at)pnlassociates(dot)com> writes:
> I have a cluster of machines and the databases are on shared disk
> storage. I'm just getting this arrangement working and, while I've been
> debugging my scripts, I've accidentally had two copies of postgresql
> running against the same initdb directories at the same time on two
> different machines. Needless to say, this causes problems and I'm
> thinking that vacuuming in this condition may be at or near the root of
> the problem.

You're going to have much worse problems than this from that. Two
postmasters modifying the same data files is a recipe for complete
corruption. I would recommend abandoning any database you realize
this has happened to: just initdb and start over. The chances of
being able to fix it are minimal.

regards, tom lane