Skip site navigation (1) Skip section navigation (2)

VIEW still referring to old name of field

From: Robins Tharakan <robins(dot)tharakan(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: VIEW still referring to old name of field
Date: 2012-02-09 13:46:24
Message-ID: CACk=U9NFSzWrEba8G5dZ=TZLy3_hx3QXGyCcKVWT=4iA1FjMuA@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(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)

Responses

pgsql-general by date

Next:From: Marti RaudseppDate: 2012-02-09 14:00:14
Subject: Re: SOUNDEX call
Previous:From: Achilleas MantziosDate: 2012-02-09 08:57:09
Subject: Re: phantom long-running query (check_postgres.pl reports a long-running query, but the database logs don't show it)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group