Обсуждение: FDW and BDR
Hi,
I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR enabled databases. And other FDW and FTS related commands 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?
--
Willy-Bas Loos
Sorry, forgot [GENERAL] in the subject at first.
--
On Wed, Sep 2, 2015 at 12:46 PM, Willy-Bas Loos <willybas@gmail.com> wrote:
(maybe one could create the FDW before configuring replication)Hi,I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR enabled databases. And other FDW and FTS related commands 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?Cheers,
--Willy-Bas Loos
--
Willy-Bas Loos
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
Haha, that is funny :) It's always nice to see problems evaporate.
Thank's a lot for your answers.On Wed, Sep 2, 2015 at 2:40 PM, Andres Freund <andres@anarazel.de> wrote:
Either way it should be
simple to implement.
--
Willy-Bas Loos
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