Re: FDW for PostgreSQL

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> TBH I think this is a fairly bad idea. You can get that behavior via
> dblink if you need it,

While I appreciate that dblink can do it, I simply don't see it as a
good solution to this.

> but there's no way to do it in an FDW without
> ending up with astonishing (and not in a good way) semantics. A commit
> would force committal of everything that'd been done through that
> connection, regardless of transaction/subtransaction structure up to
> that point; and it would also destroy open cursors. The only way to
> make this sane at all would be to provide user control of which
> operations go to which connections; which is inherent in dblink's API
> but is simply not a concept in the FDW universe. And I don't want to
> try to plaster it on, either.

This concern would make a lot more sense to me if we were sharing a
given FDW connection between multiple client backends/sessions; I admit
that I've not looked through the code but the documentation seems to
imply that we create one-or-more FDW connection per backend session and
there's no sharing going on.

A single backend will be operating in a linear fashion through the
commands sent to it. As such, I'm not sure that it's quite as bad as it
may seem.

Perhaps a reasonable compromise would be to have a SERVER option which
is along the lines of 'autocommit', where a user could request that any
query to this server is automatically committed independent of the
client transaction. I'd be happier if we could allow the user to
control it, but this would at least allow for 'log tables', which are
defined using this server definition, where long-running pl/pgsql code
could log progress where other connections could see it.

Thanks,

Stephen