But do we link oracle trigger to postgres trigger ?
i mean :
oracle trigger will take a note of what has been changed .
but then how do we pass those changes to postgres trigger ?
can u suggest any logic or algorithm ?
Regards,
Srikanth k Potluri
+63 9177444783(philippines)
On Sat 26/04/08 8:40 PM , "Joshua D. Drake" jd@commandprompt.com
sent:
Adonias Malosso wrote:
> Hi All,
>
> I�d like to know what�s the best practice to LOAD a 70 milion
rows, 101
> columns table
> from ORACLE to PGSQL.
>
> The current approach is to dump the data in CSV and than COPY it
to
> Postgresql.
>
> Anyone has a better idea.
Write a java trigger in Oracle that notes when a row has been
added/delete/updated and does the exact same thing in postgresql.
Joshua D. Drake
>
>
> Regards
> Adonias Malosso
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org [1])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Links:
------
[1]
http://sitemail7.hostway.com/javascript:top.opencompose(\'pgsql-performance@postgresql.org\',\'\',\'\',\'\')

Potluri Srikanth wrote:
> But do we link oracle trigger to postgres trigger ?
>
> i mean :
>
> oracle trigger will take a note of what has been changed .
> but then how do we pass those changes to postgres trigger ?

I am assuming you can use the java trigger from oracle to load the
postgresql jdbc driver, make a connection to postgresql and perform
whatever statement needed to be done.

Sincerely,

Joshua D. Drake

P.S. It is possible that Oracle can't do this (I don't know)

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Joshua D. Drake wrote:
> Potluri Srikanth wrote:
>> But do we link oracle trigger to postgres trigger ?
>>
>> i mean :
>>
>> oracle trigger will take a note of what has been changed .
>> but then how do we pass those changes to postgres trigger ?
>
> I am assuming you can use the java trigger from oracle to load the
> postgresql jdbc driver, make a connection to postgresql and perform
> whatever statement needed to be done.

Note that this will be rather inefficient if you're obtaining a new
connection every time. It looks like Oracle's Java stored procedures and
triggers run in an appserver-like environment, though, so you should be
able to use a connection pool, JNDI, or similar.

Some Java stored procedure examples:

http://www.oracle.com/technology/sam...acle9ijsp.html

You could also use a Java trigger to send simpler change message, with a
serialized row if required, to an external app that's responsible for
updating the PostgreSQL database. That might cause less load on the DB
server.

The trouble with this approach, though, is that it might be hard to get
right when transactions roll back. An alternative is to use an Oracle
trigger that inserts records in a change tracking / audit table. You can
then periodically read and clear the audit table, using that change
history data to update the PostgreSQL database. This method has the
advantage of being transaction safe, as data will never become visible
in the audit table until the transaction making the changes has committed.

--
Craig Ringer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance