Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A5F1A650054 for ; Tue, 12 Aug 2008 11:31:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84579-07 for ; Tue, 12 Aug 2008 11:31:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web35907.mail.mud.yahoo.com (web35907.mail.mud.yahoo.com [66.163.179.191]) by postgresql.org (Postfix) with SMTP id 6E09E64FF86 for ; Tue, 12 Aug 2008 11:31:46 -0300 (ADT) Received: (qmail 24533 invoked by uid 60001); 12 Aug 2008 14:31:44 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Message-ID; b=6fGXXq+SyZRPRAXZ5ujJf08b3AkZD8dEc+X5eyXwAN4YeGbI4q0M7JUFbzPVg+0DMReA9drihRs32M614IP+3GDIitroId4usrBTCJ+h93vgPB1xHvsDVG0oyBcHRLF9cP6DqC0a68giXC89TSuWTe9k5MOMwsabGOIO9xxVAQY=; Received: from [199.200.10.52] by web35907.mail.mud.yahoo.com via HTTP; Tue, 12 Aug 2008 07:31:44 PDT X-Mailer: YahooMailWebService/0.7.218 Date: Tue, 12 Aug 2008 07:31:44 -0700 (PDT) From: Rex Mabry Reply-To: rexmabry@yahoo.com Subject: Re: Duplicate records in a table inspite of a primary key To: pgsql-admin@postgresql.org Cc: subamathy In-Reply-To: <200808121027.53405.xzilla@users.sourceforge.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2130243369-1218551504=:24414" Message-ID: <462594.24414.qm@web35907.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/48 X-Sequence-Number: 29893 --0-2130243369-1218551504=:24414 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable You have to be careful when=A0using characters as your PK.=A0=A0A trailing = space could give the appearance of duplicate keys. Example: "user1" "user1 " (This one has a space at the end) --- On Tue, 8/12/08, Robert Treat wrote: From: Robert Treat Subject: Re: [ADMIN] Duplicate records in a table inspite of a primary key To: pgsql-admin@postgresql.org Cc: "subamathy" Date: Tuesday, August 12, 2008, 9:27 AM On Tuesday 12 August 2008 02:47:58 subamathy wrote: > Hi, > We are having problems resulting from users being able to create duplicate > records > in table. > > Below is the description of the table > Column | Type | Modifiers > -----------------------+------------------------+----------- > user_id | character varying(128) | not null > person_email_required | numeric(5,0) | > wddx_data | text | > proxy_id | character varying(128) | > email_to | character varying(6) | > password | character varying(60) | > user_name | character varying(64) | > language_preference | character varying(20) | > enable_autosearch | character varying(6) | > country_id | character varying(16) | > Indexes: > "i_personalise" PRIMARY KEY, btree (user_id) > "i_personalise_proxy_id" btree (proxy_id) > "i_personalise_user_id" btree (user_id) > > > database version: PostgreSQL 8.0.1 > > i_personalise- index > > conn type: Primary (constriant Type) > condeferrable: False > conkey: 1 (column which has constraint) > > Could you please help me out in finding the reason for such dupicate > records existing > > > Could you pls help me out in finding the reson for duplicate records bein= g > inserted. I'm pretty sure that between 8.0.1 and 8.0.17 there are a couple of bugfixes=20 related to duplicate rows with pk scenarios, you should probably take some= =20 time to upgrade before you spend too much time digging into this (make sure= =20 to read all of the release notes for those versions in between, i'm also=20 thinking there are some special instructions that might need to be done=20 somewhere in there) --=20 Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin =0A=0A=0A --0-2130243369-1218551504=:24414 Content-Type: text/html; charset=us-ascii
You have to be careful when using characters as your PK.  A trailing space could give the appearance of duplicate keys.
Example:
"user1"
"user1 " (This one has a space at the end)

--- On Tue, 8/12/08, Robert Treat <xzilla@users.sourceforge.net> wrote:
From: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: [ADMIN] Duplicate records in a table inspite of a primary key
To: pgsql-admin@postgresql.org
Cc: "subamathy" <subamathy@gmail.com>
Date: Tuesday, August 12, 2008, 9:27 AM

On Tuesday 12 August 2008 02:47:58 subamathy wrote:
>  Hi,
>  We are having problems resulting from users being able to create
duplicate
> records
> in table.
>
>   Below is the description of the table
>  Column | Type | Modifiers
> -----------------------+------------------------+-----------
> user_id | character varying(128) | not null
> person_email_required | numeric(5,0) |
> wddx_data | text |
> proxy_id | character varying(128) |
> email_to | character varying(6) |
> password | character varying(60) |
> user_name | character varying(64) |
> language_preference | character varying(20) |
> enable_autosearch | character varying(6) |
> country_id | character varying(16) |
> Indexes:
> "i_personalise" PRIMARY KEY, btree (user_id)
> "i_personalise_proxy_id" btree (proxy_id)
> "i_personalise_user_id" btree (user_id)
>
>
> database version: PostgreSQL 8.0.1
>
> i_personalise- index
>
> conn type: Primary (constriant Type)
> condeferrable: False
> conkey: 1 (column which has constraint)
>
> Could you please help me out in finding the reason for such dupicate
> records existing
>
>
> Could you pls help me out in finding the reson for duplicate records being
> inserted.

I'm pretty sure that between 8.0.1 and 8.0.17 there are a couple of
bugfixes 
related to duplicate rows with pk scenarios, you should probably take some 
time to upgrade before you spend too much time digging into this (make sure 
to read all of the release notes for those versions in between, i'm also 
thinking there are some special instructions that might need to be done 
somewhere in there)

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--0-2130243369-1218551504=:24414--