Re: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints

On Thu, 12 Sept 2024 at 21:21, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> CREATE EXTENSION file_fdw;
> CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
> CREATE FOREIGN TABLE t(a int, b int not null) SERVER file_server OPTIONS (
> filename '/data.csv', format 'csv', null 'null' );
> SELECT * FROM t WHERE b IS NOT NULL;
> SELECT * FROM t WHERE b IS NULL;
>
> Outputs:
>
> postgres=# SELECT * FROM t WHERE b IS NOT NULL;
> a | b
> ---+----
> 1 | 10
> 2 | 20
> 3 |
> 4 | 40

hmm, it's pretty simple to fix, just;

@@ -2653,7 +2653,8 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
* qual which is either of these for a partitioned table must
also be that
* for all of its child partitions.
*/
- if (!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE)
+ if ((!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE) &&
+ rte->relkind != RELKIND_FOREIGN_TABLE)

but...

CREATE FOREIGN TABLE t(a int check(a >100), b int not null) SERVER
file_server OPTIONS (filename '/data.csv', format 'csv', null 'null'
);
SET constraint_exclusion=on;
explain select * from t where a< 100;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

it doesn't seem much different from that. Maybe the bug is in your
foreign table definition. The documents [1] seem to agree:

"Constraints on foreign tables (such as CHECK or NOT NULL clauses) are
not enforced by the core PostgreSQL system, and most foreign data
wrappers do not attempt to enforce them either; that is, the
constraint is simply assumed to hold true. There would be little point
in such enforcement since it would only apply to rows inserted or
updated via the foreign table, and not to rows modified by other
means, such as directly on the remote server. Instead, a constraint
attached to a foreign table should represent a constraint that is
being enforced by the remote server."

David

[1] https://www.postgresql.org/docs/devel/sql-createforeigntable.html