Horton

Thread

Commits

  1. Doc: clarify description of degenerate NATURAL joins.

  2. Fix dumping of outer joins with empty qual lists.

  1. pg_upgrade failed if view contain natural left join condition

    tushar <tushar.ahuja@enterprisedb.com> — 2017-07-20T12:09:43Z

    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

  2. Re: pg_upgrade failed if view contain natural left join condition

    Thom Brown <thom@linux.com> — 2017-07-20T12:46:51Z

    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

  3. Re: pg_upgrade failed if view contain natural left join condition

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-07-20T13:53:23Z

    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

  4. Re: pg_upgrade failed if view contain natural left join condition

    David G. Johnston <david.g.johnston@gmail.com> — 2017-07-20T15:57:37Z

    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.

  5. Re: pg_upgrade failed if view contain natural left join condition

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-07-20T16:14:06Z

    "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