Thread: How duplicate data produce when a UNIQUE index exite ?
Dear all,
I have a table as below:
Table "public.t_sfh_history"
Column | Type | Modifiers
-------------+-----------------------------+-----------
idno | character(10) | not null
lo_date | character(8) | not null
line | character(1) | not null
tp | character(2) | not null
bcno | character(3) | not null
times | character(1) | not null
pdate | timestamp without time zone | not null
cdate | timestamp without time zone | not null
psh | character(1) | not null
hdlytime | numeric(6,0) | not null
hspr_tp | numeric(6,0) | not null
hspr_byo | numeric(6,0) | not null
hspr_deli | numeric(6,0) | not null
ltresult | numeric(6,0) | not null
ltresult_bs | numeric(6,0) | not null
dummy | character(6) | not null
Indexes:
"i_sfh_history0" UNIQUE, btree (idno, lo_date, line, tp, times)
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 :
version
----------------------------------------------------------------------------------------------------------
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)
(1 row)
Thank you very much
Best regards,
Ray Huang
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
发件人: 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?
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
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
Hi, I'd like to know if the values of postgres's Oids can changes its values for the same object. I want to use them in my aplication,but I'm afraid they could change and I get errors. I use the field Oid of table pg_type to identify the type of a field in a query. It could bring me errors in the future?For example, the oid value associated to type int4 could change at any future time? Regards, Yadira
2009/11/25 Yadira Lizama Mue <ylizama@uci.cu>
Hi,
I'd like to know if the values of postgres's Oids can changes its values for the same object. I want to use them in my aplication, but I'm afraid they could change and I get errors.
I use the field Oid of table pg_type to identify the type of a field in a query. It could bring me errors in the future? For example, the oid value associated to type int4 could change at any future time?
Regards,
Yadira
Why don't you just use pg_typeof() to get the type? And if you want the oid of the type, just do: pg_typeof(fieldname)::oid.
Regards
Thom