Re: Check-out mutable functions in check constraints

On Fri, Jul 12, 2019 at 08:55:20AM +0200, Pavel Stehule wrote:
>Hi
>
>pá 12. 7. 2019 v 8:45 odesílatel Kyotaro Horiguchi <horikyota.ntt@gmail.com>
>napsal:
>
>> Hello.
>>
>> As mentioned in the following message:
>>
>>
>> https://www.postgresql.org/message-id/20190712.150527.145133646.horikyota.ntt%40gmail.com
>>
>> Mutable function are allowed in check constraint expressions but
>> it is not right. The attached is a proposed fix for it including
>> regression test.
>>
>> Other "constraints vs xxxx" checks do not seem to be exercised
>> but it would be another issue.
>>
>
>I think so this feature (although is correct) can breaks almost all
>applications - it is 20 year late.
>

I'm not sure it actually breaks such appliations.

Let's assume you have a mutable function (i.e. it may change return value
even with the same parameters) and you use it in a CHECK constraint. Then
I'm pretty sure your application is already broken in various ways and you
just don't know it (sometimes it subtle, sometimes less so).

If you have a function that actually is immutable and it's just not marked
accordingly, then that only requires a single DDL to fix that during
upgrade. I don't think that's a massive issue.

That being said, I don't know whether fixing this is worth the hassle.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services