Thread: Cannot create unique index

Cannot create unique index

From
"Henrik Steffen"
Date:
Hello all,

REINDEXING a table I get the following message:

ERROR:   Cannot create unique index. Table contains
non-unique values.

How can that be in REINDEXING ?

This means, that the index has been corrupted before,
because the index has always been unique. But somehow
the postmaster must have succeeded in inserting a non-unique
value anyway.

Now the index is corrupted, and I get every 10-15 minutes
a message, telling me the following:

The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.


Haven't had this for quite a while now (using 7.3.3)

Any idea?

In my opinion this should not be possible...

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------


Re: Cannot create unique index

From
"Henrik Steffen"
Date:
yes, of course, I allready did this.

What I was aiming at, was, that postgres
should normally not insert a duplicate value
into a unique index, should it?

Isn't this a bug?

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Andrew Gould" <andrewgould@yahoo.com>
To: "Henrik Steffen" <steffen@city-map.de>
Sent: Monday, June 30, 2003 2:46 PM
Subject: Re: [GENERAL] Cannot create unique index


> --- Henrik Steffen <steffen@city-map.de> wrote:
> >
> > Hello all,
> >
> > REINDEXING a table I get the following message:
> >
> > ERROR:   Cannot create unique index. Table contains
> > non-unique values.
> >
> > How can that be in REINDEXING ?
> >
> > This means, that the index has been corrupted
> > before,
> > because the index has always been unique. But
> > somehow
> > the postmaster must have succeeded in inserting a
> > non-unique
> > value anyway.
> >
> > Now the index is corrupted, and I get every 10-15
> > minutes
> > a message, telling me the following:
> >
> > The Postmaster has informed me that some other
> > backend
> > died abnormally and possibly corrupted shared
> > memory.
> > I have rolled back the current transaction and am
> > going to terminate your database system connection
> > and exit.
> > Please reconnect to the database system and repeat
> > your query.
> >
> >
> > Haven't had this for quite a while now (using 7.3.3)
> >
> > Any idea?
> >
> > In my opinion this should not be possible...
> >
> > Henrik Steffen
>
> You might try:
>
> 1. Drop the unique index.
> 2. Perform a query to check for duplicates.
> 3. Remove duplicates, if any.
> 4. Vacuum the table.
> 5. Recreate the unique index.
>
> Best of luck,
>
> Andrew Gould


Re: Cannot create unique index

From
Andrew Gould
Date:
Could you identify duplicated index values? Or were
the messages erroneous?

--- Henrik Steffen <steffen@city-map.de> wrote:
> yes, of course, I allready did this.
>
> What I was aiming at, was, that postgres
> should normally not insert a duplicate value
> into a unique index, should it?
>
> Isn't this a bug?
>
> --
>
> Mit freundlichem Gru�
>
> Henrik Steffen
> Gesch�ftsf�hrer
>
> top concepts Internetmarketing GmbH
> Am Steinkamp 7 - D-21684 Stade - Germany
>
--------------------------------------------------------
> http://www.topconcepts.com          Tel. +49 4141
> 991230
> mail: steffen@topconcepts.com       Fax. +49 4141
> 991233
>
--------------------------------------------------------
> 24h-Support Hotline:  +49 1908 34697 (EUR
> 1.86/Min,topc)
>
--------------------------------------------------------
> Ihr SMS-Gateway: JETZT NEU unter:
> http://sms.city-map.de
> System-Partner gesucht:
> http://www.franchise.city-map.de
>
--------------------------------------------------------
> Handelsregister: AG Stade HRB 5811 - UstId: DE
> 213645563
>
--------------------------------------------------------
>
> ----- Original Message -----
> From: "Andrew Gould" <andrewgould@yahoo.com>
> To: "Henrik Steffen" <steffen@city-map.de>
> Sent: Monday, June 30, 2003 2:46 PM
> Subject: Re: [GENERAL] Cannot create unique index
>
>
> > --- Henrik Steffen <steffen@city-map.de> wrote:
> > >
> > > Hello all,
> > >
> > > REINDEXING a table I get the following message:
> > >
> > > ERROR:   Cannot create unique index. Table
> contains
> > > non-unique values.
> > >
> > > How can that be in REINDEXING ?
> > >
> > > This means, that the index has been corrupted
> > > before,
> > > because the index has always been unique. But
> > > somehow
> > > the postmaster must have succeeded in inserting
> a
> > > non-unique
> > > value anyway.
> > >
> > > Now the index is corrupted, and I get every
> 10-15
> > > minutes
> > > a message, telling me the following:
> > >
> > > The Postmaster has informed me that some other
> > > backend
> > > died abnormally and possibly corrupted shared
> > > memory.
> > > I have rolled back the current transaction and
> am
> > > going to terminate your database system
> connection
> > > and exit.
> > > Please reconnect to the database system and
> repeat
> > > your query.
> > >
> > >
> > > Haven't had this for quite a while now (using
> 7.3.3)
> > >
> > > Any idea?
> > >
> > > In my opinion this should not be possible...
> > >
> > > Henrik Steffen
> >
> > You might try:
> >
> > 1. Drop the unique index.
> > 2. Perform a query to check for duplicates.
> > 3. Remove duplicates, if any.
> > 4. Vacuum the table.
> > 5. Recreate the unique index.
> >
> > Best of luck,
> >
> > Andrew Gould
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


