BUG #12789: Views defined with VALUES lose their column names when dumped

">" == programble <programble@gmail.com> writes:

The following bug has been logged on the website:
Bug reference: 12789
Logged by: Curtis McEnroe
Email address: programble@gmail.com
PostgreSQL version: 9.4.1
Operating system: Mac OS X 10.10.1
Description:

A view created with explicit column names and defined as a VALUES statement
will lose its column names when dumped. When the dump is restored, the
view's columns are named "column1", "column2", etc.

I wrote a short repro script here:
https://gist.github.com/programble/a416df496bfb41259c86

For future reference and as a simpler testcase than the one in the
script:

# create view v1(a) as values (1);
CREATE VIEW

# select pg_get_viewdef('v1'::regclass);
pg_get_viewdef
----------------
VALUES (1);

Notice that the column name "a" is lost. Since pg_dump and so on rely on
pg_get_viewdef, dump and restore changes the column name back to
"column1".

The culprit is obviously in ruleutils.c:
get_simple_values_rte/get_values_def, which mistakenly thinks it only
has to deal with the result of transformValuesClause(), not considering
that the result of transformValuesClause might have been further
mogrified by DefineView. Treating this case as not being "simple" might
be one approach... not sure of the best way to detect that.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

For future reference and as a simpler testcase than the one in the
script:

# create view v1(a) as values (1);
CREATE VIEW

# select pg_get_viewdef('v1'::regclass);
pg_get_viewdef
----------------
VALUES (1);

Notice that the column name "a" is lost. Since pg_dump and so on rely on
pg_get_viewdef, dump and restore changes the column name back to
"column1".

The culprit is obviously in ruleutils.c:
get_simple_values_rte/get_values_def, which mistakenly thinks it only
has to deal with the result of transformValuesClause(), not considering
that the result of transformValuesClause might have been further
mogrified by DefineView. Treating this case as not being "simple" might
be one approach... not sure of the best way to detect that.

Yeah --- we can check to see if the tlist resnames match what's in the
RTE. It turns out that get_from_clause_item() is also buggy: apparently
the RTE_VALUES path through that has never been exercised, or at least
nobody has pointed out to us that it prints bad syntax. I'm guessing
that up to now, get_simple_values_rte *always* succeeds for situations
involving a VALUES RTE and so we never got there. The attached seems
to fix it though.

regards, tom lane