From: | David Steele <david(at)pgmasters(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | View restore error in 9.3-9.4 upgrade |
Date: | 2015-02-23 22:26:36 |
Message-ID: | 54EBA91C.1020907@pgmasters.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)pgmasters(dot)net
Attachment | Content-Type | Size |
---|---|---|
view-bug.sql | text/plain | 3.9 KB |
test.dump | text/plain | 5.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2015-02-24 00:55:43 | Re: View restore error in 9.3-9.4 upgrade |
Previous Message | Francisco Olarte | 2015-02-23 17:59:15 | Re: BUG #12785: server process (PID 2872) was terminated by exception 0xC0000005 |