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 (source WITH =, 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 specify an 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(at)justatheory(dot)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(at)sss(dot)pgh(dot)pa(dot)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

On Tue, Aug 20, 2013 at 8:53 PM, David E. Wheeler <david(at)justatheory(dot)com>wrote:

> On Aug 20, 2013, at 6:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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…
>

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

However, this restriction can be avoided either by writing dummy C-function
or touching catalog directly:

test=# insert into pg_cast values ((select oid from pg_type where typname =
'source'), (select oid from pg_type where typname = 'oid'), 0, 'e', 'b');
INSERT 341001 1

Then you can define desired restriction.

CREATE TABLE things (
source source NOT NULL,
within tstzrange NOT NULL,
EXCLUDE USING gist ((source::oid) WITH =, within WITH &&)
);

Probably, I'm missing something and casting enum to oid is somehow unsafe?

------
With best regards,
Alexander Korotkov.

Alexander Korotkov <aekorotkov(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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, but not a big deal.

David

On Wed, Aug 21, 2013 at 10:13:15AM -0400, Tom Lane wrote:
> Alexander Korotkov <aekorotkov(at)gmail(dot)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, 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.

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

Noah Misch <noah(at)leadboat(dot)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