BUG #18710: "pg_get_viewdef" triggers assertions in special scenarios

Hi everyone,

I just submitted a patch for this bug.  Please find the patch links below. 

https://commitfest.postgresql.org/51/5383/
/messages/by-id/tencent_7ABF9B1F23B0C77606FC5FE3@qq.com

Thanks,
Zeng Man
 
------------------ Original ------------------
From: &nbsp;"PG&nbsp;Bug&nbsp;reporting&nbsp;form"<noreply@postgresql.org&gt;;
Date: &nbsp;Fri, Nov 15, 2024 10:51 AM
To: &nbsp;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;
Cc: &nbsp;"zengman"<zengman@halodbtech.com&gt;;
Subject: &nbsp;BUG #18710: "pg_get_viewdef" triggers assertions in special scenarios

&nbsp;
The following bug has been logged on the website:

Bug reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18710
Logged by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Man Zeng
Email address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zengman@halodbtech.com
PostgreSQL version: 14.14
Operating system:&nbsp;&nbsp; centos-8
Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

A prototype of the problem from
https://github.com/duckdb/pg_duckdb/issues/435
This exception can be reliably triggered by calling "pg_get_viewdef"

Step 1 ：
CREATE VIEW view_a AS
WITH RECURSIVE outermost(x) AS (
SELECT 1
UNION (WITH innermost1 AS (
SELECT 2)
SELECT * FROM outermost
UNION SELECT * FROM innermost1)
)
SELECT * FROM outermost ORDER BY 1;

Step 2 ：
SELECT oid FROM pg_class where relname = 'view_a';

Step 3：
SELECT pg_get_viewdef( this oid ); -- error

The abnormalities appear as follows
[postgres@iZuf6hwo0wgeev4dvua4csZ postgres]$ psql
psql (14.14)
Type "help" for help.

postgres=# CREATE VIEW view_a AS
postgres-# WITH RECURSIVE outermost(x) AS (
postgres(#&nbsp; SELECT 1
postgres(#&nbsp; UNION (WITH innermost1 AS (
postgres(#&nbsp; SELECT 2)
postgres(#&nbsp;&nbsp; SELECT * FROM outermost
postgres(#&nbsp;&nbsp; UNION SELECT * FROM innermost1)
postgres(#&nbsp; )
postgres-#&nbsp; SELECT * FROM outermost ORDER BY 1;
CREATE VIEW
postgres=# SELECT * FROM pg_class where relname = 'view_a';
oid&nbsp; | relname | relnamespace | reltype | reloftype | relowner | relam |
relfilenode | reltablespace | relpages | reltuples | relallvisible |
reltoastrelid | relhasindex | relisshared | relpersistence | relkind |
relnatt
s | relchecks | relhasrules | relhastriggers | relhassubclass |
relrowsecurity | relforcerowsecurity | relispopulated | relreplident |
relispartition | relrewrite | relfrozenxid | relminmxid | relacl |
reloptions | relpart
bound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+--------
--+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------
------
32768 | view_a&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2200 |&nbsp;&nbsp; 32770 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 |&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;
0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0 | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | p&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | v&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 | t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;
0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
(1 row)

postgres=# select pg_get_viewdef(32768);
TRAP: FailedAssertion("subquery-&gt;setOperations == NULL", File:
"ruleutils.c", Line: 6094, PID: 325948)
postgres: postgres postgres [local]
SELECT(ExceptionalCondition+0xb9)[0xb1a6c1]
postgres: postgres postgres [local] SELECT[0xa95d6b]
postgres: postgres postgres [local] SELECT[0xa960b5]
......

Maybe we can comment out the assertion that raises the exception, because I
looked up the code and found that the assertion doesn't seem to make a lot
of sense here.
I look forward to your discussion.

On Fri, Nov 15, 2024 at 07:28:25PM +0800, zengman wrote:

I just submitted a patch for this bug.&nbsp; Please find the patch links below.&nbsp;

https://commitfest.postgresql.org/51/5383/
/messages/by-id/tencent_7ABF9B1F23B0C77606FC5FE3@qq.com

Note that it is perfectly OK to create a CF entry with a thread
pointing to pgsql-bugs, as well. What you have done is also fine, so
let's move the discussion to pgsql-hackers :)
--
Michael

Hello, Michael&nbsp;

Can I invite you to help me with the code review?&nbsp;&nbsp;
This is a minor change, but it applies to all stable releases.

Thanks,
Man Zeng

&nbsp;
&nbsp;

On Fri, Nov 15, 2024 at 07:28:25PM +0800, zengman wrote:
&gt; I just submitted a patch for this bug.&amp;nbsp; Please find the patch links below.&amp;nbsp;
&gt;
&gt; https://commitfest.postgresql.org/51/5383/
&gt; /messages/by-id/tencent_7ABF9B1F23B0C77606FC5FE3@qq.com

Note that it is perfectly OK to create a CF entry with a thread
pointing to pgsql-bugs, as well.&nbsp; What you have done is also fine, so
let's move the discussion to pgsql-hackers :)
--
Michael