[HACKERS] Issue with circular references in VIEW

Hi,

There is an issue with version prior to 10 when dumping views with circular
references. I know that these views are now exported as views in 10 but they
are still exported as TABLE + RULE in prior versions. This conduct to the
following error when columns of sub-queries doesn't have the same aliases
names:
   ERROR:  SELECT rule's target entry 1 has different column name from
column "col_a"   DETAIL:  SELECT target entry is named "other_name1".

Here is the steps to reproduce:
   CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 text);       CREATE VIEW v_t1 (col_a, col_b) AS    WITH win_query AS (
  SELECT            1::INTEGER AS col1,            'b' ::text AS col2   )   SELECT          imp.col1 AS other_name1,
     imp.col2 AS other_name2   FROM win_query imp   UNION   SELECT          2::INTEGER AS col1,          'z'::text AS
col2  UNION   SELECT * FROM t1 GROUP BY f1 ;
 

This is translated into the following code by pg_dump with PostgreSQL 9.x:
   CREATE TABLE t1 (       f1 integer NOT NULL,       f2 text   );
   CREATE TABLE v_t1 (       col_a integer,       col_b text   );
   COPY t1 (f1, f2) FROM stdin;   \.
   CREATE RULE "_RETURN" AS       ON SELECT TO v_t1 DO INSTEAD  WITH win_query AS (        SELECT 1 AS col1,
'b'::textAS col2       )    SELECT imp.col1 AS other_name1,       imp.col2 AS other_name2      FROM win_query imp
UNION   SELECT 2 AS col1,       'z'::text AS col2   UNION    SELECT t1.f1,       t1.f2      FROM t1     GROUP BY
t1.f1;

and this dump can't be restored because of the error reported above.

It is clear that the user is responsible of using wrong aliases but
this doesn't generate error at creation time, and looking at the view
through the call of pg_get_viewdef(), aliases are correctly rewritten:

test_view=# \d+ v_t1                 View "public.v_t1"Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------col_a  | integer |           | plain    |col_b  | text    |
    | extended |
 
View definition:WITH win_query AS (        SELECT 1 AS col1,           'b'::text AS col2       )SELECT imp.col1 AS
col_a,  imp.col2 AS col_b  FROM win_query imp
 
UNIONSELECT 2 AS col_a,   'z'::text AS col_b
UNIONSELECT t1.f1 AS col_a,   t1.f2 AS col_b  FROM t1 GROUP BY t1.f1;


The rule code retrieved using pg_get_ruledef() reports the use of original
incorrect column's aliases:
   CREATE RULE "_RETURN" AS       ON SELECT TO v_t1 DO INSTEAD  WITH win_query AS (        SELECT 1 AS col1,
'b'::textAS col2       )    SELECT imp.col1 AS other_name1,       imp.col2 AS other_name2      FROM win_query imp
UNION   SELECT 2 AS col1,       'z'::text AS col2   UNION    SELECT t1.f1,       t1.f2      FROM t1     GROUP BY
t1.f1;

PostgreSQL 10 now use views and no more table+rule, so call to
pg_get_viewdef() self fix this issue. My question is do this
method to export views will be back-ported to prior version or
should we have to fix it an other way?

In the last case does the use of pg_get_viewdef() to reconstruct the
_RETURN rule could be a simple fix? For example:
   'CREATE RULE "_RETURN" AS           ON SELECT TO v_t1 DO INSTEAD %s;', pg_get_viewdef(...)

Of course manually rewriting the view and replace it fixes the issue
but I think that generating dump that can't be restored easily can
confuse users.

-- 
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org