[曾满 <zengman@halodbtech.com>]

Hi everyone,



The following bug has been logged on the website:


Bug reference:      18710
Logged by:          Man Zeng
Email address:      zengman(at)halodbtech(dot)com
PostgreSQL version: 14.14
Operating system:   centos-8
Description:        




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(at)iZuf6hwo0wgeev4dvua4csZ postgres]$ psql
psql (14.14)
Type "help" for help.


postgres=# CREATE VIEW view_a AS 
postgres-# WITH RECURSIVE outermost(x) AS (
postgres(#  SELECT 1
postgres(#  UNION (WITH innermost1 AS (
postgres(#  SELECT 2)
postgres(#   SELECT * FROM outermost
postgres(#   UNION SELECT * FROM innermost1)
postgres(#  )
postgres-#  SELECT * FROM outermost ORDER BY 1;
CREATE VIEW
postgres=# SELECT * FROM pg_class where relname = 'view_a';
  oid  | 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  |         2200 |   32770 |         0 |       10 |     0 |  
        0 |             0 |        0 |        -1 |             0 |          
  0 | f           | f           | p              | v       |        
1 |         0 | t           | f              | f              | f           
  | f                   | t              | n            | f              |  
       0 |            0 |          0 |        |            | 
(1 row)




postgres=# select pg_get_viewdef(32768);
TRAP: FailedAssertion("subquery->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]
......




Fix:


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.


Please find the patch attached.

Attachments:

  [application/octet-stream] v1-0001-Remove-invalid-assertion.patch (3.7K, ../tencent_7ABF9B1F23B0C77606FC5FE3@qq.com/3-v1-0001-Remove-invalid-assertion.patch)
  download | inline diff:
From 635cda1299793134cc2d71a9f2e130225346268f Mon Sep 17 00:00:00 2001
From: zengman <zengman@halodbtech.com>
Date: Fri, 15 Nov 2024 18:06:22 +0800
Subject: [PATCH] Remove invalid assertion

---
 src/backend/utils/adt/ruleutils.c         |  1 -
 src/test/regress/expected/create_view.out | 31 ++++++++++++++++++++++-
 src/test/regress/sql/create_view.sql      | 11 ++++++++
 3 files changed, 41 insertions(+), 2 deletions(-)

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 99d9bb5d6f..fd85adb0ef 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -6091,7 +6091,6 @@ get_setop_query(Node *setOp, Query *query, deparse_context *context,
 		Query	   *subquery = rte->subquery;
 
 		Assert(subquery != NULL);
-		Assert(subquery->setOperations == NULL);
 		/* Need parens if WITH, ORDER BY, FOR UPDATE, or LIMIT; see gram.y */
 		need_paren = (subquery->cteList ||
 					  subquery->sortClause ||
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index b1b96a8874..ce80d7cc89 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -2028,6 +2028,34 @@ select viewname from pg_views where viewname = 'tt27v'; -- Ok to access a system
 (1 row)
 
 reset restrict_nonsystem_relation_kind;
+CREATE VIEW tt28v 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;
+SELECT pg_get_viewdef('tt28v', true);
+             pg_get_viewdef              
+-----------------------------------------
+  WITH RECURSIVE outermost(x) AS (      +
+          SELECT 1 AS "?column?"        +
+         UNION                          +
+         ( WITH innermost1 AS (         +
+                  SELECT 2 AS "?column?"+
+                 )                      +
+          SELECT outermost_1.x          +
+            FROM outermost outermost_1  +
+         UNION                          +
+          SELECT innermost1."?column?"  +
+            FROM innermost1)            +
+         )                              +
+  SELECT outermost.x                    +
+    FROM outermost                      +
+   ORDER BY outermost.x;
+(1 row)
+
 -- clean up all the random objects we made above
 DROP SCHEMA temp_view_test CASCADE;
 NOTICE:  drop cascades to 27 other objects
@@ -2059,7 +2087,7 @@ drop cascades to view aliased_view_2
 drop cascades to view aliased_view_3
 drop cascades to view aliased_view_4
 DROP SCHEMA testviewschm2 CASCADE;
-NOTICE:  drop cascades to 77 other objects
+NOTICE:  drop cascades to 78 other objects
 DETAIL:  drop cascades to table t1
 drop cascades to view temporal1
 drop cascades to view temporal2
@@ -2137,3 +2165,4 @@ drop cascades to view tt26v
 drop cascades to table tt26
 drop cascades to table tt27v_tbl
 drop cascades to view tt27v
+drop cascades to view tt28v
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 662bf57c86..b42c0b26fd 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -717,6 +717,17 @@ insert into tt27v values (1); -- Error
 select viewname from pg_views where viewname = 'tt27v'; -- Ok to access a system view.
 reset restrict_nonsystem_relation_kind;
 
+CREATE VIEW tt28v 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;
+
+SELECT pg_get_viewdef('tt28v', true);
+
 -- clean up all the random objects we made above
 DROP SCHEMA temp_view_test CASCADE;
 DROP SCHEMA testviewschm2 CASCADE;
-- 
2.27.0