Thread: standard interfaces for replication providers
Hi, We have just released an add-on to PostgreSQL supporting the GORDA Architecture and Programming Interface (GAPI). This opens up support for DBMS independent replication middleware, aimed at eager and multi-master replication in clusters and WANs. The implementation of the GAPI is achieved in two steps. First, a set of patches to the PostgreSQL server and a plugin provide the necessary functionality with minimal intrusion. Then the GAPI is exposed in a standalone Java process. Note that this is not yet a complete replication solution. Instead, we seek feedback on exposed features, their rendering as Java interfaces, and their implementation on PostgreSQL. Downloads of source code and documentation are available at: http://gorda.di.uminho.pt/community There you will soon find the same interface implemented on Apache Derby and a replication suite based on group-communication. We welcome the comments and suggestions of PostgreSQL hackers. Best regards, Alfranio Junior.
On 8/4/06, alfranio correia junior <alfranio@lsd.di.uminho.pt> wrote: > patches to the PostgreSQL server and a plugin provide the necessary > functionality with minimal intrusion. I haven't looked at the patch for this in awhile, but does anyone have anything against it? I personally like the triggers and think it would be a nice thing to add. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> On 8/4/06, alfranio correia junior <alfranio@lsd.di.uminho.pt> wrote:
>> patches to the PostgreSQL server and a plugin provide the necessary
>> functionality with minimal intrusion.
> I haven't looked at the patch for this in awhile, but does anyone have
> anything against it?
We haven't been able to build production-grade multi-master replication
without the barrier of a "standard" database-agnostic API, so I kinda
doubt that it will work all that much better with one. See Slony-II.
In short the burden of proof is to show why this should go in, not why not.
(Suitable proof would be a usable replication system built atop it...)
regards, tom lane
On Friday 04 August 2006 16:46, Tom Lane wrote: > We haven't been able to build production-grade multi-master replication > without the barrier of a "standard" database-agnostic API, so I kinda > doubt that it will work all that much better with one. See Slony-II. I would argue that people haven't been able to build production-grade multi-master replication, in part, due to the barrier of not having a "standard" database-agnostic API. :-) In fact, the problem is not the lack of a "standard" API but the lack of an API at all. Having to learn the intrincacies of a database server (or build a full fledged server wrapper) to experiment with simple prototypes is a serious hurdle for R&D in database replication. This has been the case for replication based on group communication. > In short the burden of proof is to show why this should go in, not why not. Please don't confuse two proposals included in the distributed package: (1) A PostgreSQL specific patch, which implements a minimal set of required features with a PostgreSQL-specific interface (e.g. triggers, new statements, configuration variables). This is by no means a "standard" interface. The included technical report discusses why these are required for a variety of replication scenarios. (2) A DBMS independent "standard" Java interface, which is easily built on the modified PostgreSQL server using both existing features (e.g. normal triggers) and those introduced by (1). Included toy applications show how this would be useful for replication, but we are not (yet) pushing for its adoption. > (Suitable proof would be a usable replication system built atop it...) I agree. ;) -- Jose Orlando Pereira
Hi, José Orlando Pereira wrote: > I would argue that people haven't been able to build production-grade > multi-master replication, in part, due to the barrier of not having > a "standard" database-agnostic API. :-) > > In fact, the problem is not the lack of a "standard" API but the lack of an > API at all. Having to learn the intrincacies of a database server (or build a > full fledged server wrapper) to experiment with simple prototypes is a > serious hurdle for R&D in database replication. This has been the case for > replication based on group communication. I disagree. There have been a couple of approaches and each has had a different interface to the database. But for most of them, coding the database interface was _not_ the hardest part. And a good understanding of the database system internals is simply required to write a good replication system. > Please don't confuse two proposals included in the distributed package: > > (1) A PostgreSQL specific patch, which implements a minimal set of required > features with a PostgreSQL-specific interface (e.g. triggers, new statements, > configuration variables). This is by no means a "standard" interface. The > included technical report discusses why these are required for a variety of > replication scenarios. Where do I find the included technical report? I've read the READMEs in PostgreSQL/G toplevel and csrc directory and did not find convincing reasons why exactly these triggers need to be added as replication hooks. In fact, Postgres-R (8) would already need different hooks. From studying the patch, I understand that these hooks are quite close to what's needed for a Postgres-R or Slony-II like sync, multi-master replication system (i.e. hooks for writeset extraction, the addition of a 'serialization error' for remote transactions). I can see use for such an API as soon as we have a production-grade replication system, which performs well enough in most applications (i.e. when we know exactly where to place the hooks). But up until then, people will try different algorithms and different hooks. Concerning my work on Postgres-R I can tell: I'm not going to use these triggers (hooks) because they are limiting. I know enough about the database system internals and I _want_ to fiddle with the database system. Why should I use such an API? Regards Markus
On Monday 07 August 2006 10:58, Markus Schiltknecht wrote: > > Where do I find the included technical report? Sorry, stuff was put twice in the zip file making it somewhat confusing. It is in postgresql-g-0.1/javasrc/GordaInterfaces/docs/gapi.pdf or directly on the web site at http://gorda.di.uminho.pt/download/reports/gapi.pdf. > From studying the patch, I understand that these hooks are quite close > to what's needed for a Postgres-R or Slony-II like sync, multi-master > replication system (i.e. hooks for writeset extraction, the addition of > a 'serialization error' for remote transactions). Good. Supporting Postgres-R-style protocols was one major goal of our work. > In fact, Postgres-R (8) would already need different hooks. > Concerning my work on Postgres-R I can tell: I'm not going to use these > triggers (hooks) because they are limiting. Can you point out why is it so, given that it is admittedly quite close? We'd rather discuss specific issues instead of the general topic of whether to build APIs around them. We certainly are not married to the proposed interfaces, although the functionality they capture does reflect our experience with several algorithms. -- Jose Orlando Pereira
Markus Schiltknecht wrote: > Hi, > > José Orlando Pereira wrote: >> I would argue that people haven't been able to build production-grade >> multi-master replication, in part, due to the barrier of not having a >> "standard" database-agnostic API. :-) >> >> In fact, the problem is not the lack of a "standard" API but the lack >> of an API at all. Having to learn the intrincacies of a database >> server (or build a full fledged server wrapper) to experiment with >> simple prototypes is a serious hurdle for R&D in database >> replication. This has been the case for replication based on group >> communication. > > I disagree. There have been a couple of approaches and each has had a > different interface to the database. But for most of them, coding the > database interface was _not_ the hardest part. And a good > understanding of the database system internals is simply required to > write a good replication system. I agree with you. But, I would add that the same understanding is required to design and implement this interface on any database system. > >> Please don't confuse two proposals included in the distributed package: >> >> (1) A PostgreSQL specific patch, which implements a minimal set of >> required features with a PostgreSQL-specific interface (e.g. >> triggers, new statements, configuration variables). This is by no >> means a "standard" interface. The included technical report discusses >> why these are required for a variety of replication scenarios. > > Where do I find the included technical report? GordaInterfaces/javasrc/docs/gapi.pdf > > I've read the READMEs in PostgreSQL/G toplevel and csrc directory and > did not find convincing reasons why exactly these triggers need to be > added as replication hooks. In fact, Postgres-R (8) would already need > different hooks. Such triggers are not necessary to develop any kind of replication protocol... However, indeed any replication protocol requires a set of hooks that might be enabled by different means, e.g. call back functions, triggers, etc, etc... We developed our hooks by means of triggers as their provide a standard interface (triggers) that might be easily exploited by other projects besides replication, e.g. materialized views. > > From studying the patch, I understand that these hooks are quite close > to what's needed for a Postgres-R or Slony-II like sync, multi-master > replication system (i.e. hooks for writeset extraction, the addition > of a 'serialization error' for remote transactions). > > I can see use for such an API as soon as we have a production-grade > replication system, which performs well enough in most applications > (i.e. when we know exactly where to place the hooks). But up until > then, people will try different algorithms and different hooks. > > Concerning my work on Postgres-R I can tell: I'm not going to use > these triggers (hooks) because they are limiting. Could you tell me why they are limiting ? > I know enough about the database system internals and I _want_ to > fiddle with the database system. Why should I use such an API? The idea is not to impose our API but what we really want is to show that behind the requirements for replication systems there is place for a variety of systems that could be leverage by means of a "standard api". Jose Orlando, any comments on that ?
Hi, Jose Orlando Pereira wrote: > Sorry, stuff was put twice in the zip file making it somewhat confusing. It is > in postgresql-g-0.1/javasrc/GordaInterfaces/docs/gapi.pdf or directly on the > web site at http://gorda.di.uminho.pt/download/reports/gapi.pdf. Thank you. I've just had a quick glance at it. > Can you point out why is it [limiting], given that it is admittedly quite close? An API is always limiting. And if you have to change the API a lot, to fit your needs, what's the point in using it at all? Good APIs don't change a lot. Even if it's quite close, I estimate the effort to port Postgres-R to use your API to be quite large. I.e. the first missing thing that came to my mind was the ordering of processes when waking them up after waiting for a lock. Postgres-R needs the processes to be woken up in the order of writeset arrival. Now, I didn't see anything related in the patch, but the gapi.pdf has 'Predictable Deadlock Handling' in it. I need to take another look... > We'd rather discuss specific issues instead of the general topic of whether to > build APIs around them. We certainly are not married to the proposed > interfaces, although the functionality they capture does reflect our > experience with several algorithms. I still feel that I would need ways too many hooks. Especially when you consider advanced replication features such as data partitioning and remote query execution. What also worries me is the use of triggers. ISTM that using triggers is not deep enough in the database. In the above example, do I really want to fire a trigger every time the database needs to wake up a process? In PostgreSQL a trigger normally runs within a transaction. How do you work around that? I'm operating a level deeper with Postgres-R and really enjoy the freedom I have with C. Having to write a hook or trigger for every change in the database systems seems a lot of work, which I tend to postpone until such a thing is really needed. Regards Markus
In the last exciting episode, markus@bluegap.ch (Markus Schiltknecht) wrote: > I'm operating a level deeper with Postgres-R and really enjoy the > freedom I have with C. Having to write a hook or trigger for every > change in the database systems seems a lot of work, which I tend to > postpone until such a thing is really needed. The fact that GORDA is operating as a Java application seems to me to throw a big layer of fuzziness in the way, too. Most databases that are interesting to replicate are implemented in C or C++, thereby implying that a suitably "deep" API needs to be implemented in C. In the case of PostgresQL, at least, operating in Java means that you need to operate at "arms length" from the database, which means the replication system is by no means tightly integrated. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxdatabases.info/info/postgresql.html Know the list of "large, chronic problems". If there is any problem with the window system, blame it on the activity system. Any lack of user functionality should be attributed to the lack of a command processor. A suprisingly large number of people will believe that you have thought in depth about the issue to which you are alluding when you do. -- from the Symbolics Guidelines for Sending Mail
Hello Christopher,
Christopher Browne wrote:
> Most databases that are interesting to replicate are implemented in C
> or C++, thereby implying that a suitably "deep" API needs to be
> implemented in C.
I generally agree with you. Although it's probably worth mentioning that
the API they propose adds hooks to PostgreSQL in the form of triggers.
This API comes as a patch is against the PostgreSQL source, thus in C.
The nature of triggers in PostgreSQL would then allow to write
replication systems in whatever language you prefer, as long as there is
a PL/{$LANG} for $LANG = your favorite.
I'm questioning if a replication system can be written by only using
triggers as hooks. AFAIK Slony-I uses triggers, so you can probably
better comment on problems or limitations using triggers. For me a
shared library with some hooks as C function calls seems a more
plausible approach.
Regards
Markus
Markus Schiltknecht wrote: > Hi, > > Jose Orlando Pereira wrote: >> Sorry, stuff was put twice in the zip file making it somewhat >> confusing. It is in >> postgresql-g-0.1/javasrc/GordaInterfaces/docs/gapi.pdf or directly on >> the web site at http://gorda.di.uminho.pt/download/reports/gapi.pdf. > > Thank you. I've just had a quick glance at it. > >> Can you point out why is it [limiting], given that it is admittedly >> quite close? > > An API is always limiting. And if you have to change the API a lot, to > fit your needs, what's the point in using it at all? Good APIs don't > change a lot. > > Even if it's quite close, I estimate the effort to port Postgres-R to > use your API to be quite large. I.e. the first missing thing that came > to my mind was the ordering of processes when waking them up after > waiting for a lock. Postgres-R needs the processes to be woken up in > the order of writeset arrival. > > Now, I didn't see anything related in the patch, but the gapi.pdf has > 'Predictable Deadlock Handling' in it. I need to take another look... If I correctly understood your idea, a priority mechanism would be enough to do so and different applications might exploit it. Most likely, we need this to apply remote transactions. However, note that a priority mechanism is not only of interesting in the field of replication systems but it might be used to improve performance for instance. Take a look at the ideas presented in http://www.cs.cmu.edu/~bianca/icde04.pdf Unfortunately, our current "prototype" only provides two levels: high priority or normal priority. Definitely, it should be improved and we are aware of that. > >> We'd rather discuss specific issues instead of the general topic of >> whether to build APIs around them. We certainly are not married to >> the proposed interfaces, although the functionality they capture does >> reflect our experience with several algorithms. > > I still feel that I would need ways too many hooks. Especially when > you consider advanced replication features such as data partitioning > and remote query execution. > > What also worries me is the use of triggers. ISTM that using triggers > is not deep enough in the database. In the above example, do I really > want to fire a trigger every time the database needs to wake up a > process? In PostgreSQL a trigger normally runs within a transaction. > How do you work around that? I think we are talking about different levels.... as I said a high priority mechanism would be enough. In this case, the API should provide only an interface to set the priority of a transaction.... In our case, still unfinished and quite simple: "set transaction master" but it could be easily transformed into "set transaction priority <n>". Best regards, Alfranio Junior.
Markus Schiltknecht wrote: > > I'm questioning if a replication system can be written by only using > triggers as hooks. AFAIK Slony-I uses triggers, so you can probably > better comment on problems or limitations using triggers. For me a > shared library with some hooks as C function calls seems a more > plausible approach. Of course not... It is impossible to build a replication system entirely by only using triggers... But definitely, there is a set of common requirements among a variety of replication systems. Moreover, such requirements are also useful to other systems as well. Roughly, the GAPI reflects any event inside a database system and allows any application to intercept and modify them. For instance, an event might be: 1 - database shutdown, startup 2 - connection shutdown, startup 3 - statement reception 4 - parsing 5 - execution plan generation 6 - tuples written Our current prototype covers some of them and some of them partially (2,6). Besides, we also need the priority mechanism. Best regards, Alfranio Junior.
On Tuesday 08 August 2006 17:44, Christopher Browne wrote: > Most databases that are interesting to replicate are implemented in C > or C++, thereby implying that a suitably "deep" API needs to be > implemented in C. > > In the case of PostgresQL, at least, operating in Java means that you > need to operate at "arms length" from the database, which means the > replication system is by no means tightly integrated. Yes, PostgreSQL is the tough one here. Having a single-process multithreaded PostgreSQL in which PL/Java would run in one global JVM would sure feel like Christmas! ;) Note however that the problem is not Java, but any package that does not expect the current PostgreSQL concurrency model. AFAIK Postgres-R has same "arms length" architecture with an external process, probably for compatibility with Spread. We have however tried to minimize the inconvenience, even for PostgreSQL, by doing the following: 1. Implementation was done in two layers, in which the PostgreSQL-specific one is 100% Java-free and feature complete. 2. High level functionality (i.e. transaction priorities instead of directly handling individual lock operations) reduce the number of round-trips to the replication process. 3. Event listeners in Java can be registered as non-blocking, thus putting the external JVM out of the critical path except in a few critical operations. Finally, although I concede that most databases that are interesting to replicate are written in C or C++, these days, most are also getting tightly coupled JVMs fairly high in their feature lists. -- Jose Orlando Pereira
On Tuesday 08 August 2006 15:24, Markus Schiltknecht wrote: > > An API is always limiting. Which is a good thing when you are not the one using it but the one committing to support it. :-) > I still feel that I would need ways too many hooks. Especially when you > consider advanced replication features such as data partitioning and > remote query execution. Indeed. We have not prototyped those features. Nonetheless, look at the "silly" query cache example in the distribution (search for QueryCache.java). It shows how the proposed hooks might be used to intercept a query and fake a result set, while at the same time executing some stuff locally. (Warning: this runs on Apache Derby only, as in PostgreSQL we'd need something like PL/J for server side JDBC.). > What also worries me is the use of triggers. ISTM that using triggers is > not deep enough in the database. In the above example, do I really want > to fire a trigger every time the database needs to wake up a process? In > PostgreSQL a trigger normally runs within a transaction. How do you work > around that? As Alfranio has pointed out in another message in this thread, these triggers are high level. We never consider some thing "trigger on lock acquire" (also because it also would hardly be portable). They certainly are more coarse grained than the standard on update stuff. Furthermore, having on commit triggers running within transactional boundaries is very useful. Think about recording global commit order or global timestamps in the originating site after propagation. -- Jose Orlando Pereira
Hello Alfranio, alfranio correia junior wrote: > Of course not... > It is impossible to build a replication system entirely by only using > triggers... Hm, I don't think it's impossible, just unpractical. Anyway, if you say so yourself, I really doubt the use of GAPI. If you need to create your own hooks anyway beside GAPI, why use GAPI at all? Better have one kind of 'hooking' (i.e. with a dynamically loaded library). > But definitely, there is a set of common requirements among a variety of > replication systems. Moreover, such requirements are also useful to > other systems as well. So far, they are only partly usable for replication. I never looked into materialized views or anything else that would benefit from such triggers. Regards Markus
On Wed, Aug 09, 2006 at 07:33:35AM +0000, Markus Schiltknecht wrote: > Hello Alfranio, > > alfranio correia junior wrote: > >Of course not... > >It is impossible to build a replication system entirely by only using > >triggers... > > Hm, I don't think it's impossible, just unpractical. Anyway, if you say > so yourself, I really doubt the use of GAPI. If you need to create your > own hooks anyway beside GAPI, why use GAPI at all? Better have one kind > of 'hooking' (i.e. with a dynamically loaded library). Why reinvent the wheel for everything if there was an interface that offered some of the needed functionality? Maybe PostgreSQL-R is simply too deep in the database for any of this to be useful, but I'm 99% certain that Slony could make use of some of this stuff, such as a hook on tuples being written out. Likewise, if there were hooks for WAL records and a way to inject WAL info into a backend it probably wouldn't be too hard to build WAL-based replication on top of that. Heck, PITR could probably be refactored to use such hooks. One of the great things about Oracle is that they expose a hell of a lot of the technology they use to build features like replication; ie: take a look at DBMS_*. > >But definitely, there is a set of common requirements among a variety of > >replication systems. Moreover, such requirements are also useful to > >other systems as well. > > So far, they are only partly usable for replication. I never looked into > materialized views or anything else that would benefit from such triggers. > > Regards > > Markus > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Ühel kenal päeval, K, 2006-08-09 kell 13:56, kirjutas Jim C. Nasby: > On Wed, Aug 09, 2006 at 07:33:35AM +0000, Markus Schiltknecht wrote: > > Hello Alfranio, > > > > alfranio correia junior wrote: > > >Of course not... > > >It is impossible to build a replication system entirely by only using > > >triggers... > > > > Hm, I don't think it's impossible, just unpractical. Anyway, if you say > > so yourself, I really doubt the use of GAPI. If you need to create your > > own hooks anyway beside GAPI, why use GAPI at all? Better have one kind > > of 'hooking' (i.e. with a dynamically loaded library). > > Why reinvent the wheel for everything if there was an interface that > offered some of the needed functionality? Maybe PostgreSQL-R is simply > too deep in the database for any of this to be useful, but I'm 99% > certain that Slony could make use of some of this stuff, such as a hook > on tuples being written out. Slonys problems ar *not* in getting access to tuples written out. Why fix something that aint broken ? > Likewise, if there were hooks for WAL > records and a way to inject WAL info into a backend it probably wouldn't > be too hard to build WAL-based replication on top of that. Heck, PITR > could probably be refactored to use such hooks. What would it buy us ? > One of the great things about Oracle is that they expose a hell of a lot > of the technology they use to build features like replication; ie: take > a look at DBMS_*. Being an OSS project, postgresql exposes *everything* it has ;) BTW, do you know how I can get something equivalent to postgresqls snapshot from oracle ? I know one case when people had to do a really lot of unefficient work to find out which transactions had committed during an interval in oracle. > > >But definitely, there is a set of common requirements among a variety of > > >replication systems. Moreover, such requirements are also useful to > > >other systems as well. > > > > So far, they are only partly usable for replication. I never looked into > > materialized views or anything else that would benefit from such triggers. > > > > Regards > > > > Markus > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On Wednesday 09 August 2006 20:57, Hannu Krosing wrote: > > > > Why reinvent the wheel for everything if there was an interface that > > offered some of the needed functionality? Maybe PostgreSQL-R is simply > > too deep in the database for any of this to be useful, but I'm 99% > > certain that Slony could make use of some of this stuff, such as a hook > > on tuples being written out. > > Slonys problems ar *not* in getting access to tuples written out. Why > fix something that aint broken ? The current implementation of GAPI on PostgreSQL uses the same approach as Slony-I to extract the write-set. The difference is that it can push them out immediatly on commit, as required for eager update. -- Jose Orlando Pereira
> > Why reinvent the wheel for everything if there was an interface that > offered some of the needed functionality? Maybe PostgreSQL-R is simply > too deep in the database for any of this to be useful, but I'm 99% > certain that Slony could make use of some of this stuff, such as a hook > on tuples being written out. Likewise, if there were hooks for WAL > records and a way to inject WAL info into a backend it probably wouldn't > be too hard to build WAL-based replication on top of that. Heck, PITR > could probably be refactored to use such hooks. > Yeah !!! :-) The idea is to provide means to inject things at different levels (statement, parsed statements, plans, tuples, logs -wal). So the GAPI provides a generic and standard interface that enables any application to retrieve information at different levels and inject information at different levels. > One of the great things about Oracle is that they expose a hell of a lot > of the technology they use to build features like replication; ie: take > a look at DBMS_*. > If I am not wrong such procedures are only for administrative purpose. For instance, we cannot insert things in a queue to be replicated.
On Aug 10, 2006, at 12:29 PM, alfranio correia junior wrote: >> One of the great things about Oracle is that they expose a hell of >> a lot >> of the technology they use to build features like replication; ie: >> take >> a look at DBMS_*. >> > If I am not wrong such procedures are only for administrative purpose. > For instance, > we cannot insert things in a queue to be replicated. No... things like DBMS_PIPE are just general tools that can be used for anything. My point is that Oracle found they needed functionality like that do provide a feature they were writing (such as replication), and said "Well, if we need this, there's probably others that need it too", so they made it all available. Granted, they may use some internal interface to it that's faster, but they still provided it to everyone. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461