Re: Cannot create unique index

From
"Henrik Steffen"
Date:
yes, there were duplicates

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Andrew Gould" <andrewgould@yahoo.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Monday, June 30, 2003 3:39 PM
Subject: Re: [GENERAL] Cannot create unique index


> Could you identify duplicated index values? Or were
> the messages erroneous?
>
> --- Henrik Steffen <steffen@city-map.de> wrote:
> > yes, of course, I allready did this.
> >
> > What I was aiming at, was, that postgres
> > should normally not insert a duplicate value
> > into a unique index, should it?
> >
> > Isn't this a bug?
> >
> > --
> >
> > Mit freundlichem Gruß
> >
> > Henrik Steffen
> > Geschäftsführer
> >
> > top concepts Internetmarketing GmbH
> > Am Steinkamp 7 - D-21684 Stade - Germany
> >
> --------------------------------------------------------
> > http://www.topconcepts.com          Tel. +49 4141
> > 991230
> > mail: steffen@topconcepts.com       Fax. +49 4141
> > 991233
> >
> --------------------------------------------------------
> > 24h-Support Hotline:  +49 1908 34697 (EUR
> > 1.86/Min,topc)
> >
> --------------------------------------------------------
> > Ihr SMS-Gateway: JETZT NEU unter:
> > http://sms.city-map.de
> > System-Partner gesucht:
> > http://www.franchise.city-map.de
> >
> --------------------------------------------------------
> > Handelsregister: AG Stade HRB 5811 - UstId: DE
> > 213645563
> >
> --------------------------------------------------------
> >
> > ----- Original Message -----
> > From: "Andrew Gould" <andrewgould@yahoo.com>
> > To: "Henrik Steffen" <steffen@city-map.de>
> > Sent: Monday, June 30, 2003 2:46 PM
> > Subject: Re: [GENERAL] Cannot create unique index
> >
> >
> > > --- Henrik Steffen <steffen@city-map.de> wrote:
> > > >
> > > > Hello all,
> > > >
> > > > REINDEXING a table I get the following message:
> > > >
> > > > ERROR:   Cannot create unique index. Table
> > contains
> > > > non-unique values.
> > > >
> > > > How can that be in REINDEXING ?
> > > >
> > > > This means, that the index has been corrupted
> > > > before,
> > > > because the index has always been unique. But
> > > > somehow
> > > > the postmaster must have succeeded in inserting
> > a
> > > > non-unique
> > > > value anyway.
> > > >
> > > > Now the index is corrupted, and I get every
> > 10-15
> > > > minutes
> > > > a message, telling me the following:
> > > >
> > > > The Postmaster has informed me that some other
> > > > backend
> > > > died abnormally and possibly corrupted shared
> > > > memory.
> > > > I have rolled back the current transaction and
> > am
> > > > going to terminate your database system
> > connection
> > > > and exit.
> > > > Please reconnect to the database system and
> > repeat
> > > > your query.
> > > >
> > > >
> > > > Haven't had this for quite a while now (using
> > 7.3.3)
> > > >
> > > > Any idea?
> > > >
> > > > In my opinion this should not be possible...
> > > >
> > > > Henrik Steffen
> > >
> > > You might try:
> > >
> > > 1. Drop the unique index.
> > > 2. Perform a query to check for duplicates.
> > > 3. Remove duplicates, if any.
> > > 4. Vacuum the table.
> > > 5. Recreate the unique index.
> > >
> > > Best of luck,
> > >
> > > Andrew Gould
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: Cannot create unique index

