Re: is this a bug or I am blind?

UPDATE
----------------

I was trying to create a demo table, because I cannot send our
confidental data. I have found weird result.

# drop table common_logins;
DROP TABLE

$ psql < ../cl.sql
SET
SET
SET
SET
SET
SET
CREATE TABLE
setval
--------
203650
(1 row)

ALTER TABLE
CREATE INDEX
CREATE INDEX

# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status | usertype | loginnum
-----+----------+----------+-----------+--------+----------+----------
(0 rows)

# VACUUM FULL analyze;
VACUUM

# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status | usertype | loginnum
-----+----------+----------+-----------+--------+----------+----------
(0 rows)

# select count(1) from common_logins;
count
--------
203361
(1 row)

# delete from common_logins where uid in (select uid from common_logins
where username not ilike 'potyty' limit 100000);
DELETE 100000

mage=# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status | usertype | loginnum
-----+----------+----------+-----------+--------+----------+----------
(0 rows)

# VACUUM FULL analyze;
VACUUM

# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status |
usertype | loginnum
--------+----------+----------+----------------------------+--------+----------+----------
155505 | potyty | board | 2004-08-16 17:45:55.723829 | A |
S | 1
60067 | potyty | board | 2004-07-07 20:22:17.68699 | A |
S | 3
(2 rows)

# delete from common_logins where uid in (select uid from common_logins
where username not ilike 'potyty' limit 80000);
DELETE 80000

# VACUUM FULL analyze;
VACUUM

# select * from common_logins where username = 'potyty';
uid | username | password | lastlogin | status |
usertype | loginnum
--------+----------+----------+----------------------------+--------+----------+----------
174041 | potyty | board | 2005-02-17 00:00:13.706144 | A |
S | 3
60067 | potyty | board | 2004-07-07 20:22:17.68699 | A |
S | 3
155505 | potyty | board | 2004-08-16 17:45:55.723829 | A |
S | 1
(3 rows)

The 2 rows part seems to be (for me) a non-locale-related, but serious
problem.
I have the data file, it is confidental, but I can send it to official
pg developers if needed.

Mage

Tom Lane wrote:

>Jaime Casanova <systemguards(at)gmail(dot)com> writes:
>
>
>>On 12/15/05, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
>>
>>
>>>Ok, that explains then the problem... but the index is arguably corrupt
>>>in this case, with the important difference that it's not even fixable
>>>by reindex...
>>>
>>>I guess what the OP really wants is a solution to his problem.
>>>
>>>
>
>
>
>>MAGE was reproducing the problem in a little table that can be send
>>but now tolds me that the problem in the test table disappear when a
>>VACUUM was executed... is this consistent with the idea of locale
>>problem?
>>
>>
>
>The VACUUM might have caused the planner not to use the index anymore;
>check EXPLAIN.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>