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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 );

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 );

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Here is a minimal query that demonstrates the problem. In 9.1 it works:

chris=# select * FROM current_user u join (current_user u cross join
current_user v) x on true;
u | u | v
-------+-------+-------
chris | chris | chris
(1 row)

On 9.3 it fails:
ERROR: table name "u" specified more than once

It may be a silly example but it works.

On Wed, Sep 11, 2013 at 5:07 PM, Andreas <maps.on@gmx.net> wrote:

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<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&gt;
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general&lt;http://www.postgresql.org/mailpref/pgsql-general&gt;

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml