Re: [HACKERS] postgres_fdw bug in 9.6

On Fri, Jan 19, 2018 at 10:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Hm. It did fail as advertised when I connected to the contrib_regression
>> database (after installcheck) and entered the query by hand; I
>> copied-and-pasted the result of that to show you. It's possible that it
>> would not have failed in the particular spot where you chose to insert it
>> in the regression script, if for example there were nondefault planner GUC
>> settings active at that spot. Did you check that the script produced the
>> expected failure against unpatched code?
>
> No. I guess I'll have to go debug this. Argh.

Well, I'm a little stumped. When I do it the way you did it, it fails
with the same error you got:

contrib_regression=# EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c1 = ft4.c1
AND ft1.c1 = ft5.c1 FOR UPDATE;
ERROR: outer pathkeys do not match mergeclauses

But when I add the same command to postgres_fdw.sql and run it as part
of the regression tests, it works. I tried adding it at the end with
\c beforehand so that there wouldn't be any temporary settings in
effect. It still didn't generate an error. So then I ran 'SHOW ALL'
from the regression test file and from my psql terminal and comparing
the results:

< application_name | pg_regress/postgres_fdw
| Sets the application name to be reported in statistics and
logs.
> application_name | psql | Sets the application name to be reported in statistics and logs.
< DateStyle | Postgres, MDY
| Sets the display format for date and time values.
> DateStyle | ISO, MDY | Sets the display format for date and time values.
< IntervalStyle | postgres_verbose
| Sets the display format for interval values.
> IntervalStyle | postgres | Sets the display format for interval values.
< TimeZone | PST8PDT
| Sets the time zone for displaying and interpreting time
stamps.
> TimeZone | US/Eastern | Sets the time zone for displaying and interpreting time stamps.

I then tried setting all of those GUCs to have the same value in my
psql session that they had in the script, but I still got the same
error. So, with the exact same settings in effect, this query
reliable produces the error from psql and reliably fails to produce
the error from pg_regress. Furthermore, it remains true even when the
query is sent from pg_regress as the first query in a brand new
session, so the problem can't be some kind of session state left
behind by the previous postgres_fdw tests. In fact, if I extract the
exact psql command that pg_regress runs and execute it by hand, then
the query errors out:

"/Users/rhaas/install/dev/bin/psql" -X -a -q -d "contrib_regression" <
"/Users/rhaas/pgsql/contrib/postgres_fdw/sql/postgres_fdw.sql"

But the exact same command run by pg_regress does not error out.
Maybe the environment variables that pg_regress sets up affect
something? But what would they affect on the server, other than the
values of GUCs? I'm probably missing something dumb here, but I can't
think of what it is at the moment.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company