Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id C263379D55 for ; Thu, 12 Jul 2012 07:05:56 -0300 (ADT) Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SpGHS-0000dI-R4 for pgsql-hackers@postgresql.org; Thu, 12 Jul 2012 10:05:56 +0000 Received: from mfs5.rdh.ecl.ntt.co.jp (mfs5.rdh.ecl.ntt.co.jp [129.60.39.144]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id q6CA5cHP006171; Thu, 12 Jul 2012 19:05:38 +0900 Received: from mfs5.rdh.ecl.ntt.co.jp (localhost.localdomain [127.0.0.1]) by mfs5.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id 0747FE00BD; Thu, 12 Jul 2012 19:05:38 +0900 (JST) Received: from dmailsv1.y.ecl.ntt.co.jp (dmailsv1.y.ecl.ntt.co.jp [129.60.53.14]) by mfs5.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id EF13DE00BB; Thu, 12 Jul 2012 19:05:37 +0900 (JST) Received: from mailsv11.y.ecl.ntt.co.jp by dmailsv1.y.ecl.ntt.co.jp (8.14.5/dmailsv1-2.1) with ESMTP id q6CA5bP2027363; Thu, 12 Jul 2012 19:05:37 +0900 (JST) Received: from localhost by mailsv11.y.ecl.ntt.co.jp (8.14.5/Lab-1.9) with ESMTP id q6CA5bsG014471; Thu, 12 Jul 2012 19:05:37 +0900 (JST) From: "Etsuro Fujita" To: "'Kohei KaiGai'" , "'Shigeru HANADA'" Cc: "'PostgreSQL-development'" References: <4FD9D947.1020803@gmail.com> In-Reply-To: Subject: Re: pgsql_fdw in contrib Date: Thu, 12 Jul 2012 19:10:40 +0900 Message-ID: <002301cd6016$971671f0$c54355d0$@lab.ntt.co.jp> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0024_01CD6062.070063E0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQJ24NQWutH15ElQlYtp4yJvebFgtwGTFppjAhoTPw+VtP1y8A== Content-Language: ja X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/543 X-Sequence-Number: 211226 This is a multipart message in MIME format. ------=_NextPart_000_0024_01CD6062.070063E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit > 2012/6/26 Kohei KaiGai : > > Harada-san, > > > > I checked your patch, and had an impression that includes many > > improvements from the previous revision that I looked at the last > > commit fest. > > > > However, I noticed several points to be revised, or investigated. > > > > * It seems to me expected results of the regression test is not > > attached, even though test cases were included. Please add it. KaiGai-san, Did you find the file? That is in the contrib/pgsql_fdw/expected directory, named pgsql_fdw.out. If necessary, I will send the file to you. BTW, I found some bugs on the expected results of the file. Attached is a patch fixing the bugs. Thanks, Best regards, Etsuro Fujita ------=_NextPart_000_0024_01CD6062.070063E0 Content-Type: application/octet-stream; name="pgsql_fdw_regress.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="pgsql_fdw_regress.patch" *** pgsql_fdw.out.orig 2012-07-12 18:01:49.000000000 +0900=0A= --- pgsql_fdw.out 2012-07-12 18:11:15.000000000 +0900=0A= ***************=0A= *** 445,464 ****=0A= -- subquery using stable function (can't be pushed down)=0A= PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN = (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) =3D 6) = ORDER BY c1;=0A= EXPLAIN (COSTS false) EXECUTE st2(10, 20);=0A= ! = QUERY PLAN = =0A= ! = -------------------------------------------------------------------------= ----------------------------------------------------------------------=0A= Sort=0A= Sort Key: t1.c1=0A= ! -> Hash Join=0A= ! Hash Cond: (t1.c3 =3D t2.c3)=0A= -> Foreign Scan on ft1 t1=0A= Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM = "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20))=0A= ! -> Hash=0A= ! -> HashAggregate=0A= ! -> Foreign Scan on ft2 t2=0A= ! Filter: (date_part('dow'::text, c4) =3D = 6::double precision)=0A= ! Remote SQL: SELECT "C 1", NULL, c3, c4, = NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) = 10))=0A= ! (11 rows)=0A= =0A= EXECUTE st2(10, 20);=0A= c1 | c2 | c3 | c4 | c5 = | c6 | c7 =0A= --- 445,463 ----=0A= -- subquery using stable function (can't be pushed down)=0A= PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN = (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) =3D 6) = ORDER BY c1;=0A= EXPLAIN (COSTS false) EXECUTE st2(10, 20);=0A= ! QUERY = PLAN =0A= ! = -------------------------------------------------------------------------= ----------------------------------------------------------------=0A= Sort=0A= Sort Key: t1.c1=0A= ! -> Nested Loop Semi Join=0A= ! Join Filter: (t1.c3 =3D t2.c3)=0A= -> Foreign Scan on ft1 t1=0A= Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM = "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20))=0A= ! -> Materialize=0A= ! -> Foreign Scan on ft2 t2=0A= ! Filter: (date_part('dow'::text, c4) =3D 6::double = precision)=0A= ! Remote SQL: SELECT "C 1", NULL, c3, c4, NULL, = NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) 10))=0A= ! (10 rows)=0A= =0A= EXECUTE st2(10, 20);=0A= c1 | c2 | c3 | c4 | c5 = | c6 | c7 =0A= ***************=0A= *** 574,580 ****=0A= -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A= ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 TYPE int;=0A= SELECT * FROM ft1 WHERE c1 =3D 1; -- ERROR=0A= ! ERROR: invalid input syntax for integer: "1970-01-02 00:00:00"=0A= CONTEXT: column c5 of foreign table ft1=0A= ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 TYPE timestamp;=0A= -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A= --- 573,579 ----=0A= -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A= ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 TYPE int;=0A= SELECT * FROM ft1 WHERE c1 =3D 1; -- ERROR=0A= ! ERROR: invalid input syntax for integer: "Fri Jan 02 00:00:00 1970"=0A= CONTEXT: column c5 of foreign table ft1=0A= ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 TYPE timestamp;=0A= -- = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A= ------=_NextPart_000_0024_01CD6062.070063E0--