Re: Postgres_fdw join pushdown - wrong results with whole-row reference

On 2016/06/22 19:37, Ashutosh Bapat wrote:
> On Wed, Jun 22, 2016 at 3:57 PM, Etsuro Fujita
> Maybe I'm confused, but I think that in the system-column case it's
> the user's responsibility to specify system columns for foreign
> tables in a local query only when that makes sense on the remote
> end, as shown in the below counter example:
>
> postgres=# create foreign table fv1 (a int, b int) server myserver
> options (table_name 'v1');
> CREATE FOREIGN TABLE
> postgres=# select ctid, * from fv1;
> ERROR: column "ctid" does not exist
> CONTEXT: Remote SQL command: SELECT a, b, ctid FROM public.v1

> But a ctid, when available, would rightly get nullified when referenced
> as a column of table.

Really?

> What happens with the other system columns is we 0
> them out locally, whether they are available at the foreign server or
> not. We never try to check whether they are available at the foreign
> server or not. If we use such column's column name to decide whether to
> report 0 or null and if that column is not available at the foreign
> table, we will get error. I think we should avoid that. Those column
> anyway do not make any sense.

Agreed except for oid. I think we should handle oid in the same way as
ctid, which I'll work on in the next CF.

I think the proposed idea of applying record::text explicit coercion to
a whole-row reference in the IS NOT NULL condition in the CASE WHEN
conversion would work as expected as you explained, but I'm concerned
that the cost wouldn't be negligible when the foreign table has a lot of
columns.

Best regards,
Etsuro Fujita