VIEW still referring to old name of field

Hi,

This is a case where I changed the name of a field in a table that a VIEW
referred to, but the VIEW definition still points to the old name of the
field. The surprise is that the VIEW still works (with live data).

Excerpt from psql (v9.1.2) given below.

The problem came up when I took a schema dump and restored this on another
server which crashed on this VIEW since now the field referred by the VIEW
isn't there any more. The same error is shown if (on this server) I copy
the VIEW definition and execute directly (which is expected, since the
field name is incorrect in the definition).

I think an easy way to solve this is to do a drop/create for the VIEW, but
I think this is a bug and its better if found / resolved, if possible. Let
me know if I can provide any more input on this. I have this server for
another day or two, and then do let me know if running a command on this
server may help.

Further, (I am unsure here) but I believe the field name was changed ~1-2
weeks back and the server was restarted just the day before. Is it possible
that this survives a restart as well?

Thanks
--
Robins Tharakan
==================

[pgsql(at)server /webstats/pgsql]$ psql
psql (9.1.2)
Type "help" for help.

pgsql=# \c aproject
You are now connected to database "aproject" as user "pgsql".

aproject=# \d ui.thisview
View "ui.thisview"
Column | Type | Modifiers
--------------------------+------------------------+-----------
pid | integer |
product_name | character varying(100) |
product_usage | bigint |
product_usage_percentage | numeric(10,2) |

aproject=# select * from pg_views where viewname = 'thisview';
schemaname | viewname | viewowner |

definition

------------+----------------------------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ui | thisview | pgsql | WITH x AS (SELECT session.pid,
count(*) AS product_usage FROM ((s.history JOIN s.session USING
(session_id)) JOIN s.product USING (pid)) WHERE (((history.datetime_entry
>= (now() - '90 days'::interval)) AND (history.datetime_entry <= now()))
AND (session.pid IS NOT NULL)) GROUP BY session.pid) SELECT x.pid,
product.product_name, x.product_usage, ((((x.product_usage * 100))::numeric
/ (SELECT sum(x.product_usage) AS count FROM x)))::numeric(10,2) AS
product_usage_percentage FROM (x JOIN s.product USING (pid));
(1 row)

aproject=# select * from ui.thisview limit 1;
pid | product_name | product_usage | product_usage_percentage
-----+-----------------+---------------+--------------------------
14 | Unknown Product | 700 | 2.02
(1 row)

aproject=# WITH x AS (SELECT session.pid, count(*) AS product_usage FROM
((s.history JOIN s.session USING (session_id)) JOIN s.product USING (pid))
WHERE (((history.datetime_entry >= (now() - '90 days'::interval)) AND
(history.datetime_entry <= now())) AND (session.pid IS NOT NULL)) GROUP BY
session.pid) SELECT x.pid, product.product_name, x.product_usage,
((((x.product_usage * 100))::numeric / (SELECT sum(x.product_usage) AS
count FROM x)))::numeric(10,2) AS product_usage_percentage FROM (x JOIN
s.product USING (pid));
ERROR: column "pid" specified in USING clause does not exist in right table

aproject=# \d s.product
Table "s.product"
Column | Type | Modifiers
--------------+------------------------+------------------------------------------------------------
product_id | integer | not null default
nextval('s.product_id_seq'::regclass)
product_name | character varying(100) | not null
Indexes:
"product_pkey" PRIMARY KEY, btree (product_id)
Referenced by:
TABLE "s.session" CONSTRAINT "fk_product_id" FOREIGN KEY (pid)
REFERENCES s.product(product_id)

Robins Tharakan <robins(dot)tharakan(at)gmail(dot)com> writes:
> This is a case where I changed the name of a field in a table that a VIEW
> referred to, but the VIEW definition still points to the old name of the
> field. The surprise is that the VIEW still works (with live data).

Specifically, you mean that you had a column referenced by a USING
clause, and then you renamed it, right? We've had discussions about
that in the past, and concluded that the SQL spec is just fundamentally
broken here. If you rename one of the input columns, there is no way
to represent a view that (used to) use USING without changing the view's
behavior -- in particular, the set of columns exposed by a join with
USING is different from the set of columns exposed without that, so
simply replacing the USING with an ON clause wouldn't get the job done.
So our view-dumping code just doesn't bother to try. You'll get
something that still says USING, but of course this won't work when the
view definition is dumped and reloaded. This is not the fault of the
view: if you'd not used a view but just issued the equivalent join
directly as a SQL query, the rename would still have broken your
application.

Our internal representation doesn't depend on the name-matching aspect
of USING, so the view continues to work as before, so long as you don't
dump and reload. But it looks wrong if you dump the definition as SQL.
That's basically because SQL lacks a way to represent the situation.

The best idea I've heard for fixing it is to invent a non-standard
syntax that could represent a USING clause matching two dissimilarly
named columns, say USING (foo = bar AS baz), and then use that syntax
when dumping a view if the column names don't match. Nobody's worked
out the idea in full detail, though, let alone implemented it; it's not
really clear it's worth the trouble.

regards, tom lane

Thanks Tom.

I get it. Putting it in another way, if there was a function and a VIEW and
this field name were to be changed, then we'd have a broken function
anyway.

The only issue is that (before writing this mail) I expected that a VIEW
would either throw up errors or would work without fail regardless of a
dump / reload scenario (and yes likewise, I expected a function to not
exhibit that behaviour). So I actually did a search for this field in the
functions, and relied on PG to throw up errors for a VIEW. Probably I got
stumped there.

But that aside, the only question left here is that if a token is not
exposed by a VIEW, would not an automatic search / replace have done the
job ? Theoretically speaking, having a known case where a VIEW's definition
not working whereas the VIEW working is flawed (frankly worrying, now to
think of it).

Thanks nonetheless. Guess I need some daily dump/reload scripts for all
projects right away.
--
Robins Tharakan

On Thu, Feb 9, 2012 at 9:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robins Tharakan <robins(dot)tharakan(at)gmail(dot)com> writes:
> > This is a case where I changed the name of a field in a table that a VIEW
> > referred to, but the VIEW definition still points to the old name of the
> > field. The surprise is that the VIEW still works (with live data).
>
> Specifically, you mean that you had a column referenced by a USING
> clause, and then you renamed it, right? We've had discussions about
> that in the past, and concluded that the SQL spec is just fundamentally
> broken here. If you rename one of the input columns, there is no way
> to represent a view that (used to) use USING without changing the view's
> behavior -- in particular, the set of columns exposed by a join with
> USING is different from the set of columns exposed without that, so
> simply replacing the USING with an ON clause wouldn't get the job done.
> So our view-dumping code just doesn't bother to try. You'll get
> something that still says USING, but of course this won't work when the
> view definition is dumped and reloaded. This is not the fault of the
> view: if you'd not used a view but just issued the equivalent join
> directly as a SQL query, the rename would still have broken your
> application.
>
> Our internal representation doesn't depend on the name-matching aspect
> of USING, so the view continues to work as before, so long as you don't
> dump and reload. But it looks wrong if you dump the definition as SQL.
> That's basically because SQL lacks a way to represent the situation.
>
> The best idea I've heard for fixing it is to invent a non-standard
> syntax that could represent a USING clause matching two dissimilarly
> named columns, say USING (foo = bar AS baz), and then use that syntax
> when dumping a view if the column names don't match. Nobody's worked
> out the idea in full detail, though, let alone implemented it; it's not
> really clear it's worth the trouble.
>
> regards, tom lane
>