Re: Check-out mutable functions in check constraints

pá 12. 7. 2019 v 13:11 odesílatel Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
napsal:

> 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(dot)ntt(at)gmail(dot)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).
>

Years ago SQL functions was used for checks instead triggers - I am not
sure if this pattern was in documentation or not, but surely there was not
any warning against it.

You can see some documents with examples

CREATE OR REPLACE FUNCTION check_func(int)
RETURNS boolean AS $$
SELECT 1 FROM tab WHERE id = $1;
$$ LANGUAGE sql;

CREATE TABLE foo( ... id CHECK(check_func(id)));

> 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.
>

These functions are stable, and this patch try to prohibit it.

Regards

Pavel

>
> 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
>
>