From: "Regina Obe" <lr@pcorp.us>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'PostgreSQL-development'" <pgsql-hackers@postgresql.org>
References: <000001d2d5de$d8d66170$8a832450$@pcorp.us>
 <29994.1495809534@sss.pgh.pa.us>
In-Reply-To: <29994.1495809534@sss.pgh.pa.us>
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something
 change? CASE WHEN behavior oddity
Date: Fri, 26 May 2017 15:14:37 -0400
Message-ID: <000c01d2d654$518f2be0$f4ad83a0$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQGSyGid0WOb7K33IPoK2k+QsEQaEAKr4LhqonG3S4A=
Content-Language: en-us
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


> "Regina Obe" <lr@pcorp.us> writes:
>> 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;

> You might want to consider changing such usages to use regexp_match()
instead of regexp_matches().

>			regards, tom lane

Thanks.  I ended up swapping out with substring which was a bit shorter than
regexp_match()[].

But I've got similar problems with PostGIS topology logic and the easiest
change to make was take advantage
of the fact that you guys are treating CASE constant ... THEN  SRF ...

Differently 

Than 

CASE not_constant_based_on_table_value THEN  SRF ..


So I switched those to constant checks.  This feels a little dirty and
fragile to me though.

Is this behavior going to stay or change?

It seems inconsistent from a user perspective that

CASE constant .... == short-circuit skipping over SRFs that may  otherwise
fail

While 

CASE not_constant_table_dependent  doesn't short-circuit.

I can understand the motive behind it, it just feels a little inconsistent
from an end-user POV.


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