Thread: duplicate primary key entries?
Hi,
I just noticed something bad in our database:
webshop=# select oid,* from content_loc where id=20488;
oid | id | locale | name
---------+-------+--------+--------------
9781056 | 20488 | any | Rise Part II
9781058 | 20488 | any | Rise Part II
(2 rows)
webshop=# \d content_loc
Table "public.content_loc"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
locale | text | not null
name | text |
Indexes: content_loc_pkey primary key btree (id, locale)
Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES content(id) ON UPDATE
CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (locale) REFERENCES languages(locale) ON
UPDATE CASCADE ON DELETE CASCADE
Apparently there are two rows with identical primary keys which should not be
possible. Is this a know problem? Can I expect everything to be ok if I just
delete the extra entry?
I am using the debian packages of postgresql 7.3.4-9.
Thanks,
Baldur
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
On Tue, Nov 25, 2003 at 12:22:29PM +0100, Baldur Norddahl wrote: > Hi, > > I just noticed something bad in our database: > > webshop=# select oid,* from content_loc where id=20488; > oid | id | locale | name > ---------+-------+--------+-------------- > 9781056 | 20488 | any | Rise Part II > 9781058 | 20488 | any | Rise Part II > (2 rows) Any possibility there are hidden spaces? What is the output of: select oid,id,'['||locale||']','['||name||']' from content_loc where id=20488; Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
Hi, No, there can be no space after 'any' because the foreign key prevents it (which you of course could not check since I didn't show the content of the foreign table). But anyway, here is the output: webshop=# select oid,id,'['||locale||']','['||name||']' from content_loc where id=20488 and locale='any'; oid | id | ?column? | ?column? ---------+-------+----------+---------------- 9781056 | 20488 | [any] | [Rise Part II] 9781058 | 20488 | [any] | [Rise Part II] (2 rows) Baldur Quoting Martijn van Oosterhout <kleptog@svana.org>: > On Tue, Nov 25, 2003 at 12:22:29PM +0100, Baldur Norddahl wrote: > > Hi, > > > > I just noticed something bad in our database: > > > > webshop=# select oid,* from content_loc where id=20488; > > oid | id | locale | name > > ---------+-------+--------+-------------- > > 9781056 | 20488 | any | Rise Part II > > 9781058 | 20488 | any | Rise Part II > > (2 rows) > > Any possibility there are hidden spaces? What is the output of: > > select oid,id,'['||locale||']','['||name||']' from content_loc where > id=20488; > > Hope this helps, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > "All that is needed for the forces of evil to triumph is for enough good > > men to do nothing." - Edmond Burke > > "The penalty good people pay for not being interested in politics is to be > > governed by people worse than themselves." - Plato > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Baldur Norddahl wrote: > Hi, > > I just noticed something bad in our database: > > webshop=# select oid,* from content_loc where id=20488; > oid | id | locale | name > ---------+-------+--------+-------------- > 9781056 | 20488 | any | Rise Part II > 9781058 | 20488 | any | Rise Part II > (2 rows) > > webshop=# \d content_loc > Table "public.content_loc" > Column | Type | Modifiers > --------+---------+----------- > id | integer | not null > locale | text | not null > name | text | > Indexes: content_loc_pkey primary key btree (id, locale) > Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES content(id) ON UPDATE > CASCADE ON DELETE CASCADE, > $2 FOREIGN KEY (locale) REFERENCES languages(locale) ON > UPDATE CASCADE ON DELETE CASCADE > > > > Apparently there are two rows with identical primary keys which should not be > possible. Is this a know problem? Can I expect everything to be ok if I just > delete the extra entry? I would assume that btree index to be corrupt, otherwise it should have led to a duplicate key error. So you have at least to reindex after removing the extra entry. The real question though is how did it get there? As far as I know it allways turned out to be some damaged hardware (memory, disk) that led to corrupted btree indexes. Jan > > I am using the debian packages of postgresql 7.3.4-9. > > Thanks, > > Baldur > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Tue, Nov 25, 2003 at 01:02:37PM +0100, Baldur Norddahl wrote: > No, there can be no space after 'any' because the foreign key prevents it (which > you of course could not check since I didn't show the content of the foreign > table). Huh ... has the table any inherited tables? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta)
Dumb question, but is id actually defined as the primary key constraint in the table definition? ~Berend Tober > Hi, > > No, there can be no space after 'any' because the foreign key prevents > it (which you of course could not check since I didn't show the content > of the foreign table). > > But anyway, here is the output: > > webshop=# select oid,id,'['||locale||']','['||name||']' from > content_loc where id=20488 and locale='any'; > oid | id | ?column? | ?column? > ---------+-------+----------+---------------- > 9781056 | 20488 | [any] | [Rise Part II] > 9781058 | 20488 | [any] | [Rise Part II] > (2 rows) > > Baldur > > Quoting Martijn van Oosterhout <kleptog@svana.org>: > >> On Tue, Nov 25, 2003 at 12:22:29PM +0100, Baldur Norddahl wrote: >> > Hi, >> > >> > I just noticed something bad in our database: >> > >> > webshop=# select oid,* from content_loc where id=20488; >> > oid | id | locale | name >> > ---------+-------+--------+-------------- >> > 9781056 | 20488 | any | Rise Part II >> > 9781058 | 20488 | any | Rise Part II >> > (2 rows) >> >> Any possibility there are hidden spaces? What is the output of: >> >> select oid,id,'['||locale||']','['||name||']' from content_loc where >> id=20488; >>
Quoting Alvaro Herrera <alvherre@dcc.uchile.cl>: > On Tue, Nov 25, 2003 at 01:02:37PM +0100, Baldur Norddahl wrote: > > > No, there can be no space after 'any' because the foreign key prevents it > (which > > you of course could not check since I didn't show the content of the > foreign > > table). > > Huh ... has the table any inherited tables? No, but I did play a little with inheritance a few months ago until I discovered that it didn't play well with my use of foreign keys. When I said that the "locale" column could not contain the value "any ", I was refering to the foreign key to the table "languages". Since there is no "any " (with a space) in that table, this could not happen. Baldur ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Baldur Norddahl <bbn-pgsql.general@clansoft.dk> writes:
> Apparently there are two rows with identical primary keys which should not be
> possible. Is this a know problem?
Nope. If you try to REINDEX the primary key index, does it spit up a
duplicate-key failure?
regards, tom lane
I found the problem. It was not hardware problems or any malfunction in postgresql. I thought I had dropped all tables that inherited from the problem table, but apparently I forgot one. It really sucks that inheritance breaks their parent tables constraints :-(. Which is also why we had to drop using it even when it fitted perfectly into the structure. Baldur Quoting Alvaro Herrera <alvherre@dcc.uchile.cl>: > On Tue, Nov 25, 2003 at 01:02:37PM +0100, Baldur Norddahl wrote: > > > No, there can be no space after 'any' because the foreign key prevents it > (which > > you of course could not check since I didn't show the content of the > foreign > > table). > > Huh ... has the table any inherited tables? > > -- > Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) > Licensee shall have no right to use the Licensed Software > for productive or commercial use. (Licencia de StarOffice 6.0 beta) > > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Baldur Norddahl <bbn-pgsql.general@clansoft.dk> writes:
> It really sucks that inheritance breaks their parent tables constraints :-(.
Yeah, we know :-(. Sooner or later someone will work out a solution
to that.
Thanks for following up to close out this open issue.
regards, tom lane
> No, there can be no space after 'any' because the foreign key prevents
> it (which you of course could not check since I didn't show the content
> of the foreign table).
>
> But anyway, here is the output:
>
> webshop=# select oid,id,'['||locale||']','['||name||']' from content_loc
> where id=20488 and locale='any';
> oid | id | ?column? | ?column?
> ---------+-------+----------+----------------
> 9781056 | 20488 | [any] | [Rise Part II]
> 9781058 | 20488 | [any] | [Rise Part II]
> (2 rows)
Is there a function that can display the contents of the fourth column
in hexadecimal? This would make it easy to determine if the spaces weren't
really spaces (e.g., ASCII character 32 could be replaced with character 255
or, in some cases, even character 9).
Another thought I had was if there is a transaction in progress. Do
you get the same results if you omit "oid" from the SELECT statement?
--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada
Please do not eMail me directly when responding
to my postings in the newsgroups.