Обсуждение: [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
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
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
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
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