[HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Peter Geoghegan]

On Mon, May 25, 2015 at 1:28 AM, Simon Riggs  wrote:
> My earlier summary was that the support for multiple constraints has been
> poorly thought through. This is an example of the breakage I have been
> complaining about when we are forced to specify the constraint
> (conflict-target).
>
> This is not just related to FDWs and should not be fixed solely for FDWs.
> This was already an open item for me in 9.5, now even more so.

I agree that the decision to change the current behavior has nothing
to do with FDWs. There is no reason to treat foreign tables
differently to local ones in this regard, which implies that ON
CONFLICT DO UPDATE cannot work with postgres_fdw unless and until
someone invents foreign constraints on foreign tables (I think), or
unless we change our mind generally (for other reasons). So,
certainly, the rationale for mandating (or not mandating) an inference
specification with ON CONFLICT DO UPDATE ought to come from balancing
concerns about safety, compatibility, flexibility, and so on.

I did not mean to imply that your comments were unreasonable/too late.
However, I don't see a lot of demand for changing the behavior. There
is at least some demand for accepting as arbiters multiple unique
constraints (that are not more or less equivalent), from Andres for
example, but that's a different question. It's also something that
could reasonably be added later.

-- 
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

Re: [HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Peter Geoghegan]

On Thu, May 28, 2015 at 1:20 AM, Etsuro Fujita
 wrote:
> I think that those are interesting problems.  Wouldn't we need some
> additional hacks for the core or FDW to perform an operation that is
> equivalent to dynamically switching the ExecInsert/ExecForeignInsert
> processing to the ExecUpdate/ExecForeignUpdate processing in case of a
> conflict?

I did not imagine so. Rather, I thought that it was a matter of simply
introducing a way that foreign tables can have foreign constraints
recognizable by the local Postgres optimizer. The decision to  insert
or update must belong to the foreign server, since the feature could
be useful for systems like MySQL, and not just Postgres. I may be
mistaken.

-- 
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

Re: [HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Etsuro Fujita]

On 2015/05/25 9:16, Peter Geoghegan wrote:

AddForeignUpdateTargets() actually won't be called with ON CONFLICT DO
UPDATE, and so it isn't exactly true that the only obstacle to making
FDWs support ON CONFLICT DO UPDATE is around inference of arbiter
unique indexes on the foreign side. It's *almost* true, though.


I think that those are interesting problems.  Wouldn't we need some 
additional hacks for the core or FDW to perform an operation that is 
equivalent to dynamically switching the ExecInsert/ExecForeignInsert 
processing to the ExecUpdate/ExecForeignUpdate processing in case of a 
conflict?


Best regards,
Etsuro Fujita


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Simon Riggs]

On 25 May 2015 at 00:22, Peter Geoghegan  wrote:

>
> 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).
>

My earlier summary was that the support for multiple constraints has been
poorly thought through. This is an example of the breakage I have been
complaining about when we are forced to specify the constraint
(conflict-target).

This is not just related to FDWs and should not be fixed solely for FDWs.
This was already an open item for me in 9.5, now even more so.

My comments do not come at the last minute, what Peter means is that we
should make a change now in response to the concerns I have previously
raised.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

[HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Peter Geoghegan]

On Sun, May 24, 2015 at 5:16 PM, Peter Geoghegan  wrote:
> AddForeignUpdateTargets() actually won't be called with ON CONFLICT DO
> UPDATE, and so it isn't exactly true that the only obstacle to making
> FDWs support ON CONFLICT DO UPDATE is around inference of arbiter
> unique indexes on the foreign side. It's *almost* true, though.

Attached patch clears this up within the fdw-handler documentation. I
think it's worth separately noting from the existing commentary on
limitations with FDWs and ON CONFLICT.

-- 
Peter Geoghegan
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 2361577..569a22d 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -399,6 +399,13 @@ AddForeignUpdateTargets (Query *parsetree,
 
 
 
+ Note that AddForeignUpdateTargets will not be called
+ for INSERT operations with an ON CONFLICT DO
+ UPDATE clause.  Such INSERT operations are
+ unsupported when a foreign table is targeted.
+
+
+
 
 List *
 PlanForeignModify (PlannerInfo *root,

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: 9.5 release notes may need ON CONFLICT DO NOTHING compatibility notice for FDW authors

[Peter Geoghegan]

On Sun, May 24, 2015 at 4:22 PM, Peter Geoghegan  wrote:
> 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).

AddForeignUpdateTargets() actually won't be called with ON CONFLICT DO
UPDATE, and so it isn't exactly true that the only obstacle to making
FDWs support ON CONFLICT DO UPDATE is around inference of arbiter
unique indexes on the foreign side. It's *almost* true, though.

-- 
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