Running catalogue source not deleted correctly from varmetric database

[AntoniaR]

In some cases, TraP creates a new running catalogue source after a new source is detected. This source can then be retrospectively associated with an existing running catalogue source and TraP deletes the source from the database.

Unfortunately, TraP does not seem to be deleting the source correctly from all the associated tables. It seems to be deleted from the running catalogue fine, but not the varmetric catalogue. We need to check that, when a running catalogue source is deleted, it is deleted from all tables that reference it.

The full log is attached to this issue but the relevant part is:

2021-07-15 12:55:25 INFO tkp.main: found 138 blind sources in 1 images
2021-07-15 12:55:26 ERROR tkp.db.database: Query failed: (psycopg2.errors.ForeignKeyViolation) update or delete on table "runningcatalog" violates foreign key constraint "varmetric_runcat_fkey" on table "varmetric"
DETAIL:  Key (id)=(139) is still referenced from table "varmetric".

[SQL: DELETE
  FROM runningcatalog
 WHERE inactive = TRUE
]
(Background on this error at: http://sqlalche.me/e/13/gkpj). Query: DELETE
  FROM runningcatalog
 WHERE inactive = TRUE
.
2021-07-15 12:55:26 ERROR tkp.main: timestep raised <class 'sqlalchemy.exc.IntegrityError'> exception: (psycopg2.errors.ForeignKeyViolation) update or delete on table "runningcatalog" violates foreign key constraint "varmetric_runcat_fkey" on table "varmetric"
DETAIL:  Key (id)=(139) is still referenced from table "varmetric".

[SQL: DELETE
  FROM runningcatalog
 WHERE inactive = TRUE
]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

FirstTry_trap.log

[AntoniaR]

I think the TraP tests also fail due to this error. The relevant code for the TraP test fail is: https://github.com/transientskp/tkp/blob/fa65950f6a8891e97880e483e03206a06e5f130e/tkp/steps/varmetric.py#L6-L25

The TraP tests error message is:

======================================================================
ERROR: test_execute_store_varmetric (test_varmetric.TestApi)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/antoniar/TraP_Develop/tkp/tests/test_steps/test_varmetric.py", line 43, in test_execute_store_varmetric
    execute_store_varmetric(session=session, dataset_id=self.dataset.id)
  File "/home/antoniar/TraP_Develop/tkp/tkp/steps/varmetric.py", line 22, in execute_store_varmetric
    session.execute(delete_)
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1643, in execute
    _parent_execute_state is not None,
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1821, in orm_pre_session_exec
    update_options,
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1949, in _do_pre_synchronize_evaluate
    from_=err,
  File "<string>", line 5, in raise_
InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate Select". Specify 'fetch' or False for the synchronize_session execution option.
-------------------- >> begin captured logging << --------------------
tkp.db.database: INFO: connecting to database...
tkp.db.database: INFO: connected to: postgresql://antoniar@vlo.science.uva.nl:5432/AR_testing
--------------------- >> end captured logging << ---------------------

[AntoniaR]

This also produces another tests error:

======================================================================
ERROR: test_execute_store_varmetric_twice (test_varmetric.TestApi)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/antoniar/TraP_Develop/tkp/tests/test_steps/test_varmetric.py", line 48, in test_execute_store_varmetric_twice
    execute_store_varmetric(session=session, dataset_id=self.dataset.id)
  File "/home/antoniar/TraP_Develop/tkp/tkp/steps/varmetric.py", line 22, in execute_store_varmetric
    session.execute(delete_)
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1643, in execute
    _parent_execute_state is not None,
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1821, in orm_pre_session_exec
    update_options,
  File "/home/antoniar/TraP_Develop_Env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1949, in _do_pre_synchronize_evaluate
    from_=err,
  File "<string>", line 5, in raise_
InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate Select". Specify 'fetch' or False for the synchronize_session execution option.

[AntoniaR]

Interesting the TraP test suite passes completely when using an earlier version of sqlalchemy (see Issue #586). Testing underway to determine if it completely solves this issue.