About bug #6049
I looked into $SUBJECT, which complains about this:
CREATE VIEW test_view AS VALUES (1), (2), (3) ORDER BY 1;
This dumps like so:
regression=# \d+ test_view
View "public.test_view"
Column | Type | Modifiers | Storage | Description
---------+---------+-----------+---------+-------------
column1 | integer | | plain |
View definition:
VALUES (1), (2), (3)
ORDER BY "*VALUES*".column1;
which is problematic because it'll fail during dump/restore, because
you can't write it that way:
regression=# VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
ERROR: invalid reference to FROM-clause entry for table "*VALUES*"
LINE 1: VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
^
HINT: There is an entry for table "*VALUES*", but it cannot be referenced from this part of the query.
The HINT gives a hint what's going on: we make an RTE for the VALUES
clause, and then we have to give it an alias, for which we use
"*VALUES*". But the code is trying to hide the existence of that
nonstandard alias by not exposing it in the parser's p_relnamespace
list. So you can write column1 to refer to the first result column
of the VALUES, but not "*VALUES*".column1.
On reflection this looks pretty stupid --- column1 is just as
nonstandard an alias, but we're allowing that to be used explicitly,
so why not the made-up table alias as well?
But anyway, there are basically two things we could do here: either
allow the table alias to be referenced, or try to teach ruleutils.c
not to qualify the column reference. The second looks pretty tricky
and maybe not future-proof, so I'm leaning to the first. Comments?
regards, tom lane
On Fri, Jun 3, 2011 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I looked into $SUBJECT, which complains about this:
CREATE VIEW test_view AS VALUES (1), (2), (3) ORDER BY 1;
This dumps like so:
regression=# \d+ test_view
View "public.test_view"
Column | Type | Modifiers | Storage | Description
---------+---------+-----------+---------+-------------
column1 | integer | | plain |
View definition:
VALUES (1), (2), (3)
ORDER BY "*VALUES*".column1;which is problematic because it'll fail during dump/restore, because
you can't write it that way:regression=# VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
ERROR: invalid reference to FROM-clause entry for table "*VALUES*"
LINE 1: VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
^
HINT: There is an entry for table "*VALUES*", but it cannot be referenced from this part of the query.The HINT gives a hint what's going on: we make an RTE for the VALUES
clause, and then we have to give it an alias, for which we use
"*VALUES*". But the code is trying to hide the existence of that
nonstandard alias by not exposing it in the parser's p_relnamespace
list. So you can write column1 to refer to the first result column
of the VALUES, but not "*VALUES*".column1.On reflection this looks pretty stupid --- column1 is just as
nonstandard an alias, but we're allowing that to be used explicitly,
so why not the made-up table alias as well?But anyway, there are basically two things we could do here: either
allow the table alias to be referenced, or try to teach ruleutils.c
not to qualify the column reference. The second looks pretty tricky
and maybe not future-proof, so I'm leaning to the first. Comments?
I think that makes sense, although it would less totally arbitrary if
the alias were just "values" rather than "*VALUES*". The asterisks
suggest that the identifier is fake. But it's probably too late to do
anything about that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Jun 3, 2011 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
But anyway, there are basically two things we could do here: either
allow the table alias to be referenced, or try to teach ruleutils.c
not to qualify the column reference. �The second looks pretty tricky
and maybe not future-proof, so I'm leaning to the first. �Comments?
I think that makes sense, although it would less totally arbitrary if
the alias were just "values" rather than "*VALUES*". The asterisks
suggest that the identifier is fake. But it's probably too late to do
anything about that.
Hmm. Right now, since the identifier can't be referenced explicitly,
you could argue that a change might be painless. But if what we're
trying to accomplish is to allow existing view definitions of this form
to be dumped and restored, that wouldn't work. I'm inclined to leave
it alone.
regards, tom lane
On Fri, Jun 3, 2011 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Jun 3, 2011 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
But anyway, there are basically two things we could do here: either
allow the table alias to be referenced, or try to teach ruleutils.c
not to qualify the column reference. The second looks pretty tricky
and maybe not future-proof, so I'm leaning to the first. Comments?I think that makes sense, although it would less totally arbitrary if
the alias were just "values" rather than "*VALUES*". The asterisks
suggest that the identifier is fake. But it's probably too late to do
anything about that.Hmm. Right now, since the identifier can't be referenced explicitly,
you could argue that a change might be painless. But if what we're
trying to accomplish is to allow existing view definitions of this form
to be dumped and restored, that wouldn't work. I'm inclined to leave
it alone.
Yep. I think we're stuck with it at this point.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company