Обсуждение: View restore error in 9.3-9.4 upgrade
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). To reproduce the issue: On a 9.3 cluster: > createdb test > psql -f view-bug.sql test > pg_dump test > test.dump Then on a 9.4 cluster: > createdb test > psql -f test.dump test The import fails with this error: psql:test.dump:151: ERROR: column reference "query" is ambiguous LINE 13: pg_stat_activity.query, view-bug.sql runs and works fine directly on 9.4. If I then dump and restore using only 9.4 that also works. It occurred to me that I was aliasing the result of a function to "pg_stat_activity", then later joining to the pg_stat_activity view (which is aliased to something else). Changing the alias for the function did not work though - I got the same error (with a different alias of course). I've also attached the dump from 9.3 (test.dump) that errors in case this is somehow environmental, though I can reproduce it on OSX and Linux with very different settings. I searched the 9.4 release notes and noticed that two columns were added to pg_stat_activity, but not sure how that would produce this error. I'll poke into this myself as time allows, but I'm hoping it will ring a bell for somebody else. -- - David Steele david@pgmasters.net
Вложения
David,
* David Steele (david@pgmasters.net) wrote:
> 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.
Which pg_dumpall / pg_dump did you use? Generally speaking, you want to
be using the pg_dumpall and pg_dump from the version of PostgreSQL which
you are upgrading *to*. In this case, you'd want to use the 9.4
pg_dumpall and pg_dump.
I haven't had a chance to look more closely, but I suspect that would
work based on your comment that a dump/reload while on 9.4 works.
Thanks!
Stephen
David Steele wrote > 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). To reproduce the issue: > > On a 9.3 cluster: > >> createdb test >> psql -f view-bug.sql test >> pg_dump test > test.dump Is the version of pg_dump 9.3 here? When upgrading to a new release you should use the newer (in this case 9.4) version of pg_dump to perform the migration dump. What are the exact releases of 9.3 and 9.4 upon which you are operating? Does the dump restore into 9.3 correctly? > psql:test.dump:151: ERROR: column reference "query" is ambiguous > LINE 13: pg_stat_activity.query, A quick visual doesn't spring anything obvious. I haven't attempted to reproduce via the supplied scripts. David J. -- View this message in context: http://postgresql.nabble.com/View-restore-error-in-9-3-9-4-upgrade-tp5839120p5839131.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
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
On Tue, Feb 24, 2015 at 10:02 AM, David G Johnston < david.g.johnston@gmail.com> wrote: > David Steele wrote > > 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). To reproduce the issue: > > > > On a 9.3 cluster: > > > >> createdb test > >> psql -f view-bug.sql test > >> pg_dump test > test.dump > > Is the version of pg_dump 9.3 here? When upgrading to a new release you > should use the newer (in this case 9.4) version of pg_dump to perform the > migration dump. > > What are the exact releases of 9.3 and 9.4 upon which you are operating? > > Does the dump restore into 9.3 correctly? > > > > psql:test.dump:151: ERROR: column reference "query" is ambiguous > > LINE 13: pg_stat_activity.query, > > A quick visual doesn't spring anything obvious. I haven't attempted to > reproduce via the supplied scripts. > FWIW I have just run a test and when after loading view-test.sql in a 9.3 cluster, then dump it with pg_dump >= 9.4, reloading the dump does fail on a newer cluster as Steeve mentions. -- Michael
On Tue, Feb 24, 2015 at 10:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. > 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) > 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. > Definitely. I don't think either that pg_dump should be made "smarter" to deal with that. -- Michael
On 2/23/15 7:55 PM, Stephen Frost wrote: > David, >=20 > * David Steele (david@pgmasters.net) wrote: >> 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 > Which pg_dumpall / pg_dump did you use? Generally speaking, you want t= o > be using the pg_dumpall and pg_dump from the version of PostgreSQL whic= h > you are upgrading *to*. In this case, you'd want to use the 9.4 > pg_dumpall and pg_dump. During the actual migration I used the 9.4 pg_dump, but for my tests I used 9.3. I just redid the tests exporting with 9.4 and it failed in the same way. --=20 - David Steele david@pgmasters.net
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
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