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