Thread: Cannot create unique index
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 --------------------------------------------------------
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
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)
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
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 >
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
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
"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