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
