Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kS9OH-0003cX-8T for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 01:46:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kS9OG-0005j6-4U for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 01:46:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kS9OF-0005iz-Uy for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 01:46:11 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kS9OB-00086D-LD for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 01:46:11 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 09D1k4fg3403769; Mon, 12 Oct 2020 21:46:04 -0400 From: Tom Lane To: Bryn Llewellyn cc: pgsql-bugs@lists.postgresql.org Subject: Re: Wrong results from function that selects from vier after "created or replace" In-reply-to: References: <4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com> <3401031.1602549490@sss.pgh.pa.us> Comments: In-reply-to Bryn Llewellyn message dated "Mon, 12 Oct 2020 18:15:21 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3403767.1602553564.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 12 Oct 2020 21:46:04 -0400 Message-ID: <3403768.1602553564@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Bryn Llewellyn writes: > Tom Lane wrote: >> No. You marked the functions as "immutable", and then you broke that >> promise by changing what they'd need to output. > Thank you for the very quick response. This is what the Version 12 doc s= ays on =E2=80=9Cimmutable=E2=80=9D: >> An IMMUTABLE function cannot modify the database and is guaranteed to r= eturn the same results given the same arguments forever. Indeed. f1() has no arguments, therefore an immutable marking is a promise --- made by you to the database, NOT vice versa --- that its output will never change at all. (f2 likewise, although in this particular example that's not what matters.) Optimizations made on the strength of such a promise are not bugs. As a general rule, immutable functions that inspect the database's contents are probably wrongly marked. We don't forbid such coding, because there are narrow use-cases for it, but I tend to believe that it's a red flag for misdesign. > I couldn=E2=80=99t find an =E2=80=9Call bets are off=E2=80=9D caveat for= the case where > either an =E2=80=9Cimmutable=E2=80=9D function itself or any of its depe= ndency > parents is recompiled. We do not attempt to document what might go wrong when you lie about the volatility classification of a function. There are a lot of possibilities, many of them worse than what you have here, and there's no reason to think that the implications will be stable enough to be worth documenting. > I had read =E2=80=9Cforever=E2=80=9D to mean =E2=80=9Cforever until the = function, or any of > its dependency parents, is semantically changed=E2=80=9D. That's a curious reading of "forever". > And this is the caveated meaning that Oracle database implements for > its moral equivalent =E2=80=9Cdeterministic=E2=80=9D. Nowhere do we claim to do exactly what Oracle does, especially when it's not even the same syntax. "immutable" is NOT the same thing as "deterministic". regards, tom lane