duplicate primary key entries?

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/

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.

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)

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.

Dumb question, but is id actually defined as the primary key constraint
in the table definition?

~Berend Tober

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 #

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.

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