Обсуждение: [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
On 20 July 2017 at 13:09, tushar <tushar.ahuja@enterprisedb.com> wrote: > 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
tushar <tushar.ahuja@enterprisedb.com> 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. > 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
Re: [HACKERS] pg_upgrade failed if view contain natural left join condition
От
"David G. Johnston"
Дата:
tushar <tushar.ahuja@enterprisedb.com> 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.
"David G. Johnston" <david.g.johnston@gmail.com> 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