Re: View restore error in 9.3-9.4 upgrade

On Tue, Feb 24, 2015 at 10:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>    FROM process_list() pg_stat_activity(datid, datname, pid, usesysid,
> usename, application_name, client_addr, client_hostname, client_port,
> backend_start, xact_start, query_start, state_change, waiting, state, query)
>
> (that's not what you wrote originally, but ruleutils.c prints all the
> known column aliases not just what you wrote).  So everything up to
> "state" matches, but then "query" is assigned as the alias for
> "backend_xid", while "backend_xmin" and "query" remain unaliased.
> And now you have two columns named "query" in that FROM-item.
>

One quick-and-dirty way you could use to avoid the error would be to
complete the alias list to map the new columns backend_xid and
backend_xmin, like that:
   FROM process_list() pg_stat_activity(datid, datname, pid, usesysid,
usename, application_name, client_addr, client_hostname, client_port,
backend_start, xact_start, query_start, state_change, waiting, state,
backend_xid, backend_xmin, query)


> I'm not sure that there's anything very nice that pg_dump could do
> to deal with this.  Pending some brilliant idea on how we might avoid
> such problems, my own advice would be to not depend on system-defined
> rowtypes to define the outputs of user-defined functions.  If you
> made your own rowtype with the columns you care about, and had the
> function select just those columns not "select *", the code would
> be much more robust against cross-version changes.
>

Definitely. I don't think either that pg_dump should be made "smarter" to
deal with that.
--
Michael