Обсуждение: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

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 ?

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.klaver@gmail.com

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.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770510.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Adrian Klaver-3 wrote
> 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.

Maybe; but the supplied query does not seem to be ambiguous and the dump
phase has already completed.  pg_restore simply issues a CREATE VIEW and
does not perform interpolation of the contents.  If the select statement is
indeed correctly represented then I could very well see creating one like
that by hand and inserting it as part of an external database schema
installation (i.e., not via pg_restore) and would expect it to work.
According to this such a scenario should also fail with the same message.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770512.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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.

Andreas-3-2 wrote
> No, it doesn't work in psql of PG 9.3. I got from EnterpriseDB

Can you please create a minimal self-contained query that exhibits this
behavior and file a bug report?

I quickly cobbled this together - works on 9.0.x

WITH
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-a'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA AS dup_tbl JOIN tblB ON (dup_tbl.a_id = tblB.b_id)
) AS refD
JOIN tblC AS dup_tbl ON (dup_tbl.c_id = refD.a_id)

If it fails on 9.3 it would do.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770519.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Try these too, please:

WITH
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA JOIN tblB ON (tblA.a_id = tblB.b_id)
) AS refD
JOIN tblA ON (tblA.a_id = refD.a_id)
;

WITH
  tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )
SELECT *
FROM (
tblA JOIN tblB ON (tblA.a_id = tblB.b_id)
) AS refD
JOIN tblA ON (tblA.a_id = tblB.a_id)
; --this one should fail trying to reference tblB


The reference to tblA is used inside refD and outside of it as well; but no
table aliases are used.

Thanks

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770520.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi David,
your 3 examples work as you expected. That is 1+2 work and 3 throws an
error.

I tried to figure out an example and found something peculiar.
The issue arises when there is another join in the subquery after the
one with the reused table alias.
There is no error without this following join.

Look at this rather chunky sample.
If I join flag_2 before flag_1 it works.
It won't with flag_2 after flag_1.

The query works as soon as the reused alias joins last in the subquery.
If there are 2 reused aliases then the query wont work at all without
renaming one alias.

The error shows with pgAdmin aws well as psql (9.3).

So probaply it's not an namespace issue but the query-parser screws up.   :(

I never placed a bug-report.      :}
Could you give a hand?


drop    table if exists sub_tab;
drop    table if exists main_tab;
drop    table if exists flag_1;
drop    table if exists flag_2;

create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
text );
insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
text );
insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );

create  temporary table main_tab ( main_id integer primary key, main_t
text, flag_1_id integer references flag_1 ( flag_1_id ) );
insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
'Main 3', 3 );

create  temporary table sub_tab ( sub_id integer primary key, sub_t
text, main_id integer references main_tab ( main_id ), flag_1_id integer
references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
flag_2_id ) );
insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
2 ), ( 3, 'Sub 3', 3, 1, 3 );


select  m.main_id, m.main_t, f.flag_1_t,
         x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
from        main_tab    as  m
     join    flag_1      as  f   using   ( flag_1_id )
     left join
     (
                 sub_tab as  s
         join    flag_2  as  f2  using   ( flag_2_id )           -- that
way it works
         join    flag_1  as  f   using   ( flag_1_id )
--        join    flag_2  as  f2  using   ( flag_2_id )         -- that
way it doesn't work
     )                   as  x   using   ( main_id );

Just another addition...

If I remove the aliases for the tbles flag_1 and flag_2 the problem
still comes up.

So one either has to mind the order of the joins or use unique aliases.

It's really an issue as there are bound to be some queries in
sql-functions or some that get assembled dynamically in my application.
Those won't get executed in the initial import of the db-dump but will
come up anytime later when the query gets used the first time.

This is a no go    :(


regards
Andreas


Am 12.09.2013 00:33, schrieb Andreas:
> Hi David,
> your 3 examples work as you expected. That is 1+2 work and 3 throws an
> error.
>
> I tried to figure out an example and found something peculiar.
> The issue arises when there is another join in the subquery after the
> one with the reused table alias.
> There is no error without this following join.
>
> Look at this rather chunky sample.
> If I join flag_2 before flag_1 it works.
> It won't with flag_2 after flag_1.
>
> The query works as soon as the reused alias joins last in the subquery.
> If there are 2 reused aliases then the query wont work at all without
> renaming one alias.
>
> The error shows with pgAdmin aws well as psql (9.3).
>
> So probaply it's not an namespace issue but the query-parser screws
> up.   :(
>
> I never placed a bug-report.      :}
> Could you give a hand?
>
>
> drop    table if exists sub_tab;
> drop    table if exists main_tab;
> drop    table if exists flag_1;
> drop    table if exists flag_2;
>
> create  temporary table flag_1 ( flag_1_id integer primary key,
> flag_1_t text );
> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>
> create  temporary table flag_2 ( flag_2_id integer primary key,
> flag_2_t text );
> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>
> create  temporary table main_tab ( main_id integer primary key, main_t
> text, flag_1_id integer references flag_1 ( flag_1_id ) );
> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), (
> 3, 'Main 3', 3 );
>
> create  temporary table sub_tab ( sub_id integer primary key, sub_t
> text, main_id integer references main_tab ( main_id ), flag_1_id
> integer references flag_1 ( flag_1_id ), flag_2_id integer references
> flag_2 ( flag_2_id ) );
> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2,
> 2, 2 ), ( 3, 'Sub 3', 3, 1, 3 );
>
>
> select  m.main_id, m.main_t, f.flag_1_t,
>         x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
> from        main_tab    as  m
>     join    flag_1      as  f   using   ( flag_1_id )
>     left join
>     (
>                 sub_tab as  s
>         join    flag_2  as  f2  using   ( flag_2_id )           --
> that way it works
>         join    flag_1  as  f   using   ( flag_1_id )
> --        join    flag_2  as  f2  using   ( flag_2_id )         --
> that way it doesn't work
>     )                   as  x   using   ( main_id );
>
>
>

