Thread: Vacuumdb error

Vacuumdb error

From

Gipsz Jakab

Date:

14 April 2011, 11:56:23

Dear List,

Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine maintenance script has started (vacuumdb --all --full --analyze), and stopped with this error:

sqlstate=23505ERROR: duplicate key value violates unique constraint "pg_index_indexrelid_index"

sqlstate=23505DETAIL: Key (indexrelid)=(2678) already exists.

We didn't do or change anything in postgre enviroment (except we raised the max_lock_per_transaction from 64 to 128)

I've started a search on the internet, because of this error, but I didn't find anything, that I can use. Any idea? I've checked the postgres DB / catalogs / pg_catalog / tables / pg_index, and there is the record where indexrelid=2678, but there is only one record there...

I saw, that somebody has the same problem (with the same ID!), and Tom Lane and Robert Haas requested the (select ctid,xmin,xmax,* from pg_index;) query output in a file. See attached.

PostgreSQL: PostgreSQL 9.0.3 on i386-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 32-bit

Thanks, and regards,

Carl

Attachment

data.csv

Re: Vacuumdb error

From

Carl von Clausewitz

Date:

14 April 2011, 13:17:03

Ok, thanks, I'll try at night.

Regards,

Carl

2011/4/14 Vidhya Bondre <vbondre@zedo.com>

Gipsz,

We got this error too what we did is ran vacuum analyze verbose and afterthat reindexed the db and we din't see the error croping again.

Regards
Vidhya

On Thu, Apr 14, 2011 at 5:26 PM, Gipsz Jakab <clausewitz45@gmail.com> wrote:
Dear List,

Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine maintenance script has started (vacuumdb --all --full --analyze), and stopped with this error:

sqlstate=23505ERROR: duplicate key value violates unique constraint "pg_index_indexrelid_index"
sqlstate=23505DETAIL: Key (indexrelid)=(2678) already exists.

We didn't do or change anything in postgre enviroment (except we raised the max_lock_per_transaction from 64 to 128)

I've started a search on the internet, because of this error, but I didn't find anything, that I can use. Any idea? I've checked the postgres DB / catalogs / pg_catalog / tables / pg_index, and there is the record where indexrelid=2678, but there is only one record there...

I saw, that somebody has the same problem (with the same ID!), and Tom Lane and Robert Haas requested the (select ctid,xmin,xmax,* from pg_index;) query output in a file. See attached.

PostgreSQL: PostgreSQL 9.0.3 on i386-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 32-bit

Thanks, and regards,
Carl

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Vacuumdb error

From

Vidhya Bondre

Date:

14 April 2011, 14:35:43

Gipsz,

We got this error too what we did is ran vacuum analyze verbose and afterthat reindexed the db and we din't see the error croping again.

Regards

Vidhya

On Thu, Apr 14, 2011 at 5:26 PM, Gipsz Jakab <clausewitz45@gmail.com> wrote:

Dear List,

Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine maintenance script has started (vacuumdb --all --full --analyze), and stopped with this error:

sqlstate=23505ERROR: duplicate key value violates unique constraint "pg_index_indexrelid_index"
sqlstate=23505DETAIL: Key (indexrelid)=(2678) already exists.

We didn't do or change anything in postgre enviroment (except we raised the max_lock_per_transaction from 64 to 128)

I've started a search on the internet, because of this error, but I didn't find anything, that I can use. Any idea? I've checked the postgres DB / catalogs / pg_catalog / tables / pg_index, and there is the record where indexrelid=2678, but there is only one record there...

I saw, that somebody has the same problem (with the same ID!), and Tom Lane and Robert Haas requested the (select ctid,xmin,xmax,* from pg_index;) query output in a file. See attached.

PostgreSQL: PostgreSQL 9.0.3 on i386-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 32-bit

Thanks, and regards,
Carl

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Vacuumdb error

From

Tom Lane

Date:

14 April 2011, 16:25:19

Gipsz Jakab <clausewitz45@gmail.com> writes:
> Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine
> maintenance script has started (vacuumdb --all --full --analyze), and
> stopped with this error:

> sqlstate=23505ERROR:  duplicate key value violates unique constraint
> "pg_index_indexrelid_index"
> sqlstate=23505DETAIL:  Key (indexrelid)=(2678) already exists.

Maybe you had better show us *all* of your "routine maintenance" steps,
because there are some fairly unexplainable things in that pg_index dump.
VACUUM FULL alone wouldn't have resulted in the pg_index and pg_class
indexes' entries having updated xmins:

> "(1,40)";"48741";"0";2678;2610;1;false;false;true;false;true;false;true
> "(1,41)";"48741";"0";2679;2610;1;true;false;true;false;true;true;true
> "(1,42)";"49791";"0";2662;1259;1;true;false;true;false;true;true;true
> "(1,43)";"49791";"0";2663;1259;2;true;false;true;false;true;true;true

and I'm wondering how come three of them have indcheckxmin true too.
I think you must be whacking the system catalogs around harder than
you've admitted to.

            regards, tom lane

Re: Vacuumdb error

From

Carl von Clausewitz

Date:

14 April 2011, 16:37:04

Hi,

see the two scripts attached. First one is the postgres_maintenance.sh, and the second is the postgres_backup.sh. I've attached it, and copied, because of the antivirus filters :-)

regards,

Carl

Maintenance:

#!/bin/sh

date >> /var/log/postgresql_maintenance.log

/usr/local/bin/reindexdb --all --username=cvc >> /var/log/postgresql_maintenance.log

echo "Reindex done" >> /var/log/postgresql_maintenance.log

/usr/local/bin/vacuumdb --all --full --analyze --username=cvc >> /var/log/postgresql_maintenance.log

echo "Vacuum done" >> /var/log/postgresql_maintenance.log

