Re: String manipulation

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I don't think it is good practice for a CHECK constraint to change its
> > behavior based on a GUC variable.
>
> You can develop comparable "failure scenarios" for any of the GUC
> variables that affect query semantics --- timezone, sql_inheritance,
> you name it. Locking them all down when a check constraint or function
> or view is created seems impractical ... and if we did do it then we'd
> get complaints about that too. ("What do you mean I can't change the
> setting later?")
>
> In practice I think we have to assume that those variables are set
> consistently within any one application. If you go frobbing them
> on-the-fly then you're going to have issues.
>
> I suppose paranoid sorts might lobby to make any GUC variable that can
> change query semantics be a superuser-only setting, but to me that cure
> sounds worse than the disease.

What concerned me is that it would actually make data the passed the
CHECK constraint initially fail later. Look at this:

test=> CREATE TABLE test (x TEXT CHECK (x ~ '(a|x).*'), y INT);
CREATE TABLE
test=> INSERT INTO test VALUES ('a', 1);
INSERT 380556 1
test=> SET regex_flavor = 'basic';
SET
test=> UPDATE test SET y=2;
ERROR: new row for relation "test" violates check constraint "test_x"

The UPDATE fails even when the row isn't changed. Certainly interesting.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073