Re: Check-out mutable functions in check constraints

On Fri, Jul 12, 2019 at 07:59:13PM -0400, Tom Lane wrote:
>Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> On Fri, Jul 12, 2019 at 02:00:25PM +0200, Pavel Stehule wrote:
>>>> Mutable function are allowed in check constraint expressions but
>>>> it is not right. The attached is a proposed fix for it including
>>>> regression test.
>
>> Yes, and the question is whether this is the right thing to do (I think
>> it probably is).
>
>I'm pretty sure this change has been proposed before, and rejected before.
>Has anybody excavated in the archives for prior discussions?
>

Yes, I've done some quick searches like "volatile constraint" and so on.
There are a couple of relevant discussions:

2004: https://www.postgresql.org/message-id/flat/0C3A1AEC-6BE4-11D8-9224-000A95C88220%40myrealbox.com

2010: https://www.postgresql.org/message-id/flat/12849.1277918175%40sss.pgh.pa.us#736c8ef9d7810c0bb85f495490fd40f5

But I don't think the conclusions are particularly clear.

In the first thread you seem to agree with requiring immutable functions
for check constraints (and triggers for one-time checks). The second
thread ended up discussing some new related stuff in SQL standard.

There may be other threads and I just haven't found them, of course.

>There are, and always will be, lots of ways to shoot yourself in the foot.
>In the case at hand, I fear we might just encourage people to mark
>functions as immutable when they really aren't --- which will make their
>problems *worse* not better, because now other uses besides check
>constraints will also be at risk of misoptimization.
>
>> OTOH, even if we prohibit mutable functions in check constraints, people
>> can still create triggers doing those checks (and shoot themselves in
>> the foot that way).
>
>There are, and always will be, lots of ways to shoot yourself in the foot.
>In the case at hand, I fear we might just encourage people to mark
>functions as immutable when they really aren't --- which will make their
>problems *worse* not better, because now other uses besides check
>constraints will also be at risk of misoptimization.
>

True.

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