pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select returns no line
10+ messages / 8 participants
[nested] [flat]

* select returns no line
@ 2001-01-22 16:47 kisix <attila.kevei@goodwill.hu>
  0 siblings, 0 replies; 10+ messages in thread

From: kisix @ 2001-01-22 16:47 UTC (permalink / raw)
  To: pgsql-sql

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).

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* select returns no line
@ 2001-01-23 11:42 Attila Kevei <attila.kevei@goodwill.hu>
  0 siblings, 0 replies; 10+ messages in thread

From: Attila Kevei @ 2001-01-23 11:42 UTC (permalink / raw)
  To: pgsql-sql

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).

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* select returns no line
@ 2001-01-23 12:32 Attila Kevei <attila.kevei@mail.goodwill.hu>
  2001-01-23 15:21 ` Re: select returns no line patrick.jacquot@anpe.fr
  2001-01-23 16:01 ` Re: select returns no line Richard Huxton <dev@archonet.com>
  2001-01-23 16:27 ` Re: select returns no line Peter Eisentraut <peter_e@gmx.net>
  2001-01-23 16:54 ` Re: select returns no line Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 4 replies; 10+ messages in thread

From: Attila Kevei @ 2001-01-23 12:32 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
@ 2001-01-23 15:21 ` patrick.jacquot@anpe.fr
  2001-01-23 13:41   ` Re: select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
  3 siblings, 1 reply; 10+ messages in thread

From: patrick.jacquot@anpe.fr @ 2001-01-23 15:21 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
  2001-01-23 15:21 ` Re: select returns no line patrick.jacquot@anpe.fr
@ 2001-01-23 13:41   ` Attila Kevei <attila.kevei@mail.goodwill.hu>
  2001-01-23 14:24     ` Re: select returns no line Volker Paul <vpaul@dohle.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Attila Kevei @ 2001-01-23 13:41 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
  2001-01-23 15:21 ` Re: select returns no line patrick.jacquot@anpe.fr
  2001-01-23 13:41   ` Re: select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
@ 2001-01-23 14:24     ` Volker Paul <vpaul@dohle.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Volker Paul @ 2001-01-23 14:24 UTC (permalink / raw)
  To: Attila Kevei <attila.kevei@mail.goodwill.hu>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
@ 2001-01-23 16:01 ` Richard Huxton <dev@archonet.com>
  3 siblings, 0 replies; 10+ messages in thread

From: Richard Huxton @ 2001-01-23 16:01 UTC (permalink / raw)
  To: pgsql-sql; Attila Kevei <attila.kevei@mail.goodwill.hu>

----- 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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
@ 2001-01-23 16:27 ` Peter Eisentraut <peter_e@gmx.net>
  3 siblings, 0 replies; 10+ messages in thread

From: Peter Eisentraut @ 2001-01-23 16:27 UTC (permalink / raw)
  To: Attila Kevei <attila.kevei@mail.goodwill.hu>; +Cc: pgsql-sql

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/




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
@ 2001-01-23 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-01-24 09:10   ` Re: select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
  3 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2001-01-23 16:54 UTC (permalink / raw)
  To: Attila Kevei <attila.kevei@mail.goodwill.hu>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: select returns no line
  2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
  2001-01-23 16:54 ` Re: select returns no line Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-01-24 09:10   ` Attila Kevei <attila.kevei@mail.goodwill.hu>
  0 siblings, 0 replies; 10+ messages in thread

From: Attila Kevei @ 2001-01-24 09:10 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2001-01-24 09:10 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-22 16:47 select returns no line kisix <attila.kevei@goodwill.hu>
2001-01-23 11:42 select returns no line Attila Kevei <attila.kevei@goodwill.hu>
2001-01-23 12:32 select returns no line Attila Kevei <attila.kevei@mail.goodwill.hu>
2001-01-23 15:21 ` patrick.jacquot@anpe.fr
2001-01-23 13:41   ` Attila Kevei <attila.kevei@mail.goodwill.hu>
2001-01-23 14:24     ` Volker Paul <vpaul@dohle.com>
2001-01-23 16:01 ` Richard Huxton <dev@archonet.com>
2001-01-23 16:27 ` Peter Eisentraut <peter_e@gmx.net>
2001-01-23 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-24 09:10   ` Attila Kevei <attila.kevei@mail.goodwill.hu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox