Re: INSERT ... ON CONFLICT syntax issues

On Tue, May 5, 2015 at 9:36 AM, Andres Freund <andres@anarazel.de> wrote:
> Theoretically this changes the pictures for FDWs, right? Right now
> there's
> +    <para>
> +     <command>INSERT</> with an <literal>ON CONFLICT</> clause is not
> +     supported with a unique index inference specification, since a
> +     conflict arbitrating unique index cannot meaningfully be inferred
> +     on a foreign table (this implies that <literal>ON CONFLICT DO
> +     UPDATE</> is never supported, since the specification is
> +     mandatory there).
> +    </para>
> but theoretically the constraint name could be meaningful on the other
> side...

Well, the inference clause could be too -- in that sense, the
constraint name isn't special at all. But you need to invent a way of
making the optimizer infer an index on the foreign side (and even with
a named constraint, we go from constraint Oid in the parser to
pg_index Oid in the optimizer, so it's a similar process to regular
inference). Of course, teaching the optimizer about foreign indexes is
a whole new infrastructure.

Note that this really is the explanation for why postgres_fdw only has
limited support. Sure, I haven't added deparsing logic for ON CONFLICT
UPDATE, but it would be pretty easy to do so, and that isn't the
blocker at all.

> I don't think this is anyting for 9.5, but it might be interesting for
> later.

Sure.
-- 
Peter Geoghegan