Re: View restore error in 9.3-9.4 upgrade - Mailing list pgsql-bugs

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 complete
> the migration.

> I've attached the relevant view and the function/views it depends on
> (view-bug.sql).

Hm.  Here's the problem:

CREATE FUNCTION process_list() RETURNS SETOF pg_stat_activity
    LANGUAGE plpgsql ...

In 9.3, the set of columns this returns is

regression=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  |
 state            | text                     |
 query            | text                     |

In 9.4, the set of columns this returns is

regression=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  |
 state            | text                     |
 backend_xid      | xid                      |    <=========
 backend_xmin     | xid                      |    <=========
 query            | text                     |

The two columns I marked are new in 9.4.  Now, you reference the result of
this function with this:

   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.

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.

            regards, tom lane