[QPROF] Error on second execution with same key_id

[yakirgb]

Hi,
If running two executions with same key_id,
First run is working well, but second run failing.

from verticapy.performance.vertica import QueryProfilerInterface

qprof = QueryProfilerInterface( 
    "SELECT AAA..."
    target_schema='tests',
    key_id='yakir_1',
    )
Until here all good.

Now running second run on same key_id and failing with error:

qprof = QueryProfilerInterface( 
    "SELECT BBB..."
    target_schema='tests',
    key_id='yakir_1',
    )

{
	"name": "QueryError",
	"message": "No transaction with transaction_id=180143985157491893 and statement_id=2 was found in the v_internal.dc_requests_issued table.",
	"stack": "---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:1405, in QueryProfiler._set_request(self)
   1404 print(f\"Debug: Result for transaction_id={tr_id} and statement_id={st_id}: {res}\")
-> 1405 self.requests += [res[0]]
   1406 self.request_labels += [res[1]]

TypeError: 'NoneType' object is not subscriptable

During handling of the above exception, another exception occurred:

QueryError                                Traceback (most recent call last)
Cell In[3], line 3
      1 from verticapy.performance.vertica import QueryProfilerInterface
----> 3 qprof = QueryProfilerInterface( 
      4     \"SELECT BBB...;\",
      6     target_schema='tests',
      7     key_id='yakir_1',
     10     )

File ~/git/VerticaPy/verticapy/performance/vertica/qprof_interface.py:49, in QueryProfilerInterface.__init__(self, transactions, key_id, resource_pool, target_schema, overwrite, add_profile, check_tables)
     39 def __init__(
     40     self,
     41     transactions: Union[None, str, list[int], list[tuple[int, int]]] = None,
   (...)
     47     check_tables: bool = True,
     48 ) -> None:
---> 49     super().__init__(
     50         transactions,
     51         key_id,
     52         resource_pool,
     53         target_schema,
     54         overwrite,
     55         add_profile,
     56         check_tables,
     57     )
     59     self.apply_tree = widgets.Checkbox(
     60         value=False, description=\"Apply tree\", disabled=False, indent=False
     61     )
     62     self.tree_style = {}

File ~/git/VerticaPy/verticapy/_utils/_sql/_collect.py:244, in save_verticapy_logs.<locals>.func_prec_save_logs(*args, **kwargs)
    241 json_dict = {**json_dict, **kwargs}
    242 save_to_query_profile(name=name, path=path, json_dict=json_dict)
--> 244 return func(*args, **kwargs)

File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:992, in QueryProfiler.__init__(self, transactions, key_id, resource_pool, target_schema, overwrite, add_profile, check_tables)
    990 if conf.get_option(\"print_info\"):
    991     print(\"Setting the requests...\")
--> 992 self._set_request()
    994 # SETTING THE queries durations.
    995 if conf.get_option(\"print_info\"):

File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:1408, in QueryProfiler._set_request(self)
   1406         self.request_labels += [res[1]]
   1407     except TypeError:
-> 1408         raise QueryError(
   1409             f\"No transaction with transaction_id={tr_id} \"
   1410             f\"and statement_id={st_id} was found in the \"
   1411             \"v_internal.dc_requests_issued table.\"
   1412         )
   1413 self.request = self.requests[self.transactions_idx]

QueryError: No transaction with transaction_id=180143985157491893 and statement_id=2 was found in the v_internal.dc_requests_issued table."
}

On tests.qprof_dc_requests_issued_yakir_1 is see the transaction_id and statement_id from first run only.

yakir.g=> select transaction_id,statement_id from tests.qprof_dc_requests_issued_yakir_1 ;
-[ RECORD 1 ]--+-------------------
transaction_id | 117093590374573354
statement_id   | 2
Time: First fetch (1 row): 885.258 ms. All rows formatted: 885.330 ms

The second query exists in v_internal.dc_requests_issued

Also the error should be with the new table name tests.qprof_dc_requests_issued_yakir_1 and not v_internal.dc_requests_issued because the row exists in v_internal.dc_requests_issued
Maybe on second run with same key not doing insert/merge of data from dc tables to new schema ?

I'm on version '1.0.1-1—02122024-a01a4cae8ae2976baadab65fc72d9a256cf1ebdc'

Thank you,
Yakir Gibraltar
Taboola

[oualib]

@yakirgb can you please try to see if the same problem appears with the latest changes?

[yakirgb]

Hi Badr,
I hope i'm looking on correct changes, but it's failing with same error,
Tested on '1.0.1-1—02262024-70cfcb2e6f8681fe736c69b4f336970d36c62420'
First run worked :

from verticapy.performance.vertica import QueryProfilerInterface

qprof_interface = QueryProfilerInterface( 
    "select * from v_monitor.QUERY_EVENTS limit 1;",
    target_schema='tests',
    key_id='yakir_1',
    overwrite=True
    )

Second run without overwrite failed:

from verticapy.performance.vertica import QueryProfilerInterface

qprof_interface = QueryProfilerInterface( 
    "select * from v_monitor.QUERY_EVENTS limit 1;",
    target_schema='tests',
    key_id='yakir_1',
    )


{
	"name": "QueryError",
	"message": "No transaction with transaction_id=81064793308072833 and statement_id=57 was found in the v_internal.dc_requests_issued table.",
	"stack": "---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:1423, in QueryProfiler._set_request(self)
   1418 res = _executeSQL(
   1419     query,
   1420     title=\"Getting the corresponding query\",
   1421     method=\"fetchrow\",
   1422 )
-> 1423 self.requests += [res[0]]
   1424 self.request_labels += [res[1]]

TypeError: 'NoneType' object is not subscriptable

During handling of the above exception, another exception occurred:

QueryError                                Traceback (most recent call last)
Cell In[7], line 3
      1 from verticapy.performance.vertica import QueryProfilerInterface
----> 3 qprof_interface = QueryProfilerInterface( 
      4     \"select * from v_monitor.QUERY_EVENTS limit 1;\",
      5     target_schema='tests',
      6     key_id='yakir_1',
      7     )

File ~/git/VerticaPy/verticapy/performance/vertica/qprof_interface.py:49, in QueryProfilerInterface.__init__(self, transactions, key_id, resource_pool, target_schema, overwrite, add_profile, check_tables)
     39 def __init__(
     40     self,
     41     transactions: Union[None, str, list[int], list[tuple[int, int]]] = None,
   (...)
     47     check_tables: bool = True,
     48 ) -> None:
---> 49     super().__init__(
     50         transactions,
     51         key_id,
     52         resource_pool,
     53         target_schema,
     54         overwrite,
     55         add_profile,
     56         check_tables,
     57     )
     59     self.apply_tree = widgets.Checkbox(
     60         value=False, description=\"Apply tree\", disabled=False, indent=False
     61     )
     62     self.tree_style = {}

File ~/git/VerticaPy/verticapy/_utils/_sql/_collect.py:244, in save_verticapy_logs.<locals>.func_prec_save_logs(*args, **kwargs)
    241 json_dict = {**json_dict, **kwargs}
    242 save_to_query_profile(name=name, path=path, json_dict=json_dict)
--> 244 return func(*args, **kwargs)

File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:1011, in QueryProfiler.__init__(self, transactions, key_id, resource_pool, target_schema, overwrite, add_profile, check_tables)
   1009 if conf.get_option(\"print_info\"):
   1010     print(\"Setting the requests...\")
-> 1011 self._set_request()
   1013 # SETTING THE queries durations.
   1014 if conf.get_option(\"print_info\"):

File ~/git/VerticaPy/verticapy/performance/vertica/qprof.py:1426, in QueryProfiler._set_request(self)
   1424         self.request_labels += [res[1]]
   1425     except TypeError:
-> 1426         raise QueryError(
   1427             f\"No transaction with transaction_id={tr_id} \"
   1428             f\"and statement_id={st_id} was found in the \"
   1429             \"v_internal.dc_requests_issued table.\"
   1430         )
   1431 self.request = self.requests[self.transactions_idx]

QueryError: No transaction with transaction_id=81064793308072833 and statement_id=57 was found in the v_internal.dc_requests_issued table."
}

[oualib]

@yakirgb can you please try with the latest pull?

[yakirgb]

Issue resolved, thank you, now getting the following clear warning:

/Users/yakir.g/git/VerticaPy/verticapy/performance/vertica/qprof.py:950: Warning: Parameter 'transactions' is not None, 'key_id' is defined and parameter 'overwrite' is set to False. It means you are trying to use a potential existing key to store new transactions. This operation is not yet supported. A new key will be then generated.
warnings.warn(warning_message, Warning)