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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
