Fix WITH CHECK OPTION on views referencing postgres_fdw tables.
If a view references a foreign table, and the foreign table has a
BEFORE INSERT trigger, then it's possible for a tuple inserted or
updated through the view to be changed such that it violates the
view's WITH CHECK OPTION constraint.
Before this commit, postgres_fdw handled this case inconsistently. A
RETURNING clause on the INSERT or UPDATE statement targeting the view
would cause the finally-inserted tuple to be read back, and the WITH
CHECK OPTION violation would throw an error. But without a RETURNING
clause, postgres_fdw would not read the final tuple back, and WITH
CHECK OPTION would not throw an error for the violation (or may throw
an error when there is no real violation). AFTER ROW triggers on the
foreign table had a similar effect as a RETURNING clause on the INSERT
or UPDATE statement.
To fix, this commit retrieves the attributes needed to enforce the
WITH CHECK OPTION constraint along with the attributes needed for the
RETURNING clause (if any) from the remote side. Thus, the WITH CHECK
OPTION constraint is always evaluated against the final tuple after
any triggers on the remote side.
This fix may be considered inconsistent with CHECK constraints
declared on foreign tables, which are not enforced locally at all
(because the constraint is on a remote object). The discussion
concluded that this difference is reasonable, because the WITH CHECK
OPTION is a constraint on the local view (not any remote object);
therefore it only makes sense to enforce its WITH CHECK OPTION
constraint locally.
Author: Etsuro Fujita
Reviewed-by: Arthur Zakirov, Stephen Frost
Discussion: https://www.postgresql.org/message-id/7eb58fab-fd3b-781b-ac33-f7cfec96021f%40lab.ntt.co.jp
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a45adc747e271556eb9443973264bf3353c86524
Modified Files
--------------
contrib/postgres_fdw/deparse.c | 41 ++++--
contrib/postgres_fdw/expected/postgres_fdw.out | 165 +++++++++++++++++++++----
contrib/postgres_fdw/postgres_fdw.c | 18 ++-
contrib/postgres_fdw/postgres_fdw.h | 6 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 61 +++++++--
doc/src/sgml/fdwhandler.sgml | 28 +++--
6 files changed, 261 insertions(+), 58 deletions(-)