view dependent on system view caused an upgrade to fail

From: "Hu, Patricia" <Patricia(dot)Hu(at)finra(dot)org>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: view dependent on system view caused an upgrade to fail
Date: 2017-01-20 16:21:55
Message-ID: A22137031445794A99E795CDDB6BAF589E8FB24E@KWAWNEXMBP002.corp.root.nasd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have the following function and view in my db:

create or replace function ${catalogSchema}.fn_show_pg_stat_activity() returns setof pg_catalog.pg_stat_activity as $$ select * from pg_catalog.pg_stat_activity; $$ language sql volatile security definer;

create or replace view ${catalogSchema}.pg_stat_activity as select * from ${catalogSchema}.fn_show_pg_stat_activity();

During an upgrade from 9.5.4 to 9.6.1, the upgrade failed due to the error msg below: obviously the structure of pg_catalog.pg_stat_activity has changed between these 2 version.
pg_restore: creating VIEW "rcmmaster.pg_stat_activity" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 205; 1259 1995821 VIEW pg_stat_activity rcm_master_user pg_restore: [archiver (db)] could not execute query: ERROR: column reference "query" is ambiguous

My question: seems like pg_dump and pg_restore duirng the pg_upgrade actually used a hard-coded record/column set of the old verison of pg_stat_activity, instead of just recompiling the function then the view after the system catalog upgrade, so it would dynamically just retrieve the new column lists. Could this be considered a bug/defect? Is there any way to work around it w/o a postgresql fix?

Thanks,
Patricia

Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential or legally privileged information. If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the sender by replying to this message and permanently delete this e-mail, its attachments, and any copies of it immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-01-20 16:38:24 Re: view dependent on system view caused an upgrade to fail
Previous Message Tom Lane 2017-01-20 14:44:25 Re: Start/stop postgresql with pg_ctl or service without root access on RHEL