[resolved] Multiple Sub Jobs in the Same Transaction

[Anonymous]

Hi,
I'm trying to use TOS 4.2.2 to migrate some tables from mssql to postgresql.
I'm using simple JDBC components to do this. I also need to disable foreign key constraints in postgres during the transaction.
I built a father to do this (see Screenshot-1.png):
- It opens connections to the two dbs and registers each as a shared connection (see Screenshot-3.png) with auto commit disabled to do everything under the same transaction (see Screenshot-2.png).
- Runs a tJDBCRow component to disable all constraints check until end of transaction (see Screenshot.png).
- Then runs child job (see Screenshot-5.png) that:
- Opens connections to the two dbs using the registered connection with auto commit disabled (see Screenshot-3.png).
- Migrates the tables.
- If there is an error in any of the child processes it should roll back and close the connections, otherwise it commits and closes the connections.
I'm getting an error about foreign keys violation even though I've run the tJDBCRow. An example is pasted below.
I assume this is because the sub jobs for some reason are not under the same big transaction. How do I get to do everything under the same flow so I can get rid of the errors?

example of exception:
Starting job MigrationTool at 14:26 07/07/2011.


connecting to socket on port 3518
connected
Exception in component tJDBCOutput_134
org.postgresql.util.PSQLException: ERROR: update or delete on table "image_templates" violates foreign key constraint "image_map" on table "sample_image_map"
Detail: Key (it_guid)=(00000000-0000-0000-0000-000000000000) is still referenced from table "help_image_map".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
at migration_tool.copytables_0_1.CopyTables.tJDBCInput_120Process(CopyTables.java:47772)
at migration_tool.copytables_0_1.CopyTables.runJobInTOS(CopyTables.java:48977)
at migration_tool.copytables_0_1.CopyTables.runJob(CopyTables.java:48129)
at migration_tool.migrationtool_1_0.MigrationTool.tRunJob_1Process(MigrationTool.java:1021)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCRow_1Process(MigrationTool.java:885)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCConnection_1Process(MigrationTool.java:775)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCConnection_2Process(MigrationTool.java:668)
at migration_tool.migrationtool_1_0.MigrationTool.runJobInTOS(MigrationTool.java:1884)
at migration_tool.migrationtool_1_0.MigrationTool.main(MigrationTool.java:1661)

!!Here goes lots of more exceptions!!
Exception in component tRunJob_1
java.lang.RuntimeException: Child job running failed
at migration_tool.migrationtool_1_0.MigrationTool.tRunJob_1Process(MigrationTool.java:1039)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCRow_1Process(MigrationTool.java:885)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCConnection_1Process(MigrationTool.java:775)
at migration_tool.migrationtool_1_0.MigrationTool.tJDBCConnection_2Process(MigrationTool.java:668)
disconnected
at migration_tool.migrationtool_1_0.MigrationTool.runJobInTOS(MigrationTool.java:1884)
at migration_tool.migrationtool_1_0.MigrationTool.main(MigrationTool.java:1661)
Job MigrationTool ended at 14:26 07/07/2011.

[Anonymous]

No one know how to help me?

[Anonymous]

Hi
You want all the sub jobs to be executed multi thread execution? I see you put all sub jobs in child job without any connector.
Maybe you can move the tJDBCRow to child job, eg:
tJDBCConnection_1
|
onsubjobok
|
tJDBCConnection_2
|
onsubjobok
|
tJDBCRow
|
onsubjobok
|
tJDBCInput--main--tJDBCOutput
...
Best regards
Shong

[Anonymous]

Yes, I want a multi thread execution, if possible.
I tried to add connectors and the tJDBCRow and both failed.

Yaniv

[Anonymous]

Hi,
I solved this issue. First there is a bug in the code generation that although I uncheck auto-commit the connection autocommit setting was true so I had to to add the code:

conn_tJDBCConnection_1.setAutoCommit(false);

that caused all the sub jobs to run under the same transaction. I opened a bug on this in this link: http://www.talendforge.org/bugs/view.php?id=23244.
Second I had to disable all foreign key during the session and I did that by entering:

SET session_replication_role to 'replica';

In postgres foreign key are implemented as triggers, so this line cause all triggers which are not defined as replica or checked always to be disabled.
In most cases this does the trick.
Hope this will help someone else.

[Anonymous · Accepted solution]

I used tJDBCRow to enter the pl/pgsql to disable foreign keys.

[Anonymous]

Hi
Glad to see that you find out the problem by yourself, cool! Thanks for your workaround and reporting!
Best regards
Shong