BUG #6034: pg_upgrade fails when it should not.
The following bug has been logged online:
Bug reference: 6034
Logged by: Tim Uckun
Email address: timuckun@gmail.com
PostgreSQL version: 8.4
Operating system: ubuntu linux
Description: pg_upgrade fails when it should not.
Details:
pg_upgrade from 8.4 to 9.0 fails with the following error message.
old and new cluster lc_collate values do not match
on 8.4 show lc_collate outputs
------------
en_NZ.utf8
(1 row)
on 9.0 it outputs
------------
en_NZ.UTF8
(1 row)
So the difference seems to be in capitalization. Presumably they are the
same collation with "different" names so it strikes me as odd that
pg_upgrade would balk and refuse to upgrade the database.
pg_upgrade should be able to tell that these are the same collations and go
ahead with the upgrade.
It also should give some indication of how to overcome the problem.
Tim Uckun wrote:
pg_upgrade from 8.4 to 9.0 fails with the following error message.
old and new cluster lc_collate values do not match
on 8.4 show lc_collate outputs
------------
en_NZ.utf8
(1 row)on 9.0 it outputs
------------
en_NZ.UTF8
(1 row)So the difference seems to be in capitalization. Presumably they are the
same collation with "different" names so it strikes me as odd that
pg_upgrade would balk and refuse to upgrade the database.pg_upgrade should be able to tell that these are the same collations and go
ahead with the upgrade.It also should give some indication of how to overcome the problem.
[ Moved to hackers list.]
Interesting. It can be easily fixed in the C code. Does anyone know of
an example where the case of the locale name is significant in
controlling the behavior?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Sun, May 22, 2011 at 9:39 PM, Bruce Momjian <bruce@momjian.us> wrote:
Tim Uckun wrote:
pg_upgrade from 8.4 to 9.0 fails with the following error message.
old and new cluster lc_collate values do not match
on 8.4 show lc_collate outputs
------------
en_NZ.utf8
(1 row)on 9.0 it outputs
------------
en_NZ.UTF8
(1 row)So the difference seems to be in capitalization. Presumably they are the
same collation with "different" names so it strikes me as odd that
pg_upgrade would balk and refuse to upgrade the database.pg_upgrade should be able to tell that these are the same collations and go
ahead with the upgrade.It also should give some indication of how to overcome the problem.
[ Moved to hackers list.]
Interesting. It can be easily fixed in the C code. Does anyone know of
an example where the case of the locale name is significant in
controlling the behavior?
Uh, are we talking about locale, or collation?
Because if it's collation, that seems certain to affect index contents.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
On Sun, May 22, 2011 at 9:39 PM, Bruce Momjian <bruce@momjian.us> wrote:
Tim Uckun wrote:
pg_upgrade from 8.4 to 9.0 fails with the following error message.
old and new cluster lc_collate values do not match
on 8.4 show lc_collate outputs
------------
?en_NZ.utf8
(1 row)on 9.0 it outputs
------------
?en_NZ.UTF8
(1 row)So the difference seems to be in capitalization. Presumably they are the
same collation with "different" names so it strikes me as odd that
pg_upgrade would balk and refuse to upgrade the database.pg_upgrade should be able to tell that these are the same collations and go
ahead with the upgrade.It also should give some indication of how to overcome the problem.
[ Moved to hackers list.]
Interesting. ?It can be easily fixed in the C code. ?Does anyone know of
an example where the case of the locale name is significant in
controlling the behavior?Uh, are we talking about locale, or collation?
Because if it's collation, that seems certain to affect index contents.
Sorry, I was unclear. The question is whether the case of _name_ of the
locale is significant, meaning can you have two locale names that differ
only by case and behave differently?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, May 23, 2011 at 8:26 AM, Bruce Momjian <bruce@momjian.us> wrote:
Sorry, I was unclear. The question is whether the case of _name_ of the
locale is significant, meaning can you have two locale names that differ
only by case and behave differently?
That would seem surprising to me, but I really have no idea.
There's the other direction, too: two locales that vary by something
more than case, but still have identical behavior. Maybe we just
decide not to worry about that, but then why worry about this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
On Mon, May 23, 2011 at 8:26 AM, Bruce Momjian <bruce@momjian.us> wrote:
Sorry, I was unclear. ?The question is whether the case of _name_ of the
locale is significant, meaning can you have two locale names that differ
only by case and behave differently?That would seem surprising to me, but I really have no idea.
There's the other direction, too: two locales that vary by something
more than case, but still have identical behavior. Maybe we just
decide not to worry about that, but then why worry about this?
Well, if we remove the check then people could easily get broken
upgrades by upgrading to a server with a different locale. A Google
search seems to indicate the locale names are case-sensitive so I am
thinking the problem is that the user didn't have exact locales, and
needs that to use pg_upgrade.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, May 23, 2011 at 2:57 PM, Bruce Momjian <bruce@momjian.us> wrote:
Robert Haas wrote:
On Mon, May 23, 2011 at 8:26 AM, Bruce Momjian <bruce@momjian.us> wrote:
Sorry, I was unclear. ?The question is whether the case of _name_ of the
locale is significant, meaning can you have two locale names that differ
only by case and behave differently?That would seem surprising to me, but I really have no idea.
There's the other direction, too: two locales that vary by something
more than case, but still have identical behavior. Maybe we just
decide not to worry about that, but then why worry about this?Well, if we remove the check then people could easily get broken
upgrades by upgrading to a server with a different locale. A Google
search seems to indicate the locale names are case-sensitive so I am
thinking the problem is that the user didn't have exact locales, and
needs that to use pg_upgrade.
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
On Mon, May 23, 2011 at 2:57 PM, Bruce Momjian <bruce@momjian.us> wrote:
Robert Haas wrote:
On Mon, May 23, 2011 at 8:26 AM, Bruce Momjian <bruce@momjian.us> wrote:
Sorry, I was unclear. ?The question is whether the case of _name_ of the
locale is significant, meaning can you have two locale names that differ
only by case and behave differently?That would seem surprising to me, but I really have no idea.
There's the other direction, too: two locales that vary by something
more than case, but still have identical behavior. ?Maybe we just
decide not to worry about that, but then why worry about this?Well, if we remove the check then people could easily get broken
upgrades by upgrading to a server with a different locale. ?A Google
search seems to indicate the locale names are case-sensitive so I am
thinking the problem is that the user didn't have exact locales, and
needs that to use pg_upgrade.I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.
OK, that's what I needed to hear. I have applied the attached patch,
but only to 9.1 because of the risk of breakage. (This was only the
first bug report of this, and we aren't 100% certain about the case
issue.)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/pg_upgradetext/x-diffDownload
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 2117b7f..60c1fbb
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** static void
*** 333,345 ****
check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl)
{
! if (strcmp(oldctrl->lc_collate, newctrl->lc_collate) != 0)
pg_log(PG_FATAL,
"old and new cluster lc_collate values do not match\n");
! if (strcmp(oldctrl->lc_ctype, newctrl->lc_ctype) != 0)
pg_log(PG_FATAL,
"old and new cluster lc_ctype values do not match\n");
! if (strcmp(oldctrl->encoding, newctrl->encoding) != 0)
pg_log(PG_FATAL,
"old and new cluster encoding values do not match\n");
}
--- 333,346 ----
check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl)
{
! /* These are often defined with inconsistent case, so use pg_strcasecmp(). */
! if (pg_strcasecmp(oldctrl->lc_collate, newctrl->lc_collate) != 0)
pg_log(PG_FATAL,
"old and new cluster lc_collate values do not match\n");
! if (pg_strcasecmp(oldctrl->lc_ctype, newctrl->lc_ctype) != 0)
pg_log(PG_FATAL,
"old and new cluster lc_ctype values do not match\n");
! if (pg_strcasecmp(oldctrl->encoding, newctrl->encoding) != 0)
pg_log(PG_FATAL,
"old and new cluster encoding values do not match\n");
}
Excerpts from Bruce Momjian's message of mar may 24 15:59:59 -0400 2011:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.OK, that's what I needed to hear. I have applied the attached patch,
but only to 9.1 because of the risk of breakage. (This was only the
first bug report of this, and we aren't 100% certain about the case
issue.)
Hmm, I know the locale can be spelled "en_US.UTF-8" (note dash) in some
systems. So if you have a locale alias that makes en_US.utf8 the same
as en_US.UTF-8, the patched code would still not work. I wonder if
there's a need for canonicalization somewhere. Or maybe this is just
not worth the trouble.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Excerpts from Bruce Momjian's message of mar may 24 15:59:59 -0400 2011:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.OK, that's what I needed to hear. I have applied the attached patch,
but only to 9.1 because of the risk of breakage. (This was only the
first bug report of this, and we aren't 100% certain about the case
issue.)Hmm, I know the locale can be spelled "en_US.UTF-8" (note dash) in some
systems. So if you have a locale alias that makes en_US.utf8 the same
as en_US.UTF-8, the patched code would still not work. I wonder if
there's a need for canonicalization somewhere. Or maybe this is just
not worth the trouble.
I can easily remove dashes before the compare if people like that idea
--- I think you could argue that a dash is not significant, unless "ab-c"
and "a-bc" are different locales.--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Wed, May 25, 2011 at 1:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
Alvaro Herrera wrote:
Excerpts from Bruce Momjian's message of mar may 24 15:59:59 -0400 2011:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.OK, that's what I needed to hear. I have applied the attached patch,
but only to 9.1 because of the risk of breakage. (This was only the
first bug report of this, and we aren't 100% certain about the case
issue.)Hmm, I know the locale can be spelled "en_US.UTF-8" (note dash) in some
systems. So if you have a locale alias that makes en_US.utf8 the same
as en_US.UTF-8, the patched code would still not work. I wonder if
there's a need for canonicalization somewhere. Or maybe this is just
not worth the trouble.I can easily remove dashes before the compare if people like that idea --- I think you could argue that a dash is not significant, unless "ab-c" and "a-bc" are different locales.
I think the more we mush that string around, the more chance we have
of breaking something. What's wrong with insisting that people set
the value to the same thing? Like, really the same?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of mié may 25 13:33:41 -0400 2011:
On Wed, May 25, 2011 at 1:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
I can easily remove dashes before the compare if people like that idea --- I think you could argue that a dash is not significant, unless "ab-c" and "a-bc" are different locales.I think the more we mush that string around, the more chance we have
of breaking something. What's wrong with insisting that people set
the value to the same thing? Like, really the same?
No objection here to that idea.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Excerpts from Robert Haas's message of mi�� may 25 13:33:41 -0400 2011:
On Wed, May 25, 2011 at 1:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
I can easily remove dashes before the compare if people like that idea --- I think you could argue that a dash is not significant, unless "ab-c" and "a-bc" are different locales.I think the more we mush that string around, the more chance we have
of breaking something. What's wrong with insisting that people set
the value to the same thing? Like, really the same?No objection here to that idea.
I thought the problem was that they upgraded the OS and now the encoding
names changed, though they behaved the same. Is that now what is
happening? Can they supply the values with different cases?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Wed, May 25, 2011 at 2:13 PM, Bruce Momjian <bruce@momjian.us> wrote:
Alvaro Herrera wrote:
Excerpts from Robert Haas's message of mié may 25 13:33:41 -0400 2011:
On Wed, May 25, 2011 at 1:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
I can easily remove dashes before the compare if people like that idea --- I think you could argue that a dash is not significant, unless "ab-c" and "a-bc" are different locales.I think the more we mush that string around, the more chance we have
of breaking something. What's wrong with insisting that people set
the value to the same thing? Like, really the same?No objection here to that idea.
I thought the problem was that they upgraded the OS and now the encoding
names changed, though they behaved the same. Is that now what is
happening? Can they supply the values with different cases?
Oh, hmm. I don't know.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, May 25, 2011 at 2:13 PM, Bruce Momjian <bruce@momjian.us> wrote:
I thought the problem was that they upgraded the OS and now the encoding
names changed, though they behaved the same. �Is that now what is
happening? �Can they supply the values with different cases?
Oh, hmm. I don't know.
The original complaint wasn't too clear. But I agree with Robert that
case-insensitive comparison is about as far as it seems safe to go in
trying to allow for variant spellings of locale names. I would think
that a platform that randomly whacks such names around is going to find
a whole lot of software breaking, not only Postgres.
regards, tom lane
Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, May 25, 2011 at 2:13 PM, Bruce Momjian <bruce@momjian.us> wrote:
I thought the problem was that they upgraded the OS and now the encoding
names changed, though they behaved the same. ���Is that now what is
happening? ���Can they supply the values with different cases?Oh, hmm. I don't know.
The original complaint wasn't too clear. But I agree with Robert that
case-insensitive comparison is about as far as it seems safe to go in
trying to allow for variant spellings of locale names. I would think
that a platform that randomly whacks such names around is going to find
a whole lot of software breaking, not only Postgres.
Agreed. Unless an expert shows up and can tell us exactly how the
naming works, we have gone as far as we can go.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
I thought the problem was that they upgraded the OS and now the encoding
names changed, though they behaved the same. Is that now what is
happening? Can they supply the values with different cases?
In my case I never touched the locale. It was set by the OS. I presume
this is true for most people.
On tis, 2011-05-24 at 15:59 -0400, Bruce Momjian wrote:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.OK, that's what I needed to hear. I have applied the attached patch,
but only to 9.1 because of the risk of breakage. (This was only the
first bug report of this, and we aren't 100% certain about the case
issue.)
On FreeBSD, locale names appear to be case-sensitive:
$ LC_ALL=en_US.UTF-8 locale charmap
UTF-8
$ LC_ALL=en_US.utf-8 locale charmap
US-ASCII
$ LC_ALL=de_DE.UTF-8 date
Mi 1 Jun 2011 18:22:24 EEST
$ LC_ALL=de_DE.utf-8 date
Wed Jun 1 18:22:47 EEST 2011
So I think that change needs to be reverted.
Peter Eisentraut <peter_e@gmx.net> writes:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.
On FreeBSD, locale names appear to be case-sensitive:
$ LC_ALL=en_US.UTF-8 locale charmap
UTF-8
$ LC_ALL=en_US.utf-8 locale charmap
US-ASCII
Hm, surely the latter result indicates that "en_US.utf-8" is not in fact
a valid locale name?
It would only be a problem if different case-foldings of the same name
represented valid but different locales on some platform, and that seems
rather hard to believe (it would be a pretty foolish choice no?).
So I'm inclined to leave the code alone.
regards, tom lane
On ons, 2011-06-01 at 13:21 -0400, Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
I think you misread what I wrote, or I misexplained it, but never
mind. Matching locale names case-insensitively sounds reasonable to
me, unless someone has reason to believe it will blow up.On FreeBSD, locale names appear to be case-sensitive:
$ LC_ALL=en_US.UTF-8 locale charmap
UTF-8$ LC_ALL=en_US.utf-8 locale charmap
US-ASCIIHm, surely the latter result indicates that "en_US.utf-8" is not in fact
a valid locale name?It would only be a problem if different case-foldings of the same name
represented valid but different locales on some platform, and that seems
rather hard to believe (it would be a pretty foolish choice no?).
Well, initdb still succeeds if you give it an invalid locale name. It
warns, but that can easily be missed if initdb is hidden behind a few
other layers. If you then run pg_upgrade, you get a hosed instance.
On Jun1, 2011, at 20:28 , Peter Eisentraut wrote:
Well, initdb still succeeds if you give it an invalid locale name. It
warns, but that can easily be missed if initdb is hidden behind a few
other layers. If you then run pg_upgrade, you get a hosed instance.
Whats the rational behind that behaviour? Wouldn't it be more user-friendly
if initdb failed outright? It'd also be consistent with CREATE DATABASE...
template1=# create database test lc_collate 'invalid' ;
ERROR: invalid locale name invalid
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
On Jun1, 2011, at 20:28 , Peter Eisentraut wrote:
Well, initdb still succeeds if you give it an invalid locale name. It
warns, but that can easily be missed if initdb is hidden behind a few
other layers. If you then run pg_upgrade, you get a hosed instance.
Whats the rational behind that behaviour? Wouldn't it be more user-friendly
if initdb failed outright? It'd also be consistent with CREATE DATABASE...
I think we were being conservative about whether initdb would get it
right. Might be time to stiffen our spines a bit, now that the logic
has been through a few release cycles.
regards, tom lane
Tom Lane wrote:
Florian Pflug <fgp@phlo.org> writes:
On Jun1, 2011, at 20:28 , Peter Eisentraut wrote:
Well, initdb still succeeds if you give it an invalid locale name. It
warns, but that can easily be missed if initdb is hidden behind a few
other layers. If you then run pg_upgrade, you get a hosed instance.Whats the rational behind that behaviour? Wouldn't it be more user-friendly
if initdb failed outright? It'd also be consistent with CREATE DATABASE...I think we were being conservative about whether initdb would get it
right. Might be time to stiffen our spines a bit, now that the logic
has been through a few release cycles.
Do we want to revisit this?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +