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

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

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

"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