data corruption? - Mailing list pgsql-bugs

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@innocent.com';
        email        |                pass
   |  id  | stamp |      alias
---------------------+------------------------------------+------+-------+-----------------
 anonymous@innocent.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