vdk-core: db_connection_decorate_operation and db_connection_recover_operation hooks for different plugins should not interfere with each other

[DeltaMichael]

Overview

When two or more database plugins are installed, the db_connection_decorate_operation and db_connection_recover operation hooks for all of them are run inside managed_cursor.py. This happens regardless of the db_default_type set.

For example, if you have vdk-oracle and vdk-impala installed, the managed cursor will execute the above hooks for both plugins. If the db_default_type=oracle, then the oracle cursor will try to execute impala queries. This will obviously fail.

How to reproduce

1. Setup

pip install quickstart-vdk
pip install vdk-oracle
pip install vdk-impala

2. Run a data job with the following config

[vdk]
db_default_type = oracle
oracle_user = <user>
oracle_password = <password>
oracle_connection_string = <connection_string>
log_execution_result = True
ingest_method_default = ORACLE

3. Job should fail and output should have a stack trace coming from impala

2023-12-13 11:59:39,285 [VDK] oracle-test-job [ERROR] vdk.internal.builtin_plugins.r          data_job.py:316  run             [id:7b8740f7-5ebd-41a5-b440-f5e20c43ef42-1702461578-c34b3]- ORA-00922: missing or invalid option
Help: https://docs.oracle.com/error-help/db/ora-00922/
Traceback (most recent call last):
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/run/data_job.py", line 304, in run
    raise execution_result.exception
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/run/data_job.py", line 75, in run_step
    step_executed = step.runner_func(step, context.job_input)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/run/file_based_step.py", line 57, in run_sql_step
    job_input.execute_query(sql)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/run/job_input.py", line 126, in execute_query
    return connection.execute_query(query)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/plugin/oracle/oracle_connection.py", line 55, in execute_query
    return super().execute_query(query)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/managed_connection_base.py", line 120, in execute_query
    cur.execute(query)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/managed_cursor.py", line 92, in execute
    self._decorate_operation(managed_operation, operation)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/managed_cursor.py", line 146, in _decorate_operation
    errors.report_and_rethrow(errors.ResolvableBy.PLATFORM_ERROR, e)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/core/errors.py", line 214, in report_and_rethrow
    raise exception
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/managed_cursor.py", line 134, in _decorate_operation
    self.__connection_hook_spec.db_connection_decorate_operation(
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/pluggy/_hooks.py", line 493, in __call__
    return self._hookexec(self.name, self._hookimpls, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/pluggy/_manager.py", line 115, in _hookexec
    return self._inner_hookexec(hook_name, methods, kwargs, firstresult)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/pluggy/_callers.py", line 113, in _multicall
    raise exception.with_traceback(exception.__traceback__)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/pluggy/_callers.py", line 77, in _multicall
    res = hook_impl.function(*args)
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/plugin/impala/impala_plugin.py", line 155, in db_connection_decorate_operation
    decoration_cursor.execute("SET SYNC_DDL=True")
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/decoration_cursor.py", line 130, in execute
    raise e
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/decoration_cursor.py", line 126, in execute
    super().execute(operation, parameters)
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/vdk/internal/builtin_plugins/connection/pep249/interfaces.py", line 64, in execute
    return self._cursor.execute(operation)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mdilyan/Projects/versatile-data-kit/projects/vdk-plugins/vdk-oracle/venv/lib/python3.11/site-packages/oracledb/cursor.py", line 383, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 132, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 420, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 421, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 414, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-00922: missing or invalid option
Help: https://docs.oracle.com/error-help/db/ora-00922/

Acceptance criteria

1. Come up with a mechanism to prevent hooks from interfering with each other
2. Sync mechanism with team
3. Implement and test mechanism

[DeltaMichael]

Problem and solution outline https://github.com/vmware/versatile-data-kit/wiki/db_connection_decorate_operation-and-db_connection_recover_operation-hooks

[DeltaMichael]

https://vmware.slack.com/archives/C03V3E89FB3/p1708074460466119

[DeltaMichael]

Follow-up tickets added to milestone https://github.com/vmware/versatile-data-kit/milestone/32