Thread: Re: [BUGS] BUG #6034: pg_upgrade fails when it should not.
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
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 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. +
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. + 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-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?). 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. +