Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
"David G. Johnston"writes: > Per the docs: > "If there are no common column names, NATURAL behaves like CROSS JOIN." > I'm being a bit pedantic here but since NATURAL is a replacement for > "ON/USING" it would seem more consistent to describe it, when no matching > columns are found, as "behaves like specifying ON TRUE" instead. Yeah, the analogy to CROSS JOIN falls down if it's an outer join. I'll go fix that. > I find it a bit strange, though not surprising, that it doesn't devolve to > "ON FALSE". No, it's normal that an AND of no conditions degenerates to TRUE. It's like omitting a WHERE clause. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
On Thu, Jul 20, 2017 at 6:53 AM, Tom Lanewrote: > tushar writes: > > postgres=# create table t(n int); > > CREATE TABLE > > postgres=# create table t1(a int); > > CREATE TABLE > > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 > d; > > CREATE VIEW > > You realize of course that that's a pretty useless join definition. > Still, yes, we do need to reverse-list the view with correct syntax. > Probably t LEFT JOIN t1 ON TRUE would do it. > Per the docs: "If there are no common column names, NATURAL behaves like CROSS JOIN." I'm being a bit pedantic here but since NATURAL is a replacement for "ON/USING" it would seem more consistent to describe it, when no matching columns are found, as "behaves like specifying ON TRUE" instead. Maybe "behaves like specifying ON TRUE, causing a CROSS JOIN to occur instead." I find it a bit strange, though not surprising, that it doesn't devolve to "ON FALSE". David J.
Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
tusharwrites: > postgres=# create table t(n int); > CREATE TABLE > postgres=# create table t1(a int); > CREATE TABLE > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; > CREATE VIEW You realize of course that that's a pretty useless join definition. Still, yes, we do need to reverse-list the view with correct syntax. Probably t LEFT JOIN t1 ON TRUE would do it. > I think -this issue should be there in the older branches as well but > not checked that. [experiments] Seems to be wrong back to 9.3. Although I have a feeling this might be a mistake in a back-patched bug fix, so that it'd depend on which 9.3.x you looked at. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
On 20 July 2017 at 13:09, tusharwrote: > Steps to reproduce - > > v9.6 > > postgres=# create table t(n int); > CREATE TABLE > postgres=# create table t1(a int); > CREATE TABLE > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; > CREATE VIEW > > v10 - > > run pg_upgrade - > > going to fail ,with this error - > > " > pg_restore: creating TABLE "public.t" > pg_restore: creating TABLE "public.t1" > pg_restore: creating VIEW "public.ttt1" > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 187; 1259 16390 VIEW ttt1 > edb > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at > or near ")" > LINE 16: LEFT JOIN "t1" "d"); > ^ > Command was: > -- For binary upgrade, must preserve pg_type oid > SELECT > pg_catalog.binary_upgrade_set_next_pg_type_oid('16392'::pg_catalog.oid); > > > -- For binary upgrade, must preserve pg_type array oid > SELECT > pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16391'::pg_catalog.oid); > > > -- For binary upgrade, must preserve pg_class oids > SELECT > pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16390'::pg_catalog.oid); > > CREATE VIEW "ttt1" AS > SELECT "e"."n" >FROM ("t" "e" > LEFT JOIN "t1" "d"); > > " > I think -this issue should be there in the older branches as well but not > checked that. I get the same result on 9.2 and 10 in pg_dump output. Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade failed if view contain natural left join condition
Steps to reproduce - v9.6 postgres=# create table t(n int); CREATE TABLE postgres=# create table t1(a int); CREATE TABLE postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1 d; CREATE VIEW v10 - run pg_upgrade - going to fail ,with this error - " pg_restore: creating TABLE "public.t" pg_restore: creating TABLE "public.t1" pg_restore: creating VIEW "public.ttt1" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 187; 1259 16390 VIEW ttt1 edb pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near ")" LINE 16: LEFT JOIN "t1" "d"); ^ Command was: -- For binary upgrade, must preserve pg_type oid SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('16392'::pg_catalog.oid); -- For binary upgrade, must preserve pg_type array oid SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16391'::pg_catalog.oid); -- For binary upgrade, must preserve pg_class oids SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16390'::pg_catalog.oid); CREATE VIEW "ttt1" AS SELECT "e"."n" FROM ("t" "e" LEFT JOIN "t1" "d"); " I think -this issue should be there in the older branches as well but not checked that. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers