Обсуждение: 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
reproducableanymore !)
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
RPMsfrom 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. > 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 >>>>>> > ***********************************************************************
> 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 ! :-)
> 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 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 >>>>>>
> > ***********************************************************************
>
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: > > 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, Pennsylvania19026
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 Nieuwkerk > > > Rob van Nieuwkerk wrote: I think I was close. ;-) If I have followed the thread correctly, it is becauase of a language setting.