Lists: | pgsql-bugspgsql-general |
---|
From: | RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org> |
Subject: | How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 05:56:39 |
Message-ID: | EDA3764BC9DBA043AABB8FF57D73237801595783@QFWA0016.gtmcl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
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
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn> |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 06:43:48 |
Message-ID: | dcc563d10911242243s48c8b657m12ea16608e57d4b8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
2009/11/24 RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)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?
From: | RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn> |
---|---|
To: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | <pgsql-bugs(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org> |
Subject: | 答复: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 06:55:20 |
Message-ID: | EDA3764BC9DBA043AABB8FF57D7323780159579B@QFWA0016.gtmcl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
发件人: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
发送时间: 2009年11月25日 14:44
收件人: RD黄永卫
抄送: pgsql-bugs(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
主题: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ?
2009/11/24 RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)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?
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn> |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: 答复: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 07:13:00 |
Message-ID: | dcc563d10911242313t3349787n7a6ce157d9842e27@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
2009/11/24 RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn>:
>
> 发件人: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> 发送时间: 2009年11月25日 14:44
> 收件人: RD黄永卫
> 抄送: pgsql-bugs(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
> 主题: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ?
>
> 2009/11/24 RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)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.
From: | Greg Smith <greg(at)2ndquadrant(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn>, pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 07:44:03 |
Message-ID: | 4B0CE043.5030002@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
Scott Marlowe wrote:
>> 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?
>
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.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn>, pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 14:56:45 |
Message-ID: | 17310.1259161005@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
Greg Smith <greg(at)2ndquadrant(dot)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
From: | Greg Smith <greg(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, RD黄永卫 <yongwei_huang(at)gtmc(dot)com(dot)cn>, pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: [GENERAL] How duplicate data produce when a UNIQUE index exite ? |
Date: | 2009-11-25 16:42:42 |
Message-ID: | 4B0D5E82.1070205@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
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(at)2ndQuadrant(dot)com www.2ndQuadrant.com
From: | Yadira Lizama Mue <ylizama(at)uci(dot)cu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | change of oid values? |
Date: | 2009-11-25 18:49:35 |
Message-ID: | 1035275551.216371259174975267.JavaMail.root@ucimail3.uci.cu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
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
From: | Thom Brown <thombrown(at)gmail(dot)com> |
---|---|
To: | Yadira Lizama Mue <ylizama(at)uci(dot)cu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: change of oid values? |
Date: | 2009-11-25 19:00:27 |
Message-ID: | bddc86150911251100k6f7e5bdcke72f355e67d34baa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-general |
2009/11/25 Yadira Lizama Mue <ylizama(at)uci(dot)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