Thread: FDW and BDR

On 2 September 2015 at 18:46, Willy-Bas Loos <willybas@gmail.com> wrote:
> Sorry, forgot [GENERAL] in the subject at first.

You don't need to add it. The list manager software does that.

> I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR
> enabled databases. And other FDW and FTS related commands too.

Correct.

The reason for this is that BDR replicates at a database level, but
CREATE SERVER and CREATE USER MAPPING are global, affecting all
databases on a PostgreSQL install. BDR can't therefore guarantee to
replicate CREATE SERVER to other nodes, since it might get run on a
non-BDR-enabled database.

If you CREATE FOREIGN TABLE it'll succeed (since the foreign server
exists locally) but might then fail on remote nodes. We've already
committed it locally, though. This will cause a stop-the-world
replication halt until the administrator intervenes by creating the
foreign server on the other nodes. It's made even worse by the impact
of the global DDL lock that's held until the CREATE FOREIGN TABLE
commits on the other nodes.

To prevent that, we reject CREATE FOREIGN TABLE.

Supporting this requires one of:

- Global DDL replication support in BDR. This is quite hard to do and
is not currently supported.

- 2-phase transaction DDL replication in BDR. Logical decoding would
have to support decoding prepared transactions for this, and it
doesn't currently. So it's 9.6 at the very soonest, and we have other
higher priorities for this.

- Detecting global dependencies in a statement and checking that all
peer nodes have those dependencies, then locking them before allowing
the DDL to commit locally. This requires nontrivial statement-specific
support and a bunch of extensions to how BDR nodes communicate.

We have higher priorities, like getting more of the BDR foundations
into 9.6 and meeting the needs of customers actively using BDR. So at
this point there's no set time frame on support for FDWs.

Basically, PostgreSQL having global objects when we replicate on a
per-database basis is messy. It creates issues with users/roles, too.



> This seems obvious, but i want to make sure:
> Does that mean that FDW's are not supported at all in databases that use
> Bi-Directional Replication?

Correct at this time. You can have FDWs in a different database on the
same Pg install, but not use FDWs in a BDR-enabled database.

> (maybe one could create the FDW before configuring replication)

No.

It's possible to override the filter using documented settings, but I
don't advise doing so unless you're extremely sure you need this, and
understand exactly what you're getting into. If you break it, you get
to keep the pieces.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer <craig@2ndquadrant.com> writes:
> On 2 September 2015 at 18:46, Willy-Bas Loos <willybas@gmail.com> wrote:
>> I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR
>> enabled databases. And other FDW and FTS related commands too.

> Correct.

> The reason for this is that BDR replicates at a database level, but
> CREATE SERVER and CREATE USER MAPPING are global, affecting all
> databases on a PostgreSQL install. BDR can't therefore guarantee to
> replicate CREATE SERVER to other nodes, since it might get run on a
> non-BDR-enabled database.

Uh ... what?

I do not know what the actual reason for this restriction is, but that
explanation is nonsense.  There are no shared catalogs involved with
FDWs.

            regards, tom lane

On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
> The reason for this is that BDR replicates at a database level, but
> CREATE SERVER and CREATE USER MAPPING are global, affecting all
> databases on a PostgreSQL install. BDR can't therefore guarantee to
> replicate CREATE SERVER to other nodes, since it might get run on a
> non-BDR-enabled database.

As Tom explained thats not the case for either of those two. To my
knowledge the only reason those two commands aren't implemented is that
either nobody implemented the required ddl deparsing or, actually
somewhat likely, nobody removed the error check. Either way it should be
simple to implement.

Greetings,

Andres Freund

Andres Freund wrote:
> On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
> > The reason for this is that BDR replicates at a database level, but
> > CREATE SERVER and CREATE USER MAPPING are global, affecting all
> > databases on a PostgreSQL install. BDR can't therefore guarantee to
> > replicate CREATE SERVER to other nodes, since it might get run on a
> > non-BDR-enabled database.
>
> As Tom explained thats not the case for either of those two. To my
> knowledge the only reason those two commands aren't implemented is that
> either nobody implemented the required ddl deparsing or, actually
> somewhat likely, nobody removed the error check. Either way it should be
> simple to implement.

Deparsing for FDW objects is in the patch that got committed for 9.5
(actually, Andres himself implemented those bits), but I'm not sure that
it was in the version used by the 9.4-BDR branch.  Anyway, even if it's
not there, it should be reasonably simple to lift the code from 9.5 into
BDR's branch.  And then remove the restrictions.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2 September 2015 at 20:40, Andres Freund <andres@anarazel.de> wrote:
> On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
>> The reason for this is that BDR replicates at a database level, but
>> CREATE SERVER and CREATE USER MAPPING are global, affecting all
>> databases on a PostgreSQL install. BDR can't therefore guarantee to
>> replicate CREATE SERVER to other nodes, since it might get run on a
>> non-BDR-enabled database.
>
> As Tom explained thats not the case for either of those two. To my
> knowledge the only reason those two commands aren't implemented is that
> either nobody implemented the required ddl deparsing or, actually
> somewhat likely, nobody removed the error check. Either way it should be
> simple to implement.

Well, that's embarrassing. Whoops. I could've sworn CREATE SERVER was global.

Thanks Tom and Andres for clearing up the misinformation.


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services