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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Aug 20, 2013 at 8:53 PM, David E. Wheeler <david@justatheory.com>wrote:
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 markedIMMUTABLE
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@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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-compatibleThe 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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-compatibleThe 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers