[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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
[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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
Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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
[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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
[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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
Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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? droptable if exists sub_tab; droptable if exists main_tab; droptable if exists flag_1; droptable 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 frommain_tabas m joinflag_1 as f using ( flag_1_id ) left join ( sub_tab as s joinflag_2 as f2 using ( flag_2_id ) -- that way it works joinflag_1 as f using ( flag_1_id ) --joinflag_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
Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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? droptable if exists sub_tab; droptable if exists main_tab; droptable if exists flag_1; droptable 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 frommain_tabas m joinflag_1 as f using ( flag_1_id ) left join ( sub_tab as s joinflag_2 as f2 using ( flag_2_id ) -- that way it works joinflag_1 as f using ( flag_1_id ) --joinflag_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
[GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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 droptable if exists sub_tab; droptable if exists main_tab; droptable if exists flag_1; droptable 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 frommain_tabas m joinflag_1 as f using ( flag_1_id ) left join ( sub_tab as s joinflag_2 as f2 using ( flag_2_id ) -- that way it works joinflag_1 as f using ( flag_1_id ) --joinflag_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
Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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 * fromtblA jointblBon tblA.a_id = tblB.b_id join( tblB jointblCon 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 droptable if exists sub_tab; droptable if exists main_tab; droptable if exists flag_1; droptable 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 frommain_tabas m joinflag_1 as f using ( flag_1_id ) left join ( sub_tab as s joinflag_2 as f2 using ( flag_2_id ) -- that way it works joinflag_1 as f using ( flag_1_id ) --joinflag_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
Re: [GENERAL] Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2
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...@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 * fromtblA jointblBon tblA.a_id = tblB.b_id join( tblB jointblCon 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 droptable if exists sub_tab; droptable if exists main_tab; droptable if exists flag_1; droptable 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 frommain_tabas m joinflag_1 as f using ( flag_1_id ) left join ( sub_tab as s joinflag_2 as f2 using ( flag_2_id ) -- that way it works joinflag_1 as f using ( flag_1_id ) --joinflag_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.htmlhttp://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-generalhttp://www.postgresql.org/mailpref/pgsql-general -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
