Re: View restore error in 9.3-9.4 upgrade

On 2/23/15 8:43 PM, Tom Lane wrote:
> David Steele <david(at)pgmasters(dot)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.

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

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