7.0.3 reproduceable serious select error
Hello,
I've selected postgresql 7.0.3 for our (critical) application and while
doing my first experiments I've found a bug which makes me worry very
much.
The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present. When the index is removed the SELECT *does*
return the right answer.
Fortunately I managed to strip down our database and create a simple
single table with which the bug can be easily reproduced.
I've been searching in the Postgres bug-database and this problem
might be related to this report:
http://www.postgresql.org/bugs/bugs.php?4~111
Below you find a psql-session that demonstrates the bug.
I've made a dump of the test-database available as:
http://dutepp0.et.tudelft.nl/~robn/demo.dump.bz2
(it is 46100 bytes long in compressed form but 45 MB when uncompressed,
I tried to trim it down but then the bug isn't reproducable anymore !)
The table is filled with all Spaces execpt for the "town" column.
Sysinfo:
--------
- well-maintained Linux Red Hat 6.2
- kernel 2.2.18
- Intel Pentium III
- postgresql-7.0.3-2 RPMs from the Postgresql site
(the problem also occurs with locally rebuilt Source RPM)
Any help is much appreciated !
Friendly greetings,
Rob van Nieuwkerk
psql session:
***********************************************************************
demo=> \d
List of relations
Name | Type | Owner
------------+-------+-------
demo_table | table | robn
(1 row)
demo=> \d demo_table
Table "demo_table"
Attribute | Type | Modifier
-----------+----------+----------
postcode | char(7) |
odd_even | char(1) |
low | char(5) |
high | char(5) |
street | char(24) |
town | char(24) |
area | char(1) |
demo=> \di
No relations found.
demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
<<<<<< here 86 towns are correctly found (output removed) >>>>>>
demo=> CREATE INDEX demo_table_town_idx ON demo_table(town);
CREATE
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
town
------
(0 rows)
<<<<<< This is wrong !!!!!! >>>>>>>
demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)
demo=> DROP INDEX demo_table_town_idx;
DROP
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
<<<<<< here 86 towns are correctly found again >>>>>>
***********************************************************************
robn@verdi.et.tudelft.nl (Rob van Nieuwkerk) writes:
The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present. When the index is removed the SELECT *does*
return the right answer.
Are you running the postmaster in a non-ASCII locale? This sounds like
the old LIKE index optimization problem that we've struggled with for
quite a while now. 7.1 works around it by disabling the optimization
in non-ASCII locales, which is unpleasant but at least it gives right
answers ...
regards, tom lane
Tom Lane wrote:
robn@verdi.et.tudelft.nl (Rob van Nieuwkerk) writes:
The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present. When the index is removed the SELECT *does*
return the right answer.Are you running the postmaster in a non-ASCII locale? This sounds like
the old LIKE index optimization problem that we've struggled with for
quite a while now. 7.1 works around it by disabling the optimization
in non-ASCII locales, which is unpleasant but at least it gives right
answers ...
Hi Tom,
I don't think I'm running postmaster in a non-ASCII locale.
At least I did not explicitly do anything to accomplish it.
I'm running with the default settings from the RPMs and didn't
change any default setting.
I peeked in some manual pages but couldn't find info quickly about
this setting. Please tell me how to check it if you want to know !
Thank you for your reaction.
greetings,
Rob van Nieuwkerk
Rob van Nieuwkerk wrote:
I tried to reproduce this bug on 7.0.2 and 7.0.3 with both 8K and 32K block
sizes, and could not reproduce the error.
I am running RedHat 6.2 kernel 2.2.16.
I don't know enough to even be close, but I wonder if there are any subtle
differences between the way characters are treated for indexes vs the way they
are treated for table scans? If there are even slight differences in the way
this happens, a misinterpretation of ascii conversions for instance, (I am
assuming you may be using ascii characters above 0x7F), it could behave
something like this, and explain why I wouldn't see it. .Like I said, however,
I don't know much so don't read too much into what I say.
Show quoted text
Hello,
I've selected postgresql 7.0.3 for our (critical) application and while
doing my first experiments I've found a bug which makes me worry very
much.The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present. When the index is removed the SELECT *does*
return the right answer.Fortunately I managed to strip down our database and create a simple
single table with which the bug can be easily reproduced.I've been searching in the Postgres bug-database and this problem
might be related to this report:http://www.postgresql.org/bugs/bugs.php?4~111
Below you find a psql-session that demonstrates the bug.
I've made a dump of the test-database available as:
http://dutepp0.et.tudelft.nl/~robn/demo.dump.bz2
(it is 46100 bytes long in compressed form but 45 MB when uncompressed,
I tried to trim it down but then the bug isn't reproducable anymore !)The table is filled with all Spaces execpt for the "town" column.
Sysinfo:
--------
- well-maintained Linux Red Hat 6.2
- kernel 2.2.18
- Intel Pentium III
- postgresql-7.0.3-2 RPMs from the Postgresql site
(the problem also occurs with locally rebuilt Source RPM)Any help is much appreciated !
Friendly greetings,
Rob van Nieuwkerkpsql session:
***********************************************************************
demo=> \d
List of relations
Name | Type | Owner
------------+-------+-------
demo_table | table | robn
(1 row)demo=> \d demo_table
Table "demo_table"
Attribute | Type | Modifier
-----------+----------+----------
postcode | char(7) |
odd_even | char(1) |
low | char(5) |
high | char(5) |
street | char(24) |
town | char(24) |
area | char(1) |demo=> \di
No relations found.
demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
<<<<<< here 86 towns are correctly found (output removed) >>>>>>
demo=> CREATE INDEX demo_table_town_idx ON demo_table(town);
CREATE
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
town
------
(0 rows)
<<<<<< This is wrong !!!!!! >>>>>>>demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)demo=> DROP INDEX demo_table_town_idx;
DROP
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;<<<<<< here 86 towns are correctly found again >>>>>>
***********************************************************************
I don't think I'm running postmaster in a non-ASCII locale.
At least I did not explicitly do anything to accomplish it.
Did you have LANG, LOCALE, or any of the LC_xxx family of
environment variables set when you started the postmaster?
Some Linux distros tend to set those in system profile scripts ...
regards, tom lane
Hi Mark,
I just checked: the "demo.dump" file does not contain any characters
above 0x7F; it's just plain ASCII. So that can't be the reason.
greetings,
Rob van Nieuwkerk
Rob van Nieuwkerk wrote:
Ehm .., *you* wrote this ! :-)
Show quoted text
I tried to reproduce this bug on 7.0.2 and 7.0.3 with both 8K and 32K block
sizes, and could not reproduce the error.I am running RedHat 6.2 kernel 2.2.16.
I don't know enough to even be close, but I wonder if there are any subtle
differences between the way characters are treated for indexes vs the way they
are treated for table scans? If there are even slight differences in the way
this happens, a misinterpretation of ascii conversions for instance, (I am
assuming you may be using ascii characters above 0x7F), it could behave
something like this, and explain why I wouldn't see it. .Like I said, however,
I don't know much so don't read too much into what I say.
Hello,
I've selected postgresql 7.0.3 for our (critical) application and while
doing my first experiments I've found a bug which makes me worry very
much.The problem is that a SELECT with a certain LIKE condition in combination
with a GROUP BY does not find the proper records when there is an index on
the particular column present. When the index is removed the SELECT *does*
return the right answer.Fortunately I managed to strip down our database and create a simple
single table with which the bug can be easily reproduced.I've been searching in the Postgres bug-database and this problem
might be related to this report:http://www.postgresql.org/bugs/bugs.php?4~111
Below you find a psql-session that demonstrates the bug.
I've made a dump of the test-database available as:
http://dutepp0.et.tudelft.nl/~robn/demo.dump.bz2
(it is 46100 bytes long in compressed form but 45 MB when uncompressed,
I tried to trim it down but then the bug isn't reproducable anymore !)The table is filled with all Spaces execpt for the "town" column.
Sysinfo:
--------
- well-maintained Linux Red Hat 6.2
- kernel 2.2.18
- Intel Pentium III
- postgresql-7.0.3-2 RPMs from the Postgresql site
(the problem also occurs with locally rebuilt Source RPM)Any help is much appreciated !
Friendly greetings,
Rob van Nieuwkerkpsql session:
***********************************************************************
demo=> \d
List of relations
Name | Type | Owner
------------+-------+-------
demo_table | table | robn
(1 row)demo=> \d demo_table
Table "demo_table"
Attribute | Type | Modifier
-----------+----------+----------
postcode | char(7) |
odd_even | char(1) |
low | char(5) |
high | char(5) |
street | char(24) |
town | char(24) |
area | char(1) |demo=> \di
No relations found.
demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
<<<<<< here 86 towns are correctly found (output removed) >>>>>>
demo=> CREATE INDEX demo_table_town_idx ON demo_table(town);
CREATE
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;
town
------
(0 rows)
<<<<<< This is wrong !!!!!! >>>>>>>demo=> SELECT town FROM demo_table WHERE town LIKE 'ZWO%' GROUP BY town;
town
--------------------------
ZWOLLE
(1 row)demo=> DROP INDEX demo_table_town_idx;
DROP
demo=> SELECT town FROM demo_table WHERE town LIKE 'Z%' GROUP BY town;<<<<<< here 86 towns are correctly found again >>>>>>
***********************************************************************
Tom Lane wrote:
I don't think I'm running postmaster in a non-ASCII locale.
At least I did not explicitly do anything to accomplish it.Did you have LANG, LOCALE, or any of the LC_xxx family of
environment variables set when you started the postmaster?
Some Linux distros tend to set those in system profile scripts ...
Checking whith ps and looking in /proc reveiled that postmaster indeed
had LANG set to "en_US" in its environment. I disabled the system script
that makes this setting, restarted postgres/postmaster and reran my tests.
The problem query returns the *right* answer now !
Turning LANG=en_US back on gives the old buggy behaviour.
I know very little about this LANG, LOCALE etc. stuff.
But for our application it is very important to support "weird" characters
like "���� ..." etc. for names. Basically we need all letter symbols
in ISO-8859-1 (Latin 1). A quick experiment shows that without the
LANG setting I can still insert & select strings containing these
symbols.
Do I lose any postgresql functionality by just getting rid of the LANG
environment variable ? Will I be able to use full ISO-8859-1 in table
fields without problems ?
Please tell if you want me to do any other tests !
greetings,
Rob van Nieuwkerk
Rob van Nieuwkerk <robn@verdi.et.tudelft.nl> writes:
I know very little about this LANG, LOCALE etc. stuff.
But for our application it is very important to support "weird" characters
like "���� ..." etc. for names. Basically we need all letter symbols
in ISO-8859-1 (Latin 1).
en_US is latin1 - this is what distinguishes it from POSIX/C.
--
Trond Eivind Glomsr�d
Red Hat, Inc.
Rob van Nieuwkerk <robn@verdi.et.tudelft.nl> writes:
Checking whith ps and looking in /proc reveiled that postmaster indeed
had LANG set to "en_US" in its environment. I disabled the system script
that makes this setting, restarted postgres/postmaster and reran my tests.
The problem query returns the *right* answer now !
Turning LANG=en_US back on gives the old buggy behaviour.
Caution: you can't just change the locale willy-nilly, because doing so
invalidates the sort ordering of btree indexes. An index built under
one sort order is effectively corrupt under another. I recommend that
you dumpall, then initdb under the desired LANG setting, then reload,
and be careful always to start the postmaster under that same setting
henceforth.
(BTW, 7.1 prevents this type of index screwup by locking down the
database's locale at initdb time --- the ONLY way to change sort order
in 7.1 is to initdb with the right locale environment variables. But in
7.0 you gotta be careful about keeping the locale consistent.)
I know very little about this LANG, LOCALE etc. stuff.
But for our application it is very important to support "weird" characters
like "���� ..." etc. for names. Basically we need all
letter symbols in ISO-8859-1 (Latin 1).
As long as you are not expecting things to sort in any particular order,
it really doesn't matter what locale you run Postgres in. If you do
care about sort order of characters that aren't bog-standard USASCII,
then you may have a problem. But you can store 'em in any case.
regards, tom lane
I meant to ask this the last time this came up on the list, but now is a
good time. Given what Tom describes below as the behavior in 7.1
(initdb stores the locale info), how do you determine what locale a
database is running in in 7.1 after initdb? Is there some file to look
at? Is there some sql statement that can be used to select the setting
from the DB?
thanks,
--Barry
Tom Lane wrote:
Show quoted text
Rob van Nieuwkerk <robn@verdi.et.tudelft.nl> writes:
Checking whith ps and looking in /proc reveiled that postmaster indeed
had LANG set to "en_US" in its environment. I disabled the system script
that makes this setting, restarted postgres/postmaster and reran my tests.The problem query returns the *right* answer now !
Turning LANG=en_US back on gives the old buggy behaviour.Caution: you can't just change the locale willy-nilly, because doing so
invalidates the sort ordering of btree indexes. An index built under
one sort order is effectively corrupt under another. I recommend that
you dumpall, then initdb under the desired LANG setting, then reload,
and be careful always to start the postmaster under that same setting
henceforth.(BTW, 7.1 prevents this type of index screwup by locking down the
database's locale at initdb time --- the ONLY way to change sort order
in 7.1 is to initdb with the right locale environment variables. But in
7.0 you gotta be careful about keeping the locale consistent.)I know very little about this LANG, LOCALE etc. stuff.
But for our application it is very important to support "weird" characters
like "éõåÊ ..." etc. for names. Basically we need all
letter symbols in ISO-8859-1 (Latin 1).As long as you are not expecting things to sort in any particular order,
it really doesn't matter what locale you run Postgres in. If you do
care about sort order of characters that aren't bog-standard USASCII,
then you may have a problem. But you can store 'em in any case.regards, tom lane
Barry Lind <barry@xythos.com> writes:
I meant to ask this the last time this came up on the list, but now is a
good time. Given what Tom describes below as the behavior in 7.1
(initdb stores the locale info), how do you determine what locale a
database is running in in 7.1 after initdb?
Hm. There probably ought to be an inquiry function or SHOW variable
for that, but at the moment there's not. Offhand I can't think of any
direct way except to paw through the pg_control file looking for the
locale name (at least it's stored there in ASCII ;-)).
regards, tom lane
Added to TODO:
* Add SHOW command to see locale
Barry Lind <barry@xythos.com> writes:
I meant to ask this the last time this came up on the list, but now is a
good time. Given what Tom describes below as the behavior in 7.1
(initdb stores the locale info), how do you determine what locale a
database is running in in 7.1 after initdb?Hm. There probably ought to be an inquiry function or SHOW variable
for that, but at the moment there's not. Offhand I can't think of any
direct way except to paw through the pg_control file looking for the
locale name (at least it's stored there in ASCII ;-)).regards, tom lane
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Rob van Nieuwkerk wrote:
The problem query returns the *right* answer now !
Turning LANG=en_US back on gives the old buggy behaviour.I know very little about this LANG, LOCALE etc. stuff.
But for our application it is very important to support "weird" characters
like "���� ..." etc. for names. Basically we need all letter symbols
in ISO-8859-1 (Latin 1). A quick experiment shows that without the
LANG setting I can still insert & select strings containing these
symbols.Do I lose any postgresql functionality by just getting rid of the LANG
environment variable ? Will I be able to use full ISO-8859-1 in table
fields without problems ?
You should, except that upper() and lower() will not give you right
answers
for char>128 and order by orders in ASCII (i.e. character code value)
order.
I would suggest that instead you keep the en_US locale (or some nl
locale
if you need the rigt ordering from DB), but do _not_ create a b-tree
(the default) index on your text fields. If you need the index for
exact lookup (field=const) an hash idex will do fine and i'm pretty sure
that LIKE optimisations will not use them to spoil searches ;).
-------------------
Hannu
Bruce Momjian wrote:
Added to TODO:
* Add SHOW command to see locale
I'd rather like it to be a function, as version() is, because SHOW
commands may not
play nice with other interfaces than psql.
(and it can first be included in ./contrib if it's too late for a
"feature" <grin>)
Just make sure we will not confict with SQL standard in naming the
function.
-------------------
Hannu
Rob van Nieuwkerk wrote:
Hi Mark,
I just checked: the "demo.dump" file does not contain any characters
above 0x7F; it's just plain ASCII. So that can't be the reason.greetings,
Rob van NieuwkerkRob van Nieuwkerk wrote:
I think I was close. ;-)
If I have followed the thread correctly, it is becauase of a language setting.