Обсуждение: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ?
2009/11/24 RD黄永卫 <yongwei_huang@gtmc.com.cn>: > Dear all, > > > > When "reindex" operation do,this error occurred: > Nov 22 10:22:27 SUC11 postgres[14145]: [2-1] ERROR: could not create unique > index > Nov 22 10:22:27 SUC11 postgres[14145]: [2-2] DETAIL: Table contains > duplicated values. > Nov 22 10:22:27 SUC11 postgres[14145]: [2-3] STATEMENT: REINDEX TABLE > "t_sfh_history" > > My question is: > > How the Duplicate datas produce when a UNIQUE index exite ? > Is this a bug of PostgreSQL ? Either bad hardware, or a pgsql bug can do this. > My postgreSQL version is : > PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 > 20030502 (Red Hat Linux 3.2.3-56) You are aware that 8.1.x is up to 8.1.18, right, that's 14 or so updates you're missing. Could one of them have fixed a bug that causes this? Sure. Look through the changelogs to be sure, but why not just keep your pgsql version up to date? It's easier. Or are you running some redhat version that stays the same number while getting bugs back ported to it or something?
2009/11/24 RD黄永卫 <yongwei_huang@gtmc.com.cn>: > > 发件人: Scott Marlowe [mailto:scott.marlowe@gmail.com] > 发送时间: 2009年11月25日 14:44 > 收件人: RD黄永卫 > 抄送: pgsql-bugs@postgresql.org; pgsql-general@postgresql.org > 主题: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? > > 2009/11/24 RD黄永卫 <yongwei_huang@gtmc.com.cn>: >> Dear all, >> >> >> >> When "reindex" operation do,this error occurred: >> Nov 22 10:22:27 SUC11 postgres[14145]: [2-1] ERROR: could not create unique >> index >> Nov 22 10:22:27 SUC11 postgres[14145]: [2-2] DETAIL: Table contains >> duplicated values. >> Nov 22 10:22:27 SUC11 postgres[14145]: [2-3] STATEMENT: REINDEX TABLE >> "t_sfh_history" >> >> My question is: >> >> How the Duplicate datas produce when a UNIQUE index exite ? >> Is this a bug of PostgreSQL ? > > > >>> My postgreSQL version is : >>> PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 >>> 20030502 (Red Hat Linux 3.2.3-56) > >> You are aware that 8.1.x is up to 8.1.18, right, that's 14 or so >> updates you're missing. Could one of them have fixed a bug that >> causes this? Sure. Look through the changelogs to be sure, but why >> not just keep your pgsql version up to date? It's easier. >> >> Or are you running some redhat version that stays the same number >> while getting bugs back ported to it or something? > > Either , or a pgsql bug can do this. > > What kind of " bad hardware " cause that ? May I reproduce that ? Any kind of bad hardware. Bad memory, cpu, motherboard, RAID controller, possibly the OS and on and on. memtest86+ is a good starting place to see if your hardware has cpu / ram /mobo issues. But first, update pgsql. Then hunt down your duplicates and fix the data in the database. I'm pretty sure there were a few problems like this in the early 8.1 series releases.
Scott Marlowe wrote:
That GCC string suggests this is a RHEL3 system, which would have shipped with PostgreSQL 7.3. Not sure how they got 8.1 onto there, but a later 8.1 is certainly a useful first step to take here, before they get any more corruption from that ancient version when trying to fix things. Compiling PostgreSQL on RHEL3 from source has some fun challenges; http://markmail.org/message/2bclakrjfxtgwcge#query:+page:1+mid:jhj3yf7zxfmsi22i+state:results covers the main one I'm aware of. And unfortunately http://yum.pgsqlrpms.org/8.1/redhat/rhel3.0/ seems to be empty, so a source build may be the only good route to get a newer version onto there.My postgreSQL version is :PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-56)You are aware that 8.1.x is up to 8.1.18, right, that's 14 or so updates you're missing. Could one of them have fixed a bug that causes this? Sure. Look through the changelogs to be sure, but why not just keep your pgsql version up to date? It's easier. Or are you running some redhat version that stays the same number while getting bugs back ported to it or something?
-- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
Greg Smith <greg@2ndquadrant.com> writes: > That GCC string suggests this is a RHEL3 system, which would have > shipped with PostgreSQL 7.3. Not sure how they got 8.1 onto there, but a > later 8.1 is certainly a useful first step to take here, before they get > any more corruption from that ancient version when trying to fix things. > Compiling PostgreSQL on RHEL3 from source has some fun challenges; > http://markmail.org/message/2bclakrjfxtgwcge#query:+page:1+mid:jhj3yf7zxfmsi22i+state:results > covers the main one I'm aware of. And unfortunately > http://yum.pgsqlrpms.org/8.1/redhat/rhel3.0/ seems to be empty, so a > source build may be the only good route to get a newer version onto there. The least painful route for a user of an RPM build would be to grab the latest SRPM they can find and then modify that specfile to reference the newer tarball. This should be a relatively mechanical thing (although you might have to adjust some of the applied patches). The advantage over just doing a naive build from source is that the update would install into the right places, play nicely with the package system etc. regards, tom lane
发件人: Scott Marlowe [mailto:scott.marlowe@gmail.com] 发送时间: 2009年11月25日 14:44 收件人: RD黄永卫 抄送: pgsql-bugs@postgresql.org; pgsql-general@postgresql.org 主题: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? 2009/11/24 RD黄永卫 <yongwei_huang@gtmc.com.cn>: > Dear all, > > > > When "reindex" operation do,this error occurred: > Nov 22 10:22:27 SUC11 postgres[14145]: [2-1] ERROR: could not create unique > index > Nov 22 10:22:27 SUC11 postgres[14145]: [2-2] DETAIL: Table contains > duplicated values. > Nov 22 10:22:27 SUC11 postgres[14145]: [2-3] STATEMENT: REINDEX TABLE > "t_sfh_history" > > My question is: > > How the Duplicate datas produce when a UNIQUE index exite ? > Is this a bug of PostgreSQL ? Either , or a pgsql bug can do this. What kind of " bad hardware " cause that ? May I reproduce that ? > My postgreSQL version is : > PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 > 20030502 (Red Hat Linux 3.2.3-56) You are aware that 8.1.x is up to 8.1.18, right, that's 14 or so updates you're missing. Could one of them have fixed a bug that causes this? Sure. Look through the changelogs to be sure, but why not just keep your pgsql version up to date? It's easier. Or are you running some redhat version that stays the same number while getting bugs back ported to it or something?
Tom Lane wrote: > The least painful route for a user of an RPM build would be to grab the > latest SRPM they can find and then modify that specfile to reference the > newer tarball. Rebuilding RPMs from source has its own challenges, if they're not already using a packaged build that may not necessarily be the easiest way; hard to say. One thing I didn't remember to suggest last night: you can always find out how your existing PostgreSQL was built using pg_config ; look at the CONFIGURE = line and it will show you what parameters were passed to the "./configure" step of the build the last time. If you're not sure exactly how the old one was built, this can help you out. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com