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@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>=20
>        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)
>=20
>     (that's not what you wrote originally, but ruleutils.c prints all t=
he
>     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.
>=20
>=20
> 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.

--=20
- David Steele
david@pgmasters.net