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(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?

发件人: 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?

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.

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

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

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

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(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