Re: View restore error in 9.3-9.4 upgrade

On Tue, Feb 24, 2015 at 10:02 AM, David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)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