Thread: 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