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 1kSAz4-00070c-8G for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 03:28:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kSAz3-0005hC-3o for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 03:28:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kSAz2-0005h5-U2 for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 03:28:16 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kSAz0-000742-NB for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 03:28:15 +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 09D3SCFe3407848; Mon, 12 Oct 2020 23:28:12 -0400 From: Tom Lane To: Bryn Llewellyn cc: Christophe Pettus , "David G. Johnston" , PostgreSQL mailing lists Subject: Re: Wrong results from function that selects from vier after "created or replace" In-reply-to: <90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com> References: <4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com> <3401031.1602549490@sss.pgh.pa.us> <3403768.1602553564@sss.pgh.pa.us> <90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com> Comments: In-reply-to Bryn Llewellyn message dated "Mon, 12 Oct 2020 19:57:31 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3407846.1602559692.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 12 Oct 2020 23:28:12 -0400 Message-ID: <3407847.1602559692@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Bryn Llewellyn writes: > I see the now that my analysis was faulty. And that, moreover, I describ= ed Oracle Database=E2=80=99s meaning of =E2=80=9Cdeterministic=E2=80=9D wr= ongly. Yes indeed, both Postgres=E2=80=99s =E2=80=9Cimmutable=E2=80=9D and= Oracle=E2=80=99s =E2=80=9Cdeterministic=E2=80=9D rule out sensitivity to = database state. And =E2=80=9Cselect=E2=80=9D from table or a view must be = defined to be reading database state=E2=80=94even when I happen to know th= at the view text specifies a result that does not depend on database state= . FWIW, when I said "database state" I meant to include the contents of the system catalogs, not only user-defined tables. So redefining the view v as you did counts as a database state change. > I=E2=80=99m going you risk overtaxing your patience with a follow-up que= stion to test what you=E2=80=99ve just said. What=E2=80=99s your stance on= this? > ... > Should I understand that this outcome, while it might have wrongly seeme= d to me to support my bad mental model, actually tells me nothing? In othe= r words, should I understand that the _definition_ of =E2=80=9Cimmutable=E2= =80=9D would allow some future PG implementation justifiably still to retu= rn =E2=80=9C42=E2=80=9D after recompiling =E2=80=9Cmy_constant()=E2=80=9D. Yeah. Whether you get "42" or "17" in this example is an implementation artifact that could vary (and has varied, I think) across PG versions, depending on the exact plan caching behavior being used. It's even possible that the result would change in an asynchronous way within a single session, since hard-to-predict cache flush events could cause the plan in the calling query to be rebuilt. > And should I understand that I might to continue to see =E2=80=9C42=E2=80= =9D quite literally forever (even after pg-stop and pg-start) =E2=80=94unt= il I actually drop the function =E2=80=9Cmy_constant()=E2=80=9D. In our current implementation, you would not see the effects of the old function contents persisting into new backend processes, *in an example of this sort*. The reason why we insist that "immutable" means "no changes for the foreseeable future" is that immutable functions are allowed in index definitions, and there is no mechanism for rebuilding an index after a behavioral change of a function that it depends on. As a simple example, if you define f(x) =3D x+1 and then do create index on mytab (f(mycol)); then a query such as "select * from mytab where f(mycol) =3D 42" will return rows where mycol=3D41. If you then change f() so it returns x+2, that query will still return rows where mycol=3D41, because the corresponding index entries still contain 42. You'd need to manually reindex in order to bring the index into sync with the new function definition. From our standpoint, relaxing the definition of immutable would entail that the database undertakes to make that sort of thing happen transparently. While it's probably not impossible, it's not something we care to invest the required amount of effort in. regards, tom lane