GitHub: exasol/etl-utils

Contributor

Hello!

May I sugged a slight modification of the query-wrapper tool which I find very handy! Thanks for your work, saves me a lot of time. ๐Ÿ‘

You are trying to fetch the job-id from the main-log table but without filtering on the script_name, this could lead to issues if you have several applications using the same logging table.

-- Step 2) retrieve max ELT_RUN_ID
local success, res = pquery( [[SELECT MAX( run_id ) FROM ::MAIN_LOG_TABLE]], {MAIN_LOG_TABLE = main_log_table} )

so would you pleas extend this to something like that:

-- Step 2) retrieve max ELT_RUN_ID
local success, res = pquery( [[SELECT MAX( run_id ) FROM ::MAIN_LOG_TABLE WHERE SCRIPT_NAME = ::SN]], {MAIN_LOG_TABLE = main_log_table, SN = script_name } )

KR and many thanks!

Michael

 

1 ACCEPTED SOLUTION

Team Exasol
Team Exasol

Ah, I understand -- Exasol's transaction system will prevent this scenario, as the initial INSERT into the main log table write-locks that table until the transaction is ended through rollback or commit, which happens *after* the SELECT in question. (There is no auto-commit within a lua script)

That transaction system is also the main reason why the query wrapper goes through so much pain to collect log messages locally before actually inserting them into the detail log table.

 

Current implementation makes pretty sure that the generated RUN_ID is unique across any and all concurrent calls to the query_wrappper.

View solution in original post

4 REPLIES 4

Team Exasol
Team Exasol

Also thanks for the positive feedback!

Regarding your request, could you elaborate why consecutive RUN_ID values per job/script are important to you?

In the current implementation, the RUN_ID serves as sole foreign/primary key between the logging tables, so compromising it would not be a good idea.

Contributor

Hello Stefan!

The thing is if you have more than one application logging into the same log-tables, one could get the id of the other application and by this, messing the log up. Of course, this could get even more messy by having users calling up the application at the same time and so on.

 

Team Exasol
Team Exasol

Ah, I understand -- Exasol's transaction system will prevent this scenario, as the initial INSERT into the main log table write-locks that table until the transaction is ended through rollback or commit, which happens *after* the SELECT in question. (There is no auto-commit within a lua script)

That transaction system is also the main reason why the query wrapper goes through so much pain to collect log messages locally before actually inserting them into the detail log table.

 

Current implementation makes pretty sure that the generated RUN_ID is unique across any and all concurrent calls to the query_wrappper.

View solution in original post

Community Manager
Community Manager

Thanks for the suggestion, I notify our team to have a look at the post

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...