pg_upgrade failed if view contain natural left join condition

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

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

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

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

On Thu, Jul 20, 2017 at 6:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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