Re: String manipulation

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073