Horton

Thread

Commits

  1. Ensure that pg_get_ruledef()'s output matches pg_get_viewdef()'s.

  1. Issue with circular references in VIEW

    Gilles Darold <gilles.darold@dalibo.com> — 2017-07-24T14:34:36Z

    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'::text AS 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
UNION
 SELECT 2 AS col_a,
    'z'::text AS col_b
UNION
 SELECT 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'::text AS 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

  2. Re: Issue with circular references in VIEW

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-07-24T17:19:05Z

    Gilles Darold <gilles.darold@dalibo.com> writes:
> 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:

The core of this issue, I think, is that pg_get_viewdef() knows that it
should make what it prints have output column names that match the view,
whereas pg_get_ruledef() does not, even when it is printing an ON SELECT
rule.  This is a little bit surprising --- you'd really expect those
functions to produce identical SELECT statements --- and I think it's
likely to break other tools even if pg_dump has managed to skirt the
issue.  So I'm inclined to think in terms of fixing it at that level
rather than in pg_dump.  It doesn't look like it would be hard to fix:
both functions ultimately call get_query_def(), it's just that one passes
down a tuple descriptor for the view while the other currently doesn't.

			regards, tom lane

  3. Re: Issue with circular references in VIEW

    Gilles Darold <gilles.darold@dalibo.com> — 2017-07-24T18:43:10Z

    Le 24/07/2017 à 19:19, Tom Lane a écrit :
> Gilles Darold <gilles.darold@dalibo.com> writes:
>> 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:
> The core of this issue, I think, is that pg_get_viewdef() knows that it
> should make what it prints have output column names that match the view,
> whereas pg_get_ruledef() does not, even when it is printing an ON SELECT
> rule.  This is a little bit surprising --- you'd really expect those
> functions to produce identical SELECT statements --- and I think it's
> likely to break other tools even if pg_dump has managed to skirt the
> issue.  So I'm inclined to think in terms of fixing it at that level
> rather than in pg_dump.  It doesn't look like it would be hard to fix:
> both functions ultimately call get_query_def(), it's just that one passes
> down a tuple descriptor for the view while the other currently doesn't.

I was thinking that this was intentional that pg_get_ruledef() returns
the raw code typed by the user. I will fix it and send a patch following
your explanation.

Thanks.

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

  4. Re: Issue with circular references in VIEW

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-07-24T19:18:36Z

    Gilles Darold <gilles.darold@dalibo.com> writes:
> Le 24/07/2017 à 19:19, Tom Lane a écrit :
>> ... I'm inclined to think in terms of fixing it at that level
>> rather than in pg_dump.  It doesn't look like it would be hard to fix:
>> both functions ultimately call get_query_def(), it's just that one passes
>> down a tuple descriptor for the view while the other currently doesn't.

> I was thinking that this was intentional that pg_get_ruledef() returns
> the raw code typed by the user. I will fix it and send a patch following
> your explanation.

Oh, I just committed a patch.

			regards, tom lane

  5. Re: Issue with circular references in VIEW

    Gilles Darold <gilles.darold@dalibo.com> — 2017-07-24T19:43:33Z

    Le 24/07/2017 à 21:18, Tom Lane a écrit :
> Gilles Darold <gilles.darold@dalibo.com> writes:
>> Le 24/07/2017 à 19:19, Tom Lane a écrit :
>>> ... I'm inclined to think in terms of fixing it at that level
>>> rather than in pg_dump.  It doesn't look like it would be hard to fix:
>>> both functions ultimately call get_query_def(), it's just that one passes
>>> down a tuple descriptor for the view while the other currently doesn't.
>> I was thinking that this was intentional that pg_get_ruledef() returns
>> the raw code typed by the user. I will fix it and send a patch following
>> your explanation.
> Oh, I just committed a patch.

That's fine, I'm sure it is better than the one I could produce :-)
Thanks for fixing this issue.

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