Thread: select returns no line
Hi, Our database has a SELECT problem using varchar columns in WHERE clause (but not in all rows!!!). We can fix the whole table (or just the row) as shown below but later it seems wrong again (and after the fix the row isn't UPDATEd). Any idea? Thanks Attila ******************************************************************************** Environment: [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] goodwill=>\d users Table = users +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | user_id | int4 not null default nextval ( | 4 | | user_login | varchar() not null | 15 | | user_passwd | varchar() not null | 15 | | user_exp | timestamp | 4 | +----------------------------------+----------------------------------+-------+ Indices: users_pkey users_user_login_key ******************************************************************************** The problem: goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd|user_exp -------+----------+-----------+-------- (0 rows) goodwill=> select * from users where user_id=4; user_id|user_login|user_passwd |user_exp -------+----------+-------------+-------- 4|test |0PDv7a2EESjZo| (1 row) goodwill=> update users set user_login=user_login where user_id=4; UPDATE 1 goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd |user_exp -------+----------+-------------+-------- 4|test |0PDv7a2EESjZo| (1 row) -- x- kisix@swi.hu -x- attila.kevei@goodwill.hu -x- kisiksz@westel900.net -x
Attila Kevei wrote: > Hi, > > Our database has a SELECT problem using varchar columns in WHERE clause > (but not in all rows!!!). > We can fix the whole table (or just the row) as shown below but later it > seems wrong again (and after the fix the row isn't UPDATEd). > > Any idea? > > Thanks > Attila > > ******************************************************************************** > Environment: > > [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] > > goodwill=>\d users > Table = users > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | user_id | int4 not null default nextval ( | 4 | > | user_login | varchar() not null | 15 | > | user_passwd | varchar() not null | 15 | > | user_exp | timestamp | 4 | > +----------------------------------+----------------------------------+-------+ > Indices: users_pkey > > users_user_login_key > > ******************************************************************************** > The problem: > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > -------+----------+-----------+-------- > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > -------+----------+-------------+-------- > 4|test |0PDv7a2EESjZo| > (1 row) > > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > -------+----------+-------------+-------- > 4|test |0PDv7a2EESjZo| > (1 row) > > -- > x- kisix@swi.hu -x- attila.kevei@goodwill.hu -x- kisiksz@westel900.net -x hello are you sure the value of the user_id in that line is "test" and not "test " i.e it has not spurious spaces at the end of it ? HTH Patrick
No, there's no space. The query (point 4. below) after the fix (point 3.) is the _same_ as the first (p.1.). In the first select there's no result but in the last select we get the right result. Attila Quoting patrick.jacquot@anpe.fr: 1. > > goodwill=>select * from users where user_login='test'; > > user_id|user_login|user_passwd|user_exp > > -------+----------+-----------+-------- > > (0 rows) > > 2. > > goodwill=> select * from users where user_id=4; > > user_id|user_login|user_passwd |user_exp > > -------+----------+-------------+-------- > > 4|test |0PDv7a2EESjZo| > > (1 row) > > 3. > > goodwill=> update users set user_login=user_login where user_id=4; > > UPDATE > > 1 > > 4. > > goodwill=>select * from users where user_login='test'; > > user_id|user_login|user_passwd |user_exp > > -------+----------+-------------+-------- > > 4|test |0PDv7a2EESjZo| > > (1 row) > > > > hello > are you sure the value of the user_id in that line is "test" and not > "test " > i.e it has not spurious spaces at the end of it ? > HTH > Patrick > -- x- kisix@swi.hu -x- attila.kevei@goodwill.hu -x- kisiksz@westel900.net -x
A space or something like that is also what I was thinking of. I'd suggest to: select * from users, length(user_login) where user_id=4; before and after the update. V.Paul
----- Original Message ----- From: "Attila Kevei" <attila.kevei@mail.goodwill.hu> > goodwill=>\d users > Table = users > +----------------------------------+----------------------------------+----- --+ > | Field | Type | Length| > +----------------------------------+----------------------------------+----- --+ > | user_id | int4 not null default nextval ( | 4 | > | user_login | varchar() not null | 15 | > | user_passwd | varchar() not null | 15 | > | user_exp | timestamp | 4 | > +----------------------------------+----------------------------------+----- --+ > Indices: users_pkey > >users_user_login_key Have you tried dropping the index? Could be mangled or a locale problem... - Richard Huxton
Attila Kevei writes: > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > -------+----------+-----------+-------- > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > -------+----------+-------------+-------- > 4|test |0PDv7a2EESjZo| > (1 row) You probably started the postmaster with two different LC_COLLATE (locale sort order) settings. The setting that was active when you ran initdb must be kept when you run the postmaster. > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > -------+----------+-------------+-------- > 4|test |0PDv7a2EESjZo| > (1 row) Yep, the update will fix the corrupted index (at least to the extent that this particular case now works). -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Attila Kevei <attila.kevei@mail.goodwill.hu> writes:
> Our database has a SELECT problem using varchar columns in WHERE clause
> (but not in all rows!!!).
> We can fix the whole table (or just the row) as shown below but later it
> seems wrong again (and after the fix the row isn't UPDATEd).
Very bizarre. Is the bogus SELECT using an index? (Check with EXPLAIN
if you're not sure.) I am suspicious that it is using a corrupted
index. Try dropping and rebuilding the index and see if the problem
persists.
If you have built Postgres with locale support, an easy way to get
corrupted indexes on text/char/varchar columns is to start the
postmaster with different locale environment variables at different
times. Different locales mean different logical sort orders, and
a btree index that is out of order is corrupt by definition. The
most common way of shooting yourself in the foot is to sometimes start
the postmaster from a boot script, and sometimes start it by hand from
a user login that has different locale environment than the boot script.
7.1 will have some defenses against this, but in older releases you
have to be careful.
regards, tom lane
This locale-thing sounds good...:-) As I remember, we've changed the locale settings after the creation of the table. The former opinions (space after the text, value length, etc.) didn't help us too much. Anyway, thanks to all. So, I try to re-create the index. Attila Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Attila Kevei <attila.kevei@mail.goodwill.hu> writes: > > Our database has a SELECT problem using varchar columns in WHERE > clause > > (but not in all rows!!!). > > We can fix the whole table (or just the row) as shown below but later > it > > seems wrong again (and after the fix the row isn't UPDATEd). > > Very bizarre. Is the bogus SELECT using an index? (Check with EXPLAIN > if you're not sure.) I am suspicious that it is using a corrupted > index. Try dropping and rebuilding the index and see if the problem > persists. > > If you have built Postgres with locale support, an easy way to get > corrupted indexes on text/char/varchar columns is to start the > postmaster with different locale environment variables at different > times. Different locales mean different logical sort orders, and > a btree index that is out of order is corrupt by definition. The > most common way of shooting yourself in the foot is to sometimes start > the postmaster from a boot script, and sometimes start it by hand from > a user login that has different locale environment than the boot script. > 7.1 will have some defenses against this, but in older releases you > have to be careful. > > regards, tom lane > -- x- kisix@swi.hu -x- attila.kevei@goodwill.hu -x- kisiksz@westel900.net -x