From: Craig Ringer <craig@postnewspapers.com.au>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: spotluri@ismartpanache.com, Adonias Malosso <malosso@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best practice to load a huge table from ORACLE to PG
Date: Sun, 27 Apr 2008 02:51:55 +0800
Message-ID: <481379CB.2010800@postnewspapers.com.au> (raw)
In-Reply-To: <48134B38.4050807@commandprompt.com>
References: <9603.1209223048@ismartpanache.com>
<48134B38.4050807@commandprompt.com>
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/sample_code/tech/java/jsp/oracle9ijsp.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, jd@commandprompt.com, malosso@gmail.com
Subject: Re: Best practice to load a huge table from ORACLE to PG
In-Reply-To: <481379CB.2010800@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox