1

I have a two databases: oracle & postgres. SymmetricDS is syncing the tables in two way directions. It seems that everything is working. But I need a logic based on triggers that corrects the inserted/updated rows "on the fly". This triggers must be located in the oracle DB.

The algorithm should be as follows:

  1. User inserts new line to the PostgreSQL.
  2. SymmetricsDS replicate this line to the Oracle.
  3. Oracle's triggers converts some columns "on the fly" to the new one.
  4. This converted line must be returned from oracle to postgres back.
  5. I'm creating last AFTER trigger (based on the FOLLOWS statement) on oracle that inserts into SYM_DATA table this renewed line.
  6. I'm waiting that SymmetricsDS will be replicate this corrected line to the postgres back. But nothing happened. Postgres line is old style, such as inserted by user, without oracle's trigger logic.

What am I doing wrong? What else can I do to implement my algorithm?

user3103137
  • 125
  • 2
  • 7
antropka
  • 21
  • 2
  • Are you having one central Oracle database and several PostgreSql client databases? – Boris Pavlović Mar 19 '18 at 15:09
  • I have one Oracle and one Postgres. Both - central databases. – antropka Mar 20 '18 at 08:09
  • antropka: For #5, that should be possible. You could try manually touching your row from a SQL editor (ie, choose a field to modify to trigger a sync from Oracle > Postgres), and if that syncronizes ok, observe the sym_data entry created by SymmetricDS and compare it to the sym_data entry that your custom trigger is creating. – Mark Michalek Mar 21 '18 at 17:16
  • I tried to do so, there is no result. I'm writing the "ref_trigger" trigger, which itself inserts row into the SYM_DATA table 1. I do UPDATE in Postgres 2. Symmetrics moves the row to Oracle 3. Triggers "on the fly" are triggered inside the Oracle 4. Trigger "ref_trigger" is triggered, which inserts the "U" = "UPDATE" row into SYM_DATA 5. This row should fly back to Postgres. Symmetrics must do this. 6. But this does not happen. Apparently Symmetrics is afraid of loops and stops the replication process. Then the question is this: How do you get Symmetrics to make a loop and cycling? – antropka Apr 16 '18 at 13:18

1 Answers1

0

Write your own implementation of org.jumpmind.symmetric.io.data.writer.DatabaseWriterFilterAdapter at Oracle DB's side. This interceptor will do the job of the step 3 (and step 5).

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148
  • I'm already set `sync_on_incoming_batch` to "1". This is necessary for two way replication. I made a mistake in the condition of the problem. Should be: "2) SymmetricsDS replicate this line to the **oracle**" – antropka Mar 20 '18 at 08:07
  • I'm decompiled this class. It's a program stub. It's pretty hard to write a program from scratch. Can you give advice how to write the trigger in step 5 ? – antropka Mar 20 '18 at 13:42
  • Well, you'll need some knowledge in programming Java and Spring framework to perform this. It's not that hard. Give it a try – Boris Pavlović Mar 20 '18 at 13:48
  • Is the method beforeWrite(DataContext context, Table table, CsvData data) for determine needing to move this row? How to fill the parameter DataContext context ? – antropka Apr 17 '18 at 08:24
  • I recommend to write a simple implementation, deploy it, remote device and figure out what goes where – Boris Pavlović Apr 17 '18 at 08:44
  • I implemented this method: " public boolean beforeWrite(DataContext context, Table table, CsvData data) { BufferedWriter bw = null; try { bw = new BufferedWriter(new FileWriter("d:\\SymmetricDS\\log\\mike.log",true)); bw.write(System.currentTimeMillis() + "mike1\n"); //bw.close(); } catch (IOException e1) { e1.printStackTrace(); } finally { try { bw.close(); } catch (IOException e2) { e2.printStackTrace(); } } return false; }" – antropka May 10 '18 at 13:55
  • Replication works, but nothing gets to the log file. This means that the method beforeWrite() is never called. – antropka May 10 '18 at 14:00
  • You'll have to add it to symmetricds extensions in spring configuration – Boris Pavlović May 10 '18 at 18:52