Re: View restore error in 9.3-9.4 upgrade - Mailing list pgsql-bugs
From | David Steele |
---|---|
Subject | Re: View restore error in 9.3-9.4 upgrade |
Date | |
Msg-id | 54EC0B63.1050306@pgmasters.net Whole thread Raw |
In response to | Re: View restore error in 9.3-9.4 upgrade (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-bugs |
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
pgsql-bugs by date: