Re: View restore error in 9.3-9.4 upgrade

On 2/23/15 8:43 PM, Tom Lane wrote:
> David Steele <david@pgmasters.net> writes:
>> I upgraded from 9.3 to 9.4 last week using pg_dumpall and psql to dump=

>> and import the database.  I got an error during the creation of one of=

>> the views and ended up having to patch the pl/pgsql manually to comple=
te
>> the migration.
>=20
>> I've attached the relevant view and the function/views it depends on
>> (view-bug.sql).
>=20
> Hm.  Here's the problem:=20
>=20
> CREATE FUNCTION process_list() RETURNS SETOF pg_stat_activity
>     LANGUAGE plpgsql ...
>=20
> In 9.3, the set of columns this returns is
>=20
> regression=3D# \d pg_stat_activity=20
>            View "pg_catalog.pg_stat_activity"
>       Column      |           Type           | Modifiers=20
> ------------------+--------------------------+-----------
>  datid            | oid                      |=20
>  datname          | name                     |=20
>  pid              | integer                  |=20
>  usesysid         | oid                      |=20
>  usename          | name                     |=20
>  application_name | text                     |=20
>  client_addr      | inet                     |=20
>  client_hostname  | text                     |=20
>  client_port      | integer                  |=20
>  backend_start    | timestamp with time zone |=20
>  xact_start       | timestamp with time zone |=20
>  query_start      | timestamp with time zone |=20
>  state_change     | timestamp with time zone |=20
>  waiting          | boolean                  |=20
>  state            | text                     |=20
>  query            | text                     |=20
>=20
> In 9.4, the set of columns this returns is
>=20
> regression=3D# \d pg_stat_activity=20
>            View "pg_catalog.pg_stat_activity"
>       Column      |           Type           | Modifiers=20
> ------------------+--------------------------+-----------
>  datid            | oid                      |=20
>  datname          | name                     |=20
>  pid              | integer                  |=20
>  usesysid         | oid                      |=20
>  usename          | name                     |=20
>  application_name | text                     |=20
>  client_addr      | inet                     |=20
>  client_hostname  | text                     |=20
>  client_port      | integer                  |=20
>  backend_start    | timestamp with time zone |=20
>  xact_start       | timestamp with time zone |=20
>  query_start      | timestamp with time zone |=20
>  state_change     | timestamp with time zone |=20
>  waiting          | boolean                  |=20
>  state            | text                     |=20
>  backend_xid      | xid                      |    <=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>  backend_xmin     | xid                      |    <=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>  query            | text                     |=20
>=20
> The two columns I marked are new in 9.4.  Now, you reference the result=
 of
> this function with this:
>=20
>    FROM process_list() pg_stat_activity(datid, datname, pid, usesysid, =
usename, application_name, client_addr, client_hostname, client_port, bac=
kend_start, xact_start, query_start, state_change, waiting, state, query)=

>=20
> (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.
>=20
> 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.

Fair enough.  The irony is that I used the row type because I thought it
would more portable to future versions.  I got burned a few years ago by
a column name change in pg_stat_activity (procid =3D> pid IIRC).

The further irony is that I did this upgrade using pg_dump because the
database is only a few GB and I prefer to use dump/restore when
practical rather that pg_upgrade.

Bruce is laughing right now.

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