From: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
---|---|
To: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: CAST Within EXCLUSION constraint |
Date: | 2013-08-21 12:04:36 |
Message-ID: | CAPpHfdt6xnTEDShj1BvgzAT5avO00wrKaybCNVCkQmtKTxZkMA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Tiikkaja | 2013-08-21 12:28:24 | PL/pgSQL, RAISE and error context |
Previous Message | Andres Freund | 2013-08-21 11:49:33 | Re: Bison 3.0 updates |