Обсуждение: 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@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.tharakan@gmail.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@sss.pgh.pa.us> wrote:
Robins Tharakan <robins.tharakan@gmail.com> writes:Specifically, you mean that you had a column referenced by a USING
> 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).
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