Re: Check-out mutable functions in check constraints

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On Fri, Jul 12, 2019 at 07:59:13PM -0400, Tom Lane wrote:
>> 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.

Well, I think that second thread is very relevant here, because
it correctly points out that we are *required by spec* to allow
check constraints of the form CHECK(datecol <= CURRENT_DATE) and
related tests. See the stuff about "retrospectively deterministic"
predicates in SQL:2003 or later.

I suppose you could imagine writing some messy logic that allowed the
specific cases called out by the spec but not any other non-immutable
function calls. But that just leaves us with an inconsistent
restriction. If the spec is allowing this because it can be seen
to be safe, why should we not allow other cases that the user has
taken the trouble to prove to themselves are safe? (If their proof is
wrong, well, it wouldn't be the first bug in anyone's SQL application.)

regards, tom lane