From
"scott.marlowe"
Date:
how odd.  Since reindex works by dropping the index then recreating it, is
it possible that some process inserted duplicates in the split second
there was no index?

for safety's sake, I've always reindexed in a transaction:

begin;
drop index bubba;
create index bubba on ...
commit;

On Tue, 1 Jul 2003, Henrik Steffen wrote:

> yes, there were duplicates
>
> --
>
> Mit freundlichem Gruß
>
> Henrik Steffen
> Geschäftsführer
>
> top concepts Internetmarketing GmbH
> Am Steinkamp 7 - D-21684 Stade - Germany
> --------------------------------------------------------
> http://www.topconcepts.com          Tel. +49 4141 991230
> mail: steffen@topconcepts.com       Fax. +49 4141 991233
> --------------------------------------------------------
> 24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
> --------------------------------------------------------
> Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
> System-Partner gesucht: http://www.franchise.city-map.de
> --------------------------------------------------------
> Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> --------------------------------------------------------
>
> ----- Original Message -----
> From: "Andrew Gould" <andrewgould@yahoo.com>
> To: "Henrik Steffen" <steffen@city-map.de>
> Cc: "pgsql" <pgsql-general@postgresql.org>
> Sent: Monday, June 30, 2003 3:39 PM
> Subject: Re: [GENERAL] Cannot create unique index
>
>
> > Could you identify duplicated index values? Or were
> > the messages erroneous?
> >
> > --- Henrik Steffen <steffen@city-map.de> wrote:
> > > yes, of course, I allready did this.
> > >
> > > What I was aiming at, was, that postgres
> > > should normally not insert a duplicate value
> > > into a unique index, should it?
> > >
> > > Isn't this a bug?
> > >
> > > --
> > >
> > > Mit freundlichem Gruß
> > >
> > > Henrik Steffen
> > > Geschäftsführer
> > >
> > > top concepts Internetmarketing GmbH
> > > Am Steinkamp 7 - D-21684 Stade - Germany
> > >
> > --------------------------------------------------------
> > > http://www.topconcepts.com          Tel. +49 4141
> > > 991230
> > > mail: steffen@topconcepts.com       Fax. +49 4141
> > > 991233
> > >
> > --------------------------------------------------------
> > > 24h-Support Hotline:  +49 1908 34697 (EUR
> > > 1.86/Min,topc)
> > >
> > --------------------------------------------------------
> > > Ihr SMS-Gateway: JETZT NEU unter:
> > > http://sms.city-map.de
> > > System-Partner gesucht:
> > > http://www.franchise.city-map.de
> > >
> > --------------------------------------------------------
> > > Handelsregister: AG Stade HRB 5811 - UstId: DE
> > > 213645563
> > >
> > --------------------------------------------------------
> > >
> > > ----- Original Message -----
> > > From: "Andrew Gould" <andrewgould@yahoo.com>
> > > To: "Henrik Steffen" <steffen@city-map.de>
> > > Sent: Monday, June 30, 2003 2:46 PM
> > > Subject: Re: [GENERAL] Cannot create unique index
> > >
> > >
> > > > --- Henrik Steffen <steffen@city-map.de> wrote:
> > > > >
> > > > > Hello all,
> > > > >
> > > > > REINDEXING a table I get the following message:
> > > > >
> > > > > ERROR:   Cannot create unique index. Table
> > > contains
> > > > > non-unique values.
> > > > >
> > > > > How can that be in REINDEXING ?
> > > > >
> > > > > This means, that the index has been corrupted
> > > > > before,
> > > > > because the index has always been unique. But
> > > > > somehow
> > > > > the postmaster must have succeeded in inserting
> > > a
> > > > > non-unique
> > > > > value anyway.
> > > > >
> > > > > Now the index is corrupted, and I get every
> > > 10-15
> > > > > minutes
> > > > > a message, telling me the following:
> > > > >
> > > > > The Postmaster has informed me that some other
> > > > > backend
> > > > > died abnormally and possibly corrupted shared
> > > > > memory.
> > > > > I have rolled back the current transaction and
> > > am
> > > > > going to terminate your database system
> > > connection
> > > > > and exit.
> > > > > Please reconnect to the database system and
> > > repeat
> > > > > your query.
> > > > >
> > > > >
> > > > > Haven't had this for quite a while now (using
> > > 7.3.3)
> > > > >
> > > > > Any idea?
> > > > >
> > > > > In my opinion this should not be possible...
> > > > >
> > > > > Henrik Steffen
> > > >
> > > > You might try:
> > > >
> > > > 1. Drop the unique index.
> > > > 2. Perform a query to check for duplicates.
> > > > 3. Remove duplicates, if any.
> > > > 4. Vacuum the table.
> > > > 5. Recreate the unique index.
> > > >
> > > > Best of luck,
> > > >
> > > > Andrew Gould
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the
> > > unregister command
> > >     (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: Cannot create unique index

From
Martijn van Oosterhout
Date:
Unlikely. Create index takes a write lock on a table. Reindex takes an
exclusive lock (or something like that). Unless something really bad happens
it shouldn't be a problem.

I've had index corruption before. We're running 7.0 (upgrading Real Soon
Now) and there are certain situations that can corrupt indexes, mostly
involving backend segmentation faults. More recent versions should be quite
resiliant against this.

On Tue, Jul 01, 2003 at 06:50:36AM -0600, scott.marlowe wrote:
> how odd.  Since reindex works by dropping the index then recreating it, is
> it possible that some process inserted duplicates in the split second
> there was no index?
>
> for safety's sake, I've always reindexed in a transaction:
>
> begin;
> drop index bubba;
> create index bubba on ...
> commit;

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

Attachment

Re: Cannot create unique index

From
"Henrik Steffen"
Date:
Hello,

before the reindexing starts, the webserver is always shut down.

as there are no database-users except the web-users, there should
not be a possibility of inserting a non-unique value just between
DROP INDEX and CREATE INDEX....

allthough, sometimes an apache child process may take a few seconds
to exit... I will put a delay between webserver-stop and reindexing,
maybe this helps...

the other possibility is segfaults or other hardware problems. We have
often had this kind of problem with earlier postgres versions
before (this has been discussed on the list several times). but since
7.3.3 we have never had it again. On none of our servers.


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Martijn van Oosterhout" <kleptog@svana.org>
To: "scott.marlowe" <scott.marlowe@ihs.com>
Cc: "Henrik Steffen" <steffen@city-map.de>; "Andrew Gould"
<andrewgould@yahoo.com>; "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, July 01, 2003 3:04 PM
Subject: Re: [GENERAL] Cannot create unique index



Re: Cannot create unique index

From
Tom Lane
Date:
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> how odd.  Since reindex works by dropping the index then recreating it, is
> it possible that some process inserted duplicates in the split second
> there was no index?

No, because reindex holds exclusive lock on the table for the whole
process.

            regards, tom lane