Обсуждение: ALTER TYPE COLLATABLE?
I observe the following discrepancy between the 9.0 and 9.1 citext install scripts: *************** *** 52,58 **** STORAGE = extended, -- make it a non-preferred member of string type category CATEGORY = 'S', ! PREFERRED = false ); -- --- 49,56 ---- STORAGE = extended, -- make it a non-preferred member of string type category CATEGORY = 'S', ! PREFERRED = false, ! COLLATABLE = true ); -- What are we going to do to allow the citext update script to fix this? I see no sign that ALTER TYPE can fix it (and am unsure that we'd want to add such a feature, particularly not right now). Is it time for a direct UPDATE on the pg_type row? If so, to what? I see pg_type.typcollation is supposed to be an OID, so how the heck does one map a bool CREATE TYPE parameter into the catalog entry? regards, tom lane
On Feb 17, 2011, at 2:50 PM, Tom Lane wrote: > I observe the following discrepancy between the 9.0 and 9.1 citext > install scripts: > > *************** > *** 52,58 **** > STORAGE = extended, > -- make it a non-preferred member of string type category > CATEGORY = 'S', > ! PREFERRED = false > ); > > -- > --- 49,56 ---- > STORAGE = extended, > -- make it a non-preferred member of string type category > CATEGORY = 'S', > ! PREFERRED = false, > ! COLLATABLE = true > ); Oh, interesting. Would be nice if the docs has a link to the COLLATE clause docs. http://developer.postgresql.org/pgdocs/postgres/sql-createtype.html So I can see how to use it. :-) David
On tor, 2011-02-17 at 17:50 -0500, Tom Lane wrote: > What are we going to do to allow the citext update script to fix this? > I see no sign that ALTER TYPE can fix it (and am unsure that we'd want > to add such a feature, particularly not right now). How would this normally be handled if a type changes properties or wants to make use of a new property? I guess the answer is that there is no "normally". > Is it time for a direct UPDATE on the pg_type row? If so, to what? I see > pg_type.typcollation is supposed to be an OID, so how the heck does > one map a bool CREATE TYPE parameter into the catalog entry? It's 100, which is the OID of "default" in pg_collation. The value may be different for domains. (Earlier versions of the feature had a boolean column and a separate collation column for domains, but somehow it turned out to be quite redundant.)
Peter Eisentraut <peter_e@gmx.net> writes: > On tor, 2011-02-17 at 17:50 -0500, Tom Lane wrote: >> Is it time for a direct UPDATE on the pg_type row? If so, to what? I see >> pg_type.typcollation is supposed to be an OID, so how the heck does >> one map a bool CREATE TYPE parameter into the catalog entry? > It's 100, which is the OID of "default" in pg_collation. The value may > be different for domains. (Earlier versions of the feature had a > boolean column and a separate collation column for domains, but somehow > it turned out to be quite redundant.) While testing a fix for this, I observe that pg_dump is entirely broken on the subject, because it fails to dump anything at all about the typcollation property when dumping a base type. I also rather wonder exactly what pg_dump would dump to restore a value of pg_type.typcollation that's not either 0 or 100. In short: I think this feature is quite a few bricks shy of a load yet, and there's no point in my kluging something in citext until it settles down more. regards, tom lane
On fre, 2011-02-18 at 11:45 -0500, Tom Lane wrote: > While testing a fix for this, I observe that pg_dump is entirely > broken on the subject, because it fails to dump anything at all about > the typcollation property when dumping a base type. This is now fixed. > I also rather wonder > exactly what pg_dump would dump to restore a value of > pg_type.typcollation that's not either 0 or 100. It does what pg_dump does with other unrecognized or invalid type attributes: it ignores them.
Peter Eisentraut <peter_e@gmx.net> writes: > On fre, 2011-02-18 at 11:45 -0500, Tom Lane wrote: >> While testing a fix for this, I observe that pg_dump is entirely >> broken on the subject, because it fails to dump anything at all about >> the typcollation property when dumping a base type. > This is now fixed. >> I also rather wonder >> exactly what pg_dump would dump to restore a value of >> pg_type.typcollation that's not either 0 or 100. > It does what pg_dump does with other unrecognized or invalid type > attributes: it ignores them. I can't say that this makes me think any better of the design here. If a boolean true/false is a sufficient representation of a type's collation property, why isn't the column in pg_type just a boolean? If the idea of storing an OID is to allow reference to a choice of collations, why are we painting ourselves into a corner by dumping it as a boolean? ISTM there are exactly two sane choices here: 1. Change the pg_type column to a boolean. 2. Change the CREATE TYPE command's representation of the COLLATION property to be the name of the referenced collation. If there is a reason why the current design is actually correct, please explain it. regards, tom lane
On tis, 2011-03-01 at 16:31 -0500, Tom Lane wrote: > I can't say that this makes me think any better of the design here. > If a boolean true/false is a sufficient representation of a type's > collation property, why isn't the column in pg_type just a boolean? > If the idea of storing an OID is to allow reference to a choice of > collations, why are we painting ourselves into a corner by dumping > it as a boolean? The same column is used for base types, which can only have default collation or nothing, and domains, which can have any collation. We could of course also have two separate columns, one typcollatable boolean, and the typcollation only used by domains, and an earlier patch had that, but as it turned out the code that ends up using this is simplest if there is only one column. We could also (probably) support arbitrary nondefault collations on base types, but that sounds a bit odd, so I wouldn't want to support it yet unless there is a real use case.
Peter Eisentraut <peter_e@gmx.net> writes: > On tis, 2011-03-01 at 16:31 -0500, Tom Lane wrote: >> If a boolean true/false is a sufficient representation of a type's >> collation property, why isn't the column in pg_type just a boolean? >> If the idea of storing an OID is to allow reference to a choice of >> collations, why are we painting ourselves into a corner by dumping >> it as a boolean? > The same column is used for base types, which can only have default > collation or nothing, and domains, which can have any collation. That seems like a 100% arbitrary distinction between base types and domains, to the detriment of base types, which is odd since in most other ways base types are much more flexible than domains. > We > could of course also have two separate columns, one typcollatable > boolean, and the typcollation only used by domains, and an earlier patch > had that, but as it turned out the code that ends up using this is > simplest if there is only one column. We could also (probably) support > arbitrary nondefault collations on base types, but that sounds a bit > odd, so I wouldn't want to support it yet unless there is a real use > case. Well, I think a use case will pop up PDQ --- contrib/citext seems like the most likely first candidate. I guess that since the CREATE TYPE parameter is named COLLATABLE, we could extend in an upward-compatible way by adding a parameter "COLLATION name", but I would just as soon not have a parameter that's got such an obviously short time-to-live. regards, tom lane
On ons, 2011-03-02 at 16:00 -0500, Tom Lane wrote: > That seems like a 100% arbitrary distinction between base types and > domains, to the detriment of base types, which is odd since in most > other ways base types are much more flexible than domains. Well, base types don't support check constraints either. So conceptually, there is a useful distinction, namely that domains are sort of a macro for a column definition. > Well, I think a use case will pop up PDQ --- contrib/citext seems like > the most likely first candidate. Why would citext need a nondefault default collation? OK, something that will probably be opened for discussion in 9.2 is fitting case-insensitivity into the core collation/type system, and then this might come into play, but we don't really know how the details of that will look like. > I guess that since the CREATE TYPE parameter is named COLLATABLE, > we could extend in an upward-compatible way by adding a parameter > "COLLATION name", Yes. > but I would just as soon not have a parameter that's got such an > obviously short time-to-live. I think the COLLATABLE parameter would still have a reason to live even then.