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

Search Discussions

  • Scott Marlowe at Nov 25, 2009 at 6:44 am

    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?
  • RD黄永卫 at Nov 25, 2009 at 6:55 am
    发件人: Scott Marlowe
    发送时间: 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?
  • Scott Marlowe at Nov 25, 2009 at 7:13 am
    2009/11/24 RD黄永卫 <yongwei_huang@gtmc.com.cn>:
    发件人: Scott Marlowe
    发送时间: 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.
  • Greg Smith at Nov 25, 2009 at 7:44 am

    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@2ndQuadrant.com www.2ndQuadrant.com
  • Tom Lane at Nov 25, 2009 at 2:57 pm

    Greg Smith 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
  • Greg Smith at Nov 25, 2009 at 4:43 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedNov 25, '09 at 5:56a
activeNov 25, '09 at 4:43p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase