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 1kS8L5-0005lz-1H for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 00:38:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kS8KY-0000Yi-7k for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 00:38:18 +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 1kS8KY-0000Yb-1s for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 00:38:18 +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 1kS8KT-0007V2-3y for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 00:38:17 +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 09D0cAuk3401032; Mon, 12 Oct 2020 20:38:10 -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: <4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com> References: <4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com> Comments: In-reply-to Bryn Llewellyn message dated "Mon, 12 Oct 2020 17:01:41 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3401030.1602549490.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 12 Oct 2020 20:38:10 -0400 Message-ID: <3401031.1602549490@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Bryn Llewellyn writes: > The attached self-contained testcase, "pg-bad-invalidation-bug.sql", see= ms to me definitely to demonstrate a bug. I've attached the spooled output= as "pg-bad-invalidation-bug.txt" Do you agree that this is a bug? No. You marked the functions as "immutable", and then you broke that promise by changing what they'd need to output. The planner had already inlined the original output value of f1() into the calling expressions in f2(), on the strength of it being allegedly immutable. There is no mechanism for undoing that (short of a forced cache flush or session end), nor should there need to be. If I mark the functions as either stable or volatile, I see the desired behavior. regards, tom lane