Re: Check-out mutable functions in check constraints

Hello, Thanks all!

At Sat, 13 Jul 2019 11:17:32 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <18372(dot)1563031052(at)sss(dot)pgh(dot)pa(dot)us>
> 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

If, we have a CURRENT_DATE() that always returns UTC timestamp
(or something like), then CURRENT_DATE()::text gives a local
representation. We may have constraints using CURRENT_DATE()
since it is truly immutable. I think the spec can be interpreted
as that.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center