Csaba Nagy wrote:
> On Wed, 2006-11-29 at 15:50, Shoaib Mir wrote:
>   
>> It should be able to replicate to stock PostgreSQL as well but then
>> you will be having problems when replicating Oracle like data types to
>> PostgreSQL as EnterpriseDB is provided Oracle compatible features.
>>     
>
> Hmm, that's true, but that could be handled if the corresponding fields
> are convertible to each other. I'm not sure how slony is working, but if
> it would allow the data to be transmitted in it's text format and not
> binary, the data conversion could work just fine. Our corresponding
> fields in Oracle/postgres are mostly compatible, problems come in for
> boolean types mostly, and that can be solved by creating some implicit
> casts which solve the issue.
>
> So it all boils down on how the data is transferred, text or binary, and
> strictly typed or not...
>   
There are two answers to that (alas)...

- At subscription time, the providers data is copied over en masse using
COPY.  That may not work entirely well with Oracle ;-). 

Presumably one could replace the COPY statements with suitable sets of
INSERTs, albeit at some not-inconsiderable performance costs.  That
would be a not-too-enormous SMOP (Small Matter of Programming).

- Ongoing updates are submitted as ordinary database requests: INSERT,
DELETE, and UPDATE statements.  The contents are all Plain Old Text, so
for sorts of column types that correspond well between Oracle and
PostgreSQL, this shouldn't present any problems. 

Column types that don't correspond will be a problem, of course.  It
would presumably be possible to set up column wrapper functions to do
translations for things that *nearly* correspond.  That would be a
somewhat tricky but, again, not enormous, SMOP.

The tricky part is getting transaction visibility right.  That's the
thing about Slony-I that's "really really clever," and I'm not sure how
it would translate to running a data provider on Oracle.  Look at Jan's
early papers for more details on that; they're in the slony1-engine/doc/
area.