Subject: Re: BUG #17161: Assert failed on opening a relation that exists in
 two schemas via the LANGUAGE SQL function
To: pgsql-bugs@lists.postgresql.org
References: <17161-048a1cdff8422800@postgresql.org>
From: Alexander Lakhin <exclusion@gmail.com>
Message-ID: <25f71277-c45a-8ef2-ef9a-9054c3d6e0b0@gmail.com>
Date: Sat, 28 Aug 2021 19:59:08 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <17161-048a1cdff8422800@postgresql.org>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/25f71277-c45a-8ef2-ef9a-9054c3d6e0b0%40gmail.com>
Precedence: bulk

25.08.2021 23:00, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      17161
> Logged by:          Alexander Lakhin
> Email address:      exclusion@gmail.com
> PostgreSQL version: 14beta3
> Operating system:   Ubuntu 20.04
> Description:        
>
> When executing the following query (based on the create_function_3 test):
> CREATE TABLE functest2 (a int, b int);
>
> CREATE SCHEMA temp_func_test;
> SET search_path TO temp_func_test, public;
> CREATE TABLE functest2 (a int, b int);
>
> CREATE FUNCTION functest_sri1() RETURNS SETOF int
> 	LANGUAGE SQL
> 	STABLE
> 	    RETURN (SELECT count(a) FROM functest2);
>
> SELECT * FROM functest_sri1();
>
My initial conclusion was incorrect. Duplicate relation is not needed in
the second schema. It's sufficient to just change search_path to another
schema:

CREATE TABLE functest2 (a int, b int);

CREATE SCHEMA temp_func_test;
SET search_path TO temp_func_test, public;

CREATE FUNCTION functest_sri1() RETURNS SETOF int
    LANGUAGE SQL
    STABLE
        RETURN (SELECT count(a) FROM functest2);

SELECT * FROM functest_sri1();

Best regards,
Alexander