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 1kSAVN-0001TW-OK for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 02:57:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kSAVM-0004AH-HY for pgsql-bugs@arkaria.postgresql.org; Tue, 13 Oct 2020 02:57:36 +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 1kSAVM-0004A9-BQ for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 02:57:36 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kSAVK-0006qI-0x for pgsql-bugs@lists.postgresql.org; Tue, 13 Oct 2020 02:57:35 +0000 Received: by mail-pf1-x42b.google.com with SMTP id 10so6204856pfp.5 for ; Mon, 12 Oct 2020 19:57:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=TPDD2jk38+oDmfvNdM+r4JJRu33xuCX4DKJCuXVtiGY=; b=BbZm0IFAleAa36p/xXfbE0O8aeZu9NGCpIqCBtM444vFY4cHhdoDPJpICzVC0WBRUq KJFIKT99eOP+DWd8Wl9M8GGRVIaLdq8/5EJM19pu/qWMATomQu3xsewVb14RnTugl7B0 9PQNLdjOwB4EhkqaWqq4rseci+aD7zvKcYNZs= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=TPDD2jk38+oDmfvNdM+r4JJRu33xuCX4DKJCuXVtiGY=; b=uBl8iLpjTCDA3JQmtLiLIc3EfK+TX9zpEhn0BD2T5OCUhKB3EW2triQpUT4UUcanA5 G/Kb+2H/g0LJyZBg6uDS3U+dJE1kSfLbtWWLpukMbYK6ob89mAtv86PvYbwL25sf8Hqf 9KiIcdtB0vvKQjo0lLdgZaKIk5gzZq7KS1mtlVYwvQ1nfX6/Chy18KL3tB1oZkUWV+z8 l+mIlIAYlEFkvf9D91ABidq6CXKakqpJ/5Q41gs0vpQkVjukl4NTzMxrQl9MuPhDILqs PDc9NTR2oHTapF7K5xOXCPU+SgNN0GuRTZKe3Re5sKjxT6+zRQd0BCMy4OSa61Qj9Kzr X6ww== X-Gm-Message-State: AOAM532h7blPkqyX6MZr3fuv4EhLqzNQQX1VzN/CmnQAqzzpD1VfzKnx 85Uc/bxFetMwmJnWrk1t/uyDNg== X-Google-Smtp-Source: ABdhPJxrEq541QttOrXPsJTUPIhSWryGMwvSMo7I+p0cPkaEZGyqHHP3rk6CafzDsRQrAy6t5ZZlFQ== X-Received: by 2002:aa7:9501:0:b029:155:3b11:d5c4 with SMTP id b1-20020aa795010000b02901553b11d5c4mr22928485pfp.76.1602557852813; Mon, 12 Oct 2020 19:57:32 -0700 (PDT) Received: from ?IPv6:2601:646:101:7fda:f103:408a:fce6:4224? ([2601:646:101:7fda:f103:408a:fce6:4224]) by smtp.gmail.com with ESMTPSA id d8sm26854565pjr.46.2020.10.12.19.57.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 12 Oct 2020 19:57:32 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\)) Subject: Re: Wrong results from function that selects from vier after "created or replace" From: Bryn Llewellyn In-Reply-To: <3403768.1602553564@sss.pgh.pa.us> Date: Mon, 12 Oct 2020 19:57:31 -0700 Cc: PostgreSQL mailing lists Content-Transfer-Encoding: quoted-printable Message-Id: <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> To: Tom Lane PostgreSQL , Christophe Pettus , "David G. Johnston" X-Mailer: Apple Mail (2.3608.120.23.2.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Tom Lane wrote: =E2=80=A6We 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=E2=80=9D. regards, tom lane Thanks again, Tom. And thanks, too, to Christophe Pettus and to David = Johnston. I see the now that my analysis was faulty. And that, moreover, I = described Oracle Database=E2=80=99s meaning of =E2=80=9Cdeterministic=E2=80= =9D wrongly. 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 that the view text specifies a result that does not = depend on database state. I=E2=80=99m going you risk overtaxing your patience with a follow-up = question to test what you=E2=80=99ve just said. What=E2=80=99s your = stance on this? drop function if exists my_dependant() cascade; drop function if exists my_constant() cascade; create function my_constant() returns int immutable language plpgsql as $body$ begin return 42; end; $body$; create function my_dependant() returns int immutable language plpgsql as $body$ declare v constant int not null :=3D my_constant(); begin return v; end; $body$; select 'my_dependant(): '||my_dependant()::text; create or replace function my_constant() returns int immutable language plpgsql as $body$ begin return 17; end; $body$; select 'my_dependant(): '||my_dependant()::text; When I run this, I see =E2=80=9C42=E2=80=9D at the start and =E2=80=9C17=E2= =80=9D after recompiling the function =E2=80=9Cmy_constant()=E2=80=9D. Should I understand that this outcome, while it might have wrongly = seemed to me to support my bad mental model, actually tells me nothing? = In other words, should I understand that the _definition_ of = =E2=80=9Cimmutable=E2=80=9D would allow some future PG implementation = justifiably still to return =E2=80=9C42=E2=80=9D after recompiling = =E2=80=9Cmy_constant()=E2=80=9D. 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=94until I actually drop the function = =E2=80=9Cmy_constant()=E2=80=9D.