From: | "Regina Obe" <lr(at)pcorp(dot)us> |
---|---|
To: | "'PostgreSQL-development'" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity |
Date: | 2017-05-26 05:13:43 |
Message-ID: | 000001d2d5de$d8d66170$8a832450$@pcorp.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Rijkers | 2017-05-26 06:10:21 | logical replication - still unstable after all these months |
Previous Message | Robert Haas | 2017-05-26 02:45:09 | Re: pg_dump ignoring information_schema tables which used in Create Publication. |