Hello All

Both server and psql are 7.4.5 and both running linux.

I have a table with a primary key as follow
--------
Column | Type |
Modifiers
--------+------------------------+-------------------------------------------------
email | character varying(256) |
pass | character varying(44) |
id | integer | not null default
nextval('"pass_id_seq"'::text)
stamp | abstime | default now()
alias | character varying(256) |
---
Indexes:
"pass_pkey" primary key, btree (id)
"pass_alias" unique, btree (alias)
"pass_email" unique, btree (email)
Triggers:
clean_up_tpass AFTER INSERT ON pass FOR EACH ROW
EXECUTE PROCEDURE clean_up_tpass()
pass_dbm AFTER INSERT OR DELETE OR UPDATE ON pass
FOR EACH ROW EXECUTE PROCEDURE recordchange()
sync_pass_epass BEFORE INSERT ON pass FOR EACH ROW
EXECUTE PROCEDURE sync_pass_epass()

-----------

I know for a fact that primary key 1598 exists because

------------
what=> select * from pass where
email='anonymous (AT) innocent (DOT) com';
email | pass
anonymous (AT) innocent (DOT) com |
$1$5127482d$LpSVZX.R/jgEo2PD5szL9/ | 1595 | |
abcd
---------------------

But when I actually do
-------------
what=>select * from pass where id=1595;
email | pass | id | stamp | alias
-------+------+----+-------+-------
(0 rows)
----

Ha? It works however if I do "where id=1595::int8"

Also watch the following

-----------
what=> select count(*) from pass where id<2000;
count
-------
1488
(1 row)

what=> select count(*) from pass where id<2000::int8;
count
-------
1918
(1 row)
--------
and the following

----------

what=>
what=> select count(*) from pass where
id<200000::int8;
count
-------
41712
(1 row)

what=> select count(*) from pass where id<200000;
count
-------
41712
---------

What exactly does that mean??? What is going on?

I started noticing this problem because there are
foreign keys on other tables referencing to this table
"pass" and when some values were inserted on one of
those tables I got an exception saying that foreign
key 1598 does not exist on this table "pass"

Is this me or pg?

I am thinking of fixing these problems by doing
something like

=> update pass set id=id::int4 where id<2000::int8;

but I really want to know why postgresql is behaving
in such a strange fashion. Can I trust postgresql to
be rational?

Thanks very much in advance.

Mr Wu Zohn Ming



__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

zohn_ming wu <wu_zohn_ming (AT) yahoo (DOT) com> writes:
Looks like a corrupted index to me. Have you tried REINDEXing the
pkey index?

regards, tom lane

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