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@xxxxxxxxx> writes:
On 12/15/05, Csaba Nagy <nagy@xxxxxxxxxxxxxx> 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
