DIfferent ORDER BY behaviour in 7.4.2
Hi,
I am seeing a different sorting behaviour for varchar columns in
Postgres 7.4.2.
postgres 7.4.2 is ignoring case for varchar columns for "ORDER BY"
commands where as 7.2.2 is not.
I am in a situation where I need to do diff from the select outputs of
two databases - one in 7.2.2 and one in 7.4.2.
So, is there any way I can force the ORDER BY to ignore case or
to not ignore case ?
The test results are as follows:
CREATE TABLE testSort (
testing varchar(32));
INSERT INTO testSort VALUES ('abc');
INSERT INTO testSort VALUES ('Abd');
INSERT INTO testSort VALUES ('aBc');
INSERT INTO testSort VALUES ('Cbaa');
select * from testSort order by testing;
In Postgres 7.2.2:
------------------
TEST=# select * from testSort order by testing;
testing
---------
Abd
Cba
aBc
abc
(4 rows)
In Postgres 7.4.2:
------------------
TEST=# select * from testSort order by testing;
testing
---------
aBc
abc
Abd
Cbaa
(4 rows)
Thanks in advance,
guna.
Gunasekaran Balakrishnan <guna@motorola.com> writes:
I am seeing a different sorting behaviour for varchar columns in
Postgres 7.4.2.
postgres 7.4.2 is ignoring case for varchar columns for "ORDER BY"
commands where as 7.2.2 is not.
Check your locale settings.
IIRC, --enable-locale was not default in 7.2 days, but it is default
(in fact mandatory) now. So it's possible that the 7.2 installation
doesn't have locale support at all and is defaulting to C-locale
behavior. If your 7.2 was built with locale support, then it was
probably initdb'd under a different locale setting than your 7.4 was.
In 7.4 you can just do "show lc_collate" to check, but in 7.2 I think
you have to use pg_controldata (or in a pinch, "strings pg_control")
to find out what locale it's using.
regards, tom lane
Thanks a lot Tom !!
Locale settings was the problem.
Thanks,
guna.
Tom Lane wrote:
Show quoted text
Gunasekaran Balakrishnan <guna@motorola.com> writes:
I am seeing a different sorting behaviour for varchar columns in
Postgres 7.4.2.
postgres 7.4.2 is ignoring case for varchar columns for "ORDER BY"
commands where as 7.2.2 is not.Check your locale settings.
IIRC, --enable-locale was not default in 7.2 days, but it is default
(in fact mandatory) now. So it's possible that the 7.2 installation
doesn't have locale support at all and is defaulting to C-locale
behavior. If your 7.2 was built with locale support, then it was
probably initdb'd under a different locale setting than your 7.4 was.
In 7.4 you can just do "show lc_collate" to check, but in 7.2 I think
you have to use pg_controldata (or in a pinch, "strings pg_control")
to find out what locale it's using.regards, tom lane
Hi,
I see that one of the bug fixes in 7.4.3 is:
"Fix temporary memory leak when using non-hashed aggregates (Tom)"
I am still using 7.4.2, and want to find out if I need to upgrade to 7.4.3.
I did not quite understand the bug, or find a way to re-produce this in the
mailist archives.
Can someone point me to the test case which can re-produce this problem.
Thanks a lot in advance,
guna.
Gunasekaran Balakrishnan wrote:
Show quoted text
Thanks a lot Tom !!
Locale settings was the problem.Thanks,
guna.Tom Lane wrote:
Gunasekaran Balakrishnan <guna@motorola.com> writes:
I am seeing a different sorting behaviour for varchar columns in
Postgres 7.4.2.
postgres 7.4.2 is ignoring case for varchar columns for "ORDER BY"
commands where as 7.2.2 is not.Check your locale settings.
IIRC, --enable-locale was not default in 7.2 days, but it is default
(in fact mandatory) now. So it's possible that the 7.2 installation
doesn't have locale support at all and is defaulting to C-locale
behavior. If your 7.2 was built with locale support, then it was
probably initdb'd under a different locale setting than your 7.4 was.
In 7.4 you can just do "show lc_collate" to check, but in 7.2 I think
you have to use pg_controldata (or in a pinch, "strings pg_control")
to find out what locale it's using.
regards, tom lane
Gunasekaran Balakrishnan wrote:
Hi,
I see that one of the bug fixes in 7.4.3 is:
"Fix temporary memory leak when using non-hashed aggregates (Tom)"
I am still using 7.4.2, and want to find out if I need to upgrade to 7.4.3.
I did not quite understand the bug, or find a way to re-produce this in the
mailist archives.Can someone point me to the test case which can re-produce this problem.
No, just upgrade, it is very easy, just stop, install, restart.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073The problem is that postgres is distributed as part of our product.
The last release we did was a month ago, where we upgraded from
7.2.2 to 7.4.2. There is no scheduled upgrade for the next 6 months.
So, I want to see how critical this problem is and what the chances
are of this occuring on our customer sites.
Thanks,
guna.
Bruce Momjian wrote:
Show quoted text
Gunasekaran Balakrishnan wrote:
Hi,
I see that one of the bug fixes in 7.4.3 is:
"Fix temporary memory leak when using non-hashed aggregates (Tom)"
I am still using 7.4.2, and want to find out if I need to upgrade to 7.4.3.
I did not quite understand the bug, or find a way to re-produce this in the
mailist archives.Can someone point me to the test case which can re-produce this problem.
No, just upgrade, it is very easy, just stop, install, restart.
Import Notes
Reference msg id not found: 26883945.1091645549112.JavaMail.root@hercules | Resolved by subject fallback
Gunasekaran Balakrishnan wrote:
The problem is that postgres is distributed as part of our product.
The last release we did was a month ago, where we upgraded from
7.2.2 to 7.4.2. There is no scheduled upgrade for the next 6 months.
So, I want to see how critical this problem is and what the chances
are of this occuring on our customer sites.Thanks,
guna.Bruce Momjian wrote:
Gunasekaran Balakrishnan wrote:
Hi,
I see that one of the bug fixes in 7.4.3 is:
"Fix temporary memory leak when using non-hashed aggregates (Tom)"
I am still using 7.4.2, and want to find out if I need to upgrade to 7.4.3.
I did not quite understand the bug, or find a way to re-produce this in the
mailist archives.Can someone point me to the test case which can re-produce this problem.
No, just upgrade, it is very easy, just stop, install, restart.
Ah, that is a tricky one. I don't think the problem is serious enough
to cause a mass upgrade. As I remember, the issue is that larger
aggregates could run out of memory and the query would fail. The memory
is always reclaimed at the end of the statement so unless you are seeing
aggregate failures, you should be fine.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
I would like to point out that NOT applying the latest sub minor
upgrades is a very bad idea. revisions that cause a bump in the third
number of postgresql are always and only bug fixes. Unlike many other
projects, I've found there to be no real liability to upgrading to the
latest sub rev of postgresql. While many of the bugs are minor,
occasionally some aren't, and missing a bug fix revision is a good way
to get bitten by one.
Show quoted text
On Wed, 2004-08-04 at 21:53, Gunasekaran Balakrishnan wrote:
The problem is that postgres is distributed as part of our product.
The last release we did was a month ago, where we upgraded from
7.2.2 to 7.4.2. There is no scheduled upgrade for the next 6 months.
So, I want to see how critical this problem is and what the chances
are of this occuring on our customer sites.Thanks,
guna.Bruce Momjian wrote:
Gunasekaran Balakrishnan wrote:
Hi,
I see that one of the bug fixes in 7.4.3 is:
"Fix temporary memory leak when using non-hashed aggregates (Tom)"
I am still using 7.4.2, and want to find out if I need to upgrade to 7.4.3.
I did not quite understand the bug, or find a way to re-produce this in the
mailist archives.Can someone point me to the test case which can re-produce this problem.
No, just upgrade, it is very easy, just stop, install, restart.