Am 11.09.2013 21:02, schrieb David Johnston:
> Andreas-3-2 wrote
>> Hi,
>>
>> *I ran into a major problem when I tried to import a backup from 9.1.
>> into a 9.3 PG.*
>>
>> I just installed PG 9.3 on a new linux box.
>> Then I wanted to import a plaintext dump of a DB that was created by
>> pg_dump of PG 9.1
>>
>> There are a lot of views that have joins to a subquery in the from-clause.
>>
>> something like
>>
>> SELECT ... some columns ...
>> FROM
>> maintable AS m
>> JOIN someflag AS f ON m.flag_1_id = f.id
>> LEFT JOIN
>> (
>> child_table AS c
>> JOIN someotherflag AS f ON c.flag_2_id = f.id
>> ) AS x ON m.id = x.main_id
>>
>> This works with PG 9.1 and PG 9.2 but PG 9.3 complains:
>>
>> ERROR: table name "f" specified more than once
>>
>> *Are there no separate namespaces for subqueries anymore in PG 9.3 ?*
>>
>> Do I have to change ALL those views in the old PG 9.1 server before I
>> can import the backup into 9.3 or is there another way to work around
>> this issue ?
> Not running 9.3 yet so cannot test this myself. Ignore the pg_dump and
> view. Does (should) the above query work if executed in psql?
>
> David J.
>
No, it doesn't work in psql of PG 9.3. I got from EnterpriseDB
I did check like this:
Open pgAdmin 1.18 of PG 9.3
Open connections to both servers ... to the remote 9.1 and the 9.3 here
in my LAN.
Take the SQL definition of one of the problematic views out of the 9.1
server as pgAdmin displays it.
Paste this into a sql editor window of the 9.3 server and execute it.
The creation of the view is rejected.
When I try to run the query within the view directly PG 9.3 balks too.
It doesn't accept the reused alias within the subquery.
This internal alias references not even the same table as the one
outside the subquery.
It appeares as if there is just a global namespace for the whole query
that spans over the names within the subquery, too.
If this is the case then I can't switch to PG 9.3 at all, because I had
to reevaluate every query at my application throws at the DB and not
only those 70 views that get rejected while the initial import of the
sql dump.
