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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
