Re: 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(at)gmx(dot)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(at)gmx(dot)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(at)gmx(dot)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.