Hello, Thanks all! At Sat, 13 Jul 2019 11:17:32 -0400, Tom Lane <t...@sss.pgh.pa.us> wrote in <18372.1563031...@sss.pgh.pa.us> > Tomas Vondra <tomas.von...@2ndquadrant.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
