Wrong results from function that selects from vier after "created or replace"

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Wrong results from function that selects from vier after "created or replace"
Date: 2020-10-13 00:01:41
Message-ID: 4DE6C9D8-77A5-4858-82CB-18728F59D715@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Read about the scheme in this two-part blog post:

"Distributed PostgreSQL on a Google Spanner Architecture":
(1) Storage Layer
https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-storage-layer/
(2) Query Layer
https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/

The same testcase produced the results that I expect (as set out above) both in the single-session test and in the two-session test.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2020-10-13 00:25:07 Re: BUG #15858: could not stat file - over 4GB
Previous Message Tom Lane 2020-10-12 22:16:52 Re: BUG #16329: Valgrind detects an invalid read when building a gist index with buffering