pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bryn Llewellyn <bryn@yugabyte.com>
To: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>
To: Christophe Pettus <xof@thebuild.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: Wrong results from function that selects from vier after "created or replace"
Date: Mon, 12 Oct 2020 19:57:31 -0700
Message-ID: <90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com> (raw)
In-Reply-To: <3403768.1602553564@sss.pgh.pa.us>
References: <4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com>
	<3401031.1602549490@sss.pgh.pa.us>
	<EF124763-B98B-4456-A499-64E0613D3EA6@yugabyte.com>
	<3403768.1602553564@sss.pgh.pa.us>

Tom Lane <tgl@sss.pgh.pa.us> wrote:

…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 “forever” to mean “forever until the function, or any of
> its dependency parents, is semantically changed”.

That's a curious reading of "forever”.

			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’s meaning of “deterministic” wrongly. Yes indeed, both Postgres’s “immutable” and Oracle’s “deterministic” rule out sensitivity to database state. And “select” from table or a view must be defined to be reading database state—even when I happen to know that the view text specifies a result that does not depend on database state.

I’m going you risk overtaxing your patience with a follow-up question to test what you’ve just said. What’s 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 := 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 “42” at the start and “17” after recompiling the function “my_constant()”.

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 “immutable” would allow some future PG implementation justifiably still to return “42” after recompiling “my_constant()”. And should I understand that I might to continue to see “42” quite literally forever (even after pg-stop and pg-start) —until I actually drop the function “my_constant()”.



















view thread (15+ messages)  latest in thread

Message-ID: <90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com>
Permalink:  ../90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com/
Also on:    postgresql.org/message-id/90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: bryn@yugabyte.com, tgl@sss.pgh.pa.us, xof@thebuild.com, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: Wrong results from function that selects from vier after "created or replace"
  In-Reply-To: <90FE6FD0-896D-449D-BF8F-CF580460C81C@yugabyte.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox