Repopulating Structure Tables

I am using JChem Base with a Postgres database to store a single structure table. This structure table is periodically appended to, using a "UpdateHandler.INSERT" command. This seems to work fine to grow the table.

The problem is that I need to periodically "refresh" all of the molecules in the structure table (to reflect any changes in the source dataset, which is extracted from another database). The way I have been doing is by issuing an UpdateHandler.dropStructureTable() call to destroy the table and then UpdateHandler.createStructureTable() and then I create an UpdateHandler object of type "INSERT " and a series of "setStructure" and "execute" commands. This usually works, but occasionally there is a problem and the net effect is that I cannot query the structure table any more - the table exists but SQL cannot interact with it - as if the table is somehow "locked". When I look at the tables in Postgres, it seems that jchemproperties, jchemproperties_cr and the structure table exist, but the "_ul" table is missing. It's as if the dropping/recreating system crashed. When I create my Connection object, I set AutoCommit to "false" and I only commit once all of the UpdateHandler execute() calls are made for all of the molecules.

I was wondering if anyone had any suggestions, please? Is there a more appropriate way to refresh all of the rows (where some of the rows may need to be deleted and some added)? Instead of dropping the table completely, should I instead delete all the rows and then re-insert?

Many thanks!

Hi,

The workflow you described should correctly work. However, are you sure that the table isn't locked (e.g., no searches are running) when the dropStuctureTable() tries to be executed. It could cause that only "_ul" table will be deleted, and later, the createStructureTable() can't create the table because it already exists, consequently, no new "_ul" table is created.  

Best regards,

Krisztina

kvajda wrote:

Hi, The workflow you described should correctly work. However, are you sure that the table isn't locked (e.g., no searches are running) when the dropStuctureTable() tries to be executed. It could cause that only "_ul" table will be deleted, and later, the createStructureTable() can't create the table because it already exists, consequently, no new "_ul" table is created.   Best regards, Krisztina

No, there is no other interaction with the database as the drop and re-insert is performed early in the morning. I'm not sure what the root cause is, but I have now switched to a different system where I no longer drop the table and simple call:

UpdateHandler.deleteRows(connection_handler,"table_name","");

Then re-insert.

Thankyou.