Re: is this a bug or I am blind? - Mailing list pgsql-general

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@gmail.com> writes:
>
>
>>On 12/15/05, Csaba Nagy <nagy@ecircle-ag.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
>
>