VIEW still referring to old name of field - Mailing list pgsql-general

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)