Re: Cannot create unique index - Mailing list pgsql-general

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
>