Andreas-3-2 wrote
> I never placed a bug-report.      :}
> Could you give a hand?

Sure.

PostgreSQL homepage (postgresql.org)
"Report a Bug" link on right-hand side of page.
Fill in the form.

>>>SEND THIS>>>>


> drop    table if exists sub_tab;
> drop    table if exists main_tab;
> drop    table if exists flag_1;
> drop    table if exists flag_2;
>
> create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
> text );
> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>
> create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
> text );
> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>
> create  temporary table main_tab ( main_id integer primary key, main_t
> text, flag_1_id integer references flag_1 ( flag_1_id ) );
> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
> 'Main 3', 3 );
>
> create  temporary table sub_tab ( sub_id integer primary key, sub_t
> text, main_id integer references main_tab ( main_id ), flag_1_id integer
> references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
> flag_2_id ) );
> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
> 2 ), ( 3, 'Sub 3', 3, 1, 3 );
>
>
> select  m.main_id, m.main_t, f.flag_1_t,
>          x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
> from        main_tab    as  m
>      join    flag_1      as  f   using   ( flag_1_id )
>      left join
>      (
>                  sub_tab as  s
>          join    flag_2  as  f2  using   ( flag_2_id )           -- that
> way it works
>          join    flag_1  as  f   using   ( flag_1_id )
> --        join    flag_2  as  f2  using   ( flag_2_id )         -- that
> way it doesn't work
>      )                   as  x   using   ( main_id );





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I took your sample and modified it a bit.

ERROR:  table name "tblb" specified more than once
Switch tblB and tblC in the subquery and it works.

WITH
   tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) )
, tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) )
, tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) )

select  *
from        tblA
     join    tblB    on  tblA.a_id = tblB.b_id
     join    (
                         tblB
                 join    tblC    on  tblC.c_id = tblB.b_id
             )   as  x           on  tblA.a_id = x.c_id;




Am 12.09.2013 00:52, schrieb David Johnston:
> Andreas-3-2 wrote
>> I never placed a bug-report.      :}
>> Could you give a hand?
> Sure.
>
> PostgreSQL homepage (postgresql.org)
> "Report a Bug" link on right-hand side of page.
> Fill in the form.
>
>>>> SEND THIS>>>>
>
>> drop    table if exists sub_tab;
>> drop    table if exists main_tab;
>> drop    table if exists flag_1;
>> drop    table if exists flag_2;
>>
>> create  temporary table flag_1 ( flag_1_id integer primary key, flag_1_t
>> text );
>> insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>
>> create  temporary table flag_2 ( flag_2_id integer primary key, flag_2_t
>> text );
>> insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' );
>>
>> create  temporary table main_tab ( main_id integer primary key, main_t
>> text, flag_1_id integer references flag_1 ( flag_1_id ) );
>> insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3,
>> 'Main 3', 3 );
>>
>> create  temporary table sub_tab ( sub_id integer primary key, sub_t
>> text, main_id integer references main_tab ( main_id ), flag_1_id integer
>> references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 (
>> flag_2_id ) );
>> insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2,
>> 2 ), ( 3, 'Sub 3', 3, 1, 3 );
>>
>>
>> select  m.main_id, m.main_t, f.flag_1_t,
>>           x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t
>> from        main_tab    as  m
>>       join    flag_1      as  f   using   ( flag_1_id )
>>       left join
>>       (
>>                   sub_tab as  s
>>           join    flag_2  as  f2  using   ( flag_2_id )           -- that
>> way it works
>>           join    flag_1  as  f   using   ( flag_1_id )
>> --        join    flag_2  as  f2  using   ( flag_2_id )         -- that
>> way it doesn't work
>>       )                   as  x   using   ( main_id );
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>