Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

On 09/11/2013 09:53 AM, Andreas 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 ?

My guess you are seeing this:

http://www.postgresql.org/docs/9.3/interactive/release-9-3.html

Improve view/rule printing code to handle cases where referenced tables
are renamed, or columns are renamed, added, or dropped (Tom Lane)

Table and column renamings can produce cases where, if we merely
substitute the new name into the original text of a rule or view, the
result is ambiguous. This change fixes the rule-dumping code to insert
manufactured table and column aliases when needed to preserve the
original semantics.

You would be advised to use the 9.3 version of pg_dump to dump the 9.1
database.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com