Thread: CAST Within EXCLUSION constraint

Hackers,

I am trying to do something like this:
   CREATE TYPE source AS ENUM(       'fred', 'wilma', 'barney', 'betty'   );
   CREATE EXTENSION btree_gist;
   CREATE TABLE things (       source source NOT NULL,       within tstzrange NOT NULL,       EXCLUDE USING gist
(sourceWITH =, within WITH &&)   ); 

Alas, enums are not supported by btree_gist:
   try.sql:13: ERROR:  data type source has no default operator class for access method "gist"   HINT:  You must
specifyan operator class for the index or define a default operator class for the data type. 

Well, maybe I can cast it? But no, changing the EXCLUDE line to
       EXCLUDE USING gist (source::text WITH =, within WITH &&)

Yields a syntax error:
   try.sql:13: ERROR:  syntax error at or near "::"   LINE 4:     EXCLUDE USING gist (source::text WITH =, within WITH
&&)

So that's out. Why shouldn't :: be allowed?

No problem, I can use CAST(), right? So I try:
       EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)

Not so much:
   try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE

I guess it's because locale settings might change, and therefore change the text representation? Seems unlikely,
though.

I guess I can create my own IMMUTABLE function over the ENUM:
   CREATE FUNCTION source_to_text(       source   ) RETURNS TEXT LANGUAGE sql STRICT IMMUTABLE AS $$       SELECT
$1::text;  $$; 

So this works:
   EXCLUDE USING gist (source_to_text(source) WITH =, within WITH &&)

So I guess that’s good enough for now. But should :: really be a syntax error in index expressions?

Thanks,

David

"David E. Wheeler" <david@justatheory.com> writes:
> Well, maybe I can cast it? But no, changing the EXCLUDE line to
>         EXCLUDE USING gist (source::text WITH =, within WITH &&)
> Yields a syntax error:
>     try.sql:13: ERROR:  syntax error at or near "::"
>     LINE 4:     EXCLUDE USING gist (source::text WITH =, within WITH &&)
> So that's out. Why shouldn't :: be allowed?

You need more parentheses -- (source::text) would've worked.

> No problem, I can use CAST(), right? So I try:
>         EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)
> Not so much:
>     try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE
> I guess it's because locale settings might change, and therefore change the text representation? Seems unlikely,
though.

Not locale, just renaming one of the values would be enough to break that.
Admittedly we don't provide an official way to do that ATM, but you can do
an UPDATE on pg_enum.
        regards, tom lane

On Aug 20, 2013, at 6:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> You need more parentheses -- (source::text) would've worked.

Alas, no, same problem as for CAST():
 ERROR:  functions in index expression must be marked IMMUTABLE

>> No problem, I can use CAST(), right? So I try:
>>        EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)
>> Not so much:
>>    try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE
>> I guess it's because locale settings might change, and therefore change the text representation? Seems unlikely,
though.
>
> Not locale, just renaming one of the values would be enough to break that.
> Admittedly we don't provide an official way to do that ATM, but you can do
> an UPDATE on pg_enum.

Ah, right. Maybe if there was a way to get at some immutable numeric value…

Thanks,

David

Alexander Korotkov <aekorotkov@gmail.com> writes:
> It seems reasonable to me to cast enum to oid. However, creating casts
> without function isn't allowed for enums.

> test=# create cast (source as oid) without function;
> ERROR:  enum data types are not binary-compatible

The reason for that is you'd get randomly different results on another
installation.  In this particular application, I think David doesn't
really care about what values he gets as long as they're distinct,
so this might be an OK workaround for him.  But that's the reasoning
for the general prohibition.
        regards, tom lane

On Aug 21, 2013, at 4:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> test=# create cast (source as oid) without function;
>> ERROR:  enum data types are not binary-compatible
>
> The reason for that is you'd get randomly different results on another
> installation.  In this particular application, I think David doesn't
> really care about what values he gets as long as they're distinct,
> so this might be an OK workaround for him.  But that's the reasoning
> for the general prohibition.

I’m okay with my function that casts to text, at least for now. An integer would be nicer, likely smaller for my index,
butnot a big deal. 

David

On Wed, Aug 21, 2013 at 10:13:15AM -0400, Tom Lane wrote:
> Alexander Korotkov <aekorotkov@gmail.com> writes:
> > It seems reasonable to me to cast enum to oid. However, creating casts
> > without function isn't allowed for enums.
> 
> > test=# create cast (source as oid) without function;
> > ERROR:  enum data types are not binary-compatible
> 
> The reason for that is you'd get randomly different results on another
> installation.  In this particular application, I think David doesn't
> really care about what values he gets as long as they're distinct,
> so this might be an OK workaround for him.  But that's the reasoning
> for the general prohibition.

While a WITHOUT FUNCTION cast does *guarantee* that flaw, working around the
restriction with a cast function is all too likely to create the same flaw.
Here's the comment about the restriction:
     * Theoretically you could build a user-defined base type that is     * binary-compatible with a composite, enum,
orarray type.  But we     * disallow that too, as in practice such a cast is surely a mistake.     * You can always
workaround that by writing a cast function.
 

That's reasonable enough, but we could reduce this to a WARNING.  Alexander
shows a credible use case.  A superuser can easily introduce breakage through
careless addition of WITHOUT FUNCTION casts.  Permitting borderline cases
seems more consistent with the level of user care already expected in this
vicinity.

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com

Noah Misch <noah@leadboat.com> writes:
> On Wed, Aug 21, 2013 at 10:13:15AM -0400, Tom Lane wrote:
>> The reason for that is you'd get randomly different results on another
>> installation.  In this particular application, I think David doesn't
>> really care about what values he gets as long as they're distinct,
>> so this might be an OK workaround for him.  But that's the reasoning
>> for the general prohibition.

> While a WITHOUT FUNCTION cast does *guarantee* that flaw, working around the
> restriction with a cast function is all too likely to create the same flaw.
> Here's the comment about the restriction:

>          * Theoretically you could build a user-defined base type that is
>          * binary-compatible with a composite, enum, or array type.  But we
>          * disallow that too, as in practice such a cast is surely a mistake.
>          * You can always work around that by writing a cast function.

> That's reasonable enough, but we could reduce this to a WARNING.  Alexander
> shows a credible use case.  A superuser can easily introduce breakage through
> careless addition of WITHOUT FUNCTION casts.  Permitting borderline cases
> seems more consistent with the level of user care already expected in this
> vicinity.

Well, if we're gonna allow it, let's just allow it --- I don't see much
point in a WARNING here.  As you say, superusers are presumed to be
responsible adults.
        regards, tom lane