pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade failed if view contain natural left join condition
5+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2017-07-20 ed3dc224e5 5512427b19 315ca7ffb9 769eaef760 a6088e1f5a 80af818dd6 Doc: clarify description of degenerate NATURAL joins.
2017-07-20 eb145fdfea 6ab4a1136e c2bbec9f2b e947838ae4 41ada83774 Fix dumping of outer joins with empty qual lists.
* pg_upgrade failed if view contain natural left join condition
@ 2017-07-20 12:09 tushar <tushar.ahuja@enterprisedb.com>
2017-07-20 12:46 ` Re: pg_upgrade failed if view contain natural left join condition Thom Brown <thom@linux.com>
2017-07-20 13:53 ` Re: pg_upgrade failed if view contain natural left join condition Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: tushar @ 2017-07-20 12:09 UTC (permalink / raw)
To: pgsql-hackers
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_upgrade failed if view contain natural left join condition
2017-07-20 12:09 pg_upgrade failed if view contain natural left join condition tushar <tushar.ahuja@enterprisedb.com>
@ 2017-07-20 12:46 ` Thom Brown <thom@linux.com>
1 sibling, 0 replies; 5+ messages in thread
From: Thom Brown @ 2017-07-20 12:46 UTC (permalink / raw)
To: tushar <tushar.ahuja@enterprisedb.com>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_upgrade failed if view contain natural left join condition
2017-07-20 12:09 pg_upgrade failed if view contain natural left join condition tushar <tushar.ahuja@enterprisedb.com>
@ 2017-07-20 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-07-20 15:57 ` Re: pg_upgrade failed if view contain natural left join condition David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2017-07-20 13:53 UTC (permalink / raw)
To: tushar <tushar.ahuja@enterprisedb.com>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_upgrade failed if view contain natural left join condition
2017-07-20 12:09 pg_upgrade failed if view contain natural left join condition tushar <tushar.ahuja@enterprisedb.com>
2017-07-20 13:53 ` Re: pg_upgrade failed if view contain natural left join condition Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-07-20 15:57 ` David G. Johnston <david.g.johnston@gmail.com>
2017-07-20 16:14 ` Re: pg_upgrade failed if view contain natural left join condition Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2017-07-20 15:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: tushar <tushar.ahuja@enterprisedb.com>; 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_upgrade failed if view contain natural left join condition
2017-07-20 12:09 pg_upgrade failed if view contain natural left join condition tushar <tushar.ahuja@enterprisedb.com>
2017-07-20 13:53 ` Re: pg_upgrade failed if view contain natural left join condition Tom Lane <tgl@sss.pgh.pa.us>
2017-07-20 15:57 ` Re: pg_upgrade failed if view contain natural left join condition David G. Johnston <david.g.johnston@gmail.com>
@ 2017-07-20 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2017-07-20 16:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: tushar <tushar.ahuja@enterprisedb.com>; pgsql-hackers
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-07-20 16:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-20 12:09 pg_upgrade failed if view contain natural left join condition tushar <tushar.ahuja@enterprisedb.com>
2017-07-20 12:46 ` Thom Brown <thom@linux.com>
2017-07-20 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-07-20 15:57 ` David G. Johnston <david.g.johnston@gmail.com>
2017-07-20 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox