postgres_fdw supports ON CONFLICT DO NOTHING, provided no inference
specification is provided. Foreign tables do not have associated
unique indexes (or exclusion constraints) as far as the optimizer is
concerned, and so Postgres does not accept an inference specification
for foreign tables -- the optimizer will simply complain that a unique
index that satisfies the user's inference specification is
unavailable.
There is no support for ON CONFLICT DO UPDATE with postgres_fdw, but
that's really only because an inference specification (or explicitly
named constraint) is always required for DO UPDATE. The deparsing
support actually added will have deparsing add "ON CONFLICT DO
NOTHING" for the SQL generated for execution on foreign servers if the
original statement had that exact, unadorned ON CONFLICT clause. As
things stand, every other possible ON CONFLICT clause will throw an
error in some way before the FDW is consulted at all, so FDW authors
need not concern themselves with those other cases (unless perhaps we
allow ON CONFLICT DO UPDATE to not require an inference specification
in a last minute behavioral tweak, as suggested by Simon Riggs, making
ON CONFLICT DO UPDATE support by foreign data wrappers a possibility
that must be considered).
postgres_fdw handles the one simple ON CONFLICT DO NOTHING case (the
only case that can actually reach it), but no other FDW we ship pay
any attention. Do we need to make existing contrib FDWs, like
file_fdw, explicitly reject unadorned ON CONFLICT DO NOTHING clauses
as wrong-headed? Is it okay to just let them not pay attention at all
on the theory that it's the same as performing INSERT ... ON CONFLICT
DO NOTHING on a table that has no unique indexes or exclusion
constraints?
In any case, third party foreign data wrappers that target other
database system will totally ignore ON CONFLICT DO NOTHING when built
against the master branch (unless they consider these questions). They
should perhaps make a point of rejecting DO NOTHING outright where it
makes sense that support could exist, but it just doesn't. Or they
could just add support (I imagine that this would be very easy for
mysql_fdw, for example -- MySQL has INSERT IGNORE). I feel a
compatibility item in the release notes is in order so the question is
considered, but there seems to be no place to do that on the Wiki, and
the original commit message does not have a note like this.
--
Peter Geoghegan
