WRONG RESULTS FROM FUNCTION THAT SELECTS FROM VIER AFTER "CREATED OR REPLACE"
The attached self-contained testcase, "pg-bad-invalidation-bug.sql", seems 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?
NOTE: I ran my testcase in PG Version 12.4. I also ran it in PG version 11.2 (for reasons that I explain at the end). The outcome was identical in both of the enviromnments.
IF you do agree THEN
IF it's already filed THEN What's the bug number? What's the URL to this existing bug? ELSE Please use the information here to file it. What's the number of, and URL to, the newly-filed bug? END IF
ELSE Please explain why it isn't a bug. END
The script relies on the ordinary user "u1" in the database "demo". You can use what suits you. It drops and (re)creates everything that it needs.
SUMMARY
"f2()" (returns text language plpgsql) depends on "f1()" (returns text language gsql). The same behavior is seen if "f1()" is language plpgsql. But "f2()" can be written only using language plpgsql. Here's the body of "f2()":
declare t1 constant text := f1(); t2 constant text := (select f1()); t3 text not null := ''; begin execute 'select f1()' into t3; return t1||' | '||t2||' | '||t3; end;
Here's the body of "f1()":
select x from v;
And here's the (starting) definition of the view "v":
select 'dog' as x;
ANALYSIS
When an object with a closure of dependent objects is changed, than all of these should be immediately invalidated so that they must be re-compiled before next use. This rule should hold not only within a single sesssion but also across all concurrent sessions. Here's the dependecy graph for the present testcase:
function f2() depends on function f1() depends on view v
So when view "v" suffers "create or replace" to give it this new definition:
select 'cat' as x;
The very next use of "f1()" should return this:
cat
and the very next use of "f2()" should return this
cat | cat | cat
In fact, "f2()" returns this:
dog | dog | cat
. And it does this, even when all steps are done in a single session. You can do the experiment by using two concurrent sessions. Do everything in "Session One" except for this, which you do (at the same point in the overall flow) in "Session Two":
create or replace view v as select 'cat' as x;
The buggy outcome is unchanged. This is to be expected because the buggy outcome is seen even in a single-session test.
FINALLY
I work for Yugabyte, Inc. We make an open-source distrubuted SQL database. It directly uses the PostgreSQL code (at Version 11.2) for its SQL processing layer. This has been wired up to a distrubuted storage layer, written in C and C++ by Yugabyte engineers, and inspired by the design of Google Spanner.