Backup:

#!/bin/sh

date >> /var/log/postgresql_backup.log

/usr/local/bin/pg_dump --create --file=/usr/NAS/mentesek/postgres/$(date +%Y%m%d).backup --format=c --no-owner --username=cvc ktv_migracio_access

echo "Backup done" >> /var/log/postgresql_backup.log

2011/4/14 Tom Lane <tgl@sss.pgh.pa.us>

Gipsz Jakab <clausewitz45@gmail.com> writes:
> Today morning at 01:00 AM in our PostgreSQL 9.0.3 server a routine
> maintenance script has started (vacuumdb --all --full --analyze), and
> stopped with this error:

> sqlstate=23505ERROR: duplicate key value violates unique constraint
> "pg_index_indexrelid_index"
> sqlstate=23505DETAIL: Key (indexrelid)=(2678) already exists.

Maybe you had better show us *all* of your "routine maintenance" steps,
because there are some fairly unexplainable things in that pg_index dump.
VACUUM FULL alone wouldn't have resulted in the pg_index and pg_class
indexes' entries having updated xmins:

> "(1,40)";"48741";"0";2678;2610;1;false;false;true;false;true;false;true
> "(1,41)";"48741";"0";2679;2610;1;true;false;true;false;true;true;true
> "(1,42)";"49791";"0";2662;1259;1;true;false;true;false;true;true;true
> "(1,43)";"49791";"0";2663;1259;2;true;false;true;false;true;true;true

and I'm wondering how come three of them have indcheckxmin true too.
I think you must be whacking the system catalogs around harder than
you've admitted to.

regards, tom lane

Attachment

Re: Vacuumdb error

From

Tom Lane

Date:

14 April 2011, 16:46:25

Carl von Clausewitz <clausewitz45@gmail.com> writes:
> Maintenance:
> #!/bin/sh
> date >> /var/log/postgresql_maintenance.log
> /usr/local/bin/reindexdb --all --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Reindex done" >> /var/log/postgresql_maintenance.log
> /usr/local/bin/vacuumdb --all --full --analyze --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Vacuum done" >> /var/log/postgresql_maintenance.log

Just FYI, the reindex step is 100% useless if you're going to do a
vacuum full afterwards.

Before 9.0 there was some value in doing vacuum full and then reindex,
but none whatsoever in the other ordering.  As of 9.0 there's just no
point at all in doing both.  VACUUM FULL rebuilds the indexes anyway.

            regards, tom lane

Re: Vacuumdb error

From

Carl von Clausewitz

Date:

14 April 2011, 16:52:26

Ok thanks, the information. I've made the mistake, I will change the script, but I will try, that Vidhya told me. Let me see, what will going on.

Regards,

Carl

2011/4/14 Tom Lane <tgl@sss.pgh.pa.us>

Carl von Clausewitz <clausewitz45@gmail.com> writes:
> Maintenance:
> #!/bin/sh
> date >> /var/log/postgresql_maintenance.log
> /usr/local/bin/reindexdb --all --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Reindex done" >> /var/log/postgresql_maintenance.log
> /usr/local/bin/vacuumdb --all --full --analyze --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Vacuum done" >> /var/log/postgresql_maintenance.log

Just FYI, the reindex step is 100% useless if you're going to do a
vacuum full afterwards.

Before 9.0 there was some value in doing vacuum full and then reindex,
but none whatsoever in the other ordering. As of 9.0 there's just no
point at all in doing both. VACUUM FULL rebuilds the indexes anyway.

regards, tom lane

Re: Vacuumdb error

From

Carl von Clausewitz

Date:

14 April 2011, 20:18:03

Everything was fine, the reordered script fixed everything. Thanks all.

Regards,

Carl

2011/4/14 Carl von Clausewitz <clausewitz45@gmail.com>

Ok thanks, the information. I've made the mistake, I will change the script, but I will try, that Vidhya told me. Let me see, what will going on.

Regards,
Carl

2011/4/14 Tom Lane <tgl@sss.pgh.pa.us>
Carl von Clausewitz <clausewitz45@gmail.com> writes:
> Maintenance:
> #!/bin/sh
> date >> /var/log/postgresql_maintenance.log
> /usr/local/bin/reindexdb --all --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Reindex done" >> /var/log/postgresql_maintenance.log
> /usr/local/bin/vacuumdb --all --full --analyze --username=cvc >>
> /var/log/postgresql_maintenance.log
> echo "Vacuum done" >> /var/log/postgresql_maintenance.log

Just FYI, the reindex step is 100% useless if you're going to do a
vacuum full afterwards.

Before 9.0 there was some value in doing vacuum full and then reindex,
but none whatsoever in the other ordering. As of 9.0 there's just no
point at all in doing both. VACUUM FULL rebuilds the indexes anyway.

regards, tom lane

Re: Vacuumdb error

From

Tom Lane

Date:

15 April 2011, 20:34:54

Carl von Clausewitz <clausewitz45@gmail.com> writes:
>>> sqlstate=23505ERROR:  duplicate key value violates unique constraint
>>> "pg_index_indexrelid_index"
>>> sqlstate=23505DETAIL:  Key (indexrelid)=(2678) already exists.

After a considerable amount of fooling around I've been able to
reproduce this and identify the cause:
http://archives.postgresql.org/pgsql-hackers/2011-04/msg00777.php

A real fix will be forthcoming in the next update releases (not Monday's
updates, unfortunately; too late for that).  In the meantime, it appears
that the problem can only be triggered by a sequence that involves
*both* REINDEX and VACUUM FULL on pg_index.  Since, as I mentioned
before, a separate reindex step is completely useless when using 9.0's
VACUUM FULL, removing the reindex step from your maintenance script
should be an adequate workaround.

            regards, tom lane