By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

duplicate primary key entries?

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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 <kl*****@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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/w0DgY5Twig3Ge+YRAgUqAKCvIwJANcFHfZykmbYNB3vsNKRRvw Cgycm3
yAvrZ5Cdwpj/R7AcsKIQmq4=
=7eNA
-----END PGP SIGNATURE-----

Nov 12 '05 #2

P: n/a
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 <kl*****@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 <kl*****@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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4

P: n/a
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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #5

P: n/a
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 <kl*****@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;



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #6

P: n/a
Quoting Alvaro Herrera <al******@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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #7

P: n/a
> 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.
Nov 12 '05 #8

P: n/a
Baldur Norddahl <bb***************@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #9

P: n/a
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 <al******@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #10

P: n/a
Baldur Norddahl <bb***************@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.