Обсуждение: [GENERAL] view dependent on system view caused an upgrade to fail

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_activityhas 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
theold 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
wayto work around it w/o a postgresql fix?   

Thanks,
Patricia


Confidentiality Notice::  This email, including attachments, may include non-public, proprietary, confidential or
legallyprivileged information.  If you are not an intended recipient or an authorized agent of an intended recipient,
youare hereby notified that any dissemination, distribution or copying of the information contained in or transmitted
withthis e-mail is unauthorized and strictly prohibited.  If you have received this email in error, please notify the
senderby 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
partof the contents to any other person. Thank you. 

"Hu, Patricia" <Patricia.Hu@finra.org> writes:
> 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_activityhas changed between these 2 version.  

Yeah.

> 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?

By our reading, this behavior is required by the SQL standard.
Your view wasn't saved as "SELECT * FROM ...", it was saved as
"SELECT datid, datname, ... FROM ..." because the standard says to
do it that way.  On the other hand, the rowtype of the function
result *did* change in the upgrade.  So then you had a problem
with the view definition referring to columns that don't exist
in the function output.

Even if the view had, contrary to spec, acted like "SELECT *",
that would just have moved the problem up one level to whatever
depends on the view.  No doubt this is why the SQL committee
said to make it work like that --- at least it confines the
problem to the particular view.

> Is there any way to work around it w/o a postgresql fix?

You can't really get around the fact that if your application
looks at system catalog or view columns that change, your
application is going to need changes too.

            regards, tom lane