From: "Regina Obe" <lr@pcorp.us>
To: "'PostgreSQL-development'" <pgsql-hackers@postgresql.org>
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something
 change?  CASE WHEN behavior oddity
Date: Fri, 26 May 2017 01:13:43 -0400
Message-ID: <000001d2d5de$d8d66170$8a832450$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AdLV3tSCOWDqvmKvR3+nW0Qp37Hx0A==
Content-Language: en-us
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


> Did something change with how exclusion constraints are handled?  I'm
trying to troubleshoot a regression we are having with PostGIS raster
support.

> As best I can guess, it's because exclusion constraints that used to work
in past versions are failing in PostgreSQL 10 with an error something like
> this:

> ERROR:  conflicting key value violates exclusion constraint
"enforce_spatially_unique_test_raster_columns_rast"
> ERROR:  new row for relation "test_raster_columns" violates check
constraint "enforce_coverage_tile_rast"

> Unfortunately I don't know how long this has been an issue since we had an
earlier test failing preventing the raster ones  from being tested.

> Thanks,
> Regina


I figured out the culprit was the change in CASE WHEN behavior with set
returning functions

Had a criteria something of the form:

CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false THEN
(regexp_matches(...))[1] ELSE ...  END
FROM sometable;


One thing that seems a little odd to me is why these return a record


SELECT CASE WHEN strpos('ABC', 'd') > 1 THEN (regexp_matches('a (b) c',
'd'))[1] ELSE 'a' END;

SELECT CASE WHEN false THEN (regexp_matches('a (b) c', 'd'))[1] ELSE 'a' END
FROM pg_tables;


And this doesn't - I'm guessing it has to do with this being a function of
the value of table, but it seems unintuitive 
From a user perspective.

SELECT CASE WHEN  strpos(f.tablename, 'ANY (ARRAY[') > 1 THEN
(regexp_matches('a (b) c', 'd'))[1] ELSE 'a' END
FROM pg_tables AS f;


Pre-PostgreSQL 10 this would return a row for each record in pg_tables


Thanks,
Regina


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers