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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Ü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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend