Re: View restore error in 9.3-9.4 upgrade

On 2/23/15 8:57 PM, Michael Paquier wrote:
> On Tue, Feb 24, 2015 at 10:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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)

Sure - that would definitely work. Not too much fun to change on a
multi-gig file, though.

I'll just chalk this one up to a lesson learned - never use system row
types as they are subject to change.

--
- David Steele
david(at)pgmasters(dot)net