Re: [HACKERS] CAST Within EXCLUSION constraint
Noah Misch 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
Re: [HACKERS] CAST Within EXCLUSION constraint
On Wed, Aug 21, 2013 at 10:13:15AM -0400, Tom Lane wrote: > Alexander Korotkov 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CAST Within EXCLUSION constraint
On Aug 21, 2013, at 4:13 PM, Tom Lane 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CAST Within EXCLUSION constraint
Alexander Korotkov 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
Re: [HACKERS] CAST Within EXCLUSION constraint
On Tue, Aug 20, 2013 at 8:53 PM, David E. Wheeler wrote: > On Aug 20, 2013, at 6:50 PM, Tom Lane 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.
Re: [HACKERS] CAST Within EXCLUSION constraint
On Aug 20, 2013, at 6:50 PM, Tom Lane 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
Re: [HACKERS] CAST Within EXCLUSION constraint
"David E. Wheeler" 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
[HACKERS] 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