ENABLE_WITH_CLAUSE_MATERIALIZATION-hint and CURRENT_TRANS_ID()
gzzz
Vertica Customer ✭
Hello.
I've founded some strange Vertica behaviour.
Four queries in transaction:
----------------------------- vertica=> BEGIN; BEGIN vertica=> vertica=> SELECT vertica-> 'before' AS context, vertica-> CURRENT_TRANS_ID() AS transaction_id, vertica-> CURRENT_STATEMENT() AS statement_id vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- before | 63050396964579788 | 2 (1 row) vertica=> vertica=> WITH vertica-> test_1 AS ( vertica(> SELECT vertica(> 'test_1' AS context, vertica(> CURRENT_TRANS_ID() AS transaction_id, vertica(> CURRENT_STATEMENT() AS statement_id vertica(> ), vertica-> test_2 AS ( vertica(> SELECT vertica(> 'test_2' AS context, vertica(> CURRENT_TRANS_ID() AS transaction_id, vertica(> CURRENT_STATEMENT() AS statement_id vertica(> ) vertica-> vertica-> SELECT * vertica-> FROM test_1 vertica-> vertica-> UNION ALL vertica-> vertica-> SELECT * vertica-> FROM test_2 vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- test_1 | 63050396964579788 | 3 test_2 | 63050396964579788 | 3 (2 rows) vertica=> vertica=> SELECT vertica-> 'after' AS context, vertica-> CURRENT_TRANS_ID() AS transaction_id, vertica-> CURRENT_STATEMENT() AS statement_id vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- after | 63050396964579788 | 4 (1 row) vertica=> vertica=> ROLLBACK; ROLLBACK -----------------------------
All transaction_id's is the same, OK.
Now, add a ENABLE_WITH_CLAUSE_MATERIALIZATION-hint:
----------------------------- vertica=> BEGIN; BEGIN vertica=> vertica=> SELECT vertica-> 'before' AS context, vertica-> CURRENT_TRANS_ID() AS transaction_id, vertica-> CURRENT_STATEMENT() AS statement_id vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- before | 63050396964580331 | 2 (1 row) vertica=> vertica=> WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ vertica-> test_1 AS ( vertica(> SELECT vertica(> 'test_1' AS context, vertica(> CURRENT_TRANS_ID() AS transaction_id, vertica(> CURRENT_STATEMENT() AS statement_id vertica(> ), vertica-> test_2 AS ( vertica(> SELECT vertica(> 'test_2' AS context, vertica(> CURRENT_TRANS_ID() AS transaction_id, vertica(> CURRENT_STATEMENT() AS statement_id vertica(> ) vertica-> vertica-> SELECT * vertica-> FROM test_1 vertica-> vertica-> UNION ALL vertica-> vertica-> SELECT * vertica-> FROM test_2 vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- test_1 | 63050396964580356 | 1 test_2 | 63050396964580382 | 1 (2 rows) vertica=> vertica=> SELECT vertica-> 'after' AS context, vertica-> CURRENT_TRANS_ID() AS transaction_id, vertica-> CURRENT_STATEMENT() AS statement_id vertica-> ; context | transaction_id | statement_id ---------+-------------------+-------------- after | 63050396964580392 | 1 (1 row) vertica=> vertica=> ROLLBACK; ROLLBACK -----------------------------
Four different transactions.
So, in fact materialization-hint cause a new transaction for every request.
Why it works this way?
Can i change this behavior and have a single transaction with ENABLE_WITH_CLAUSE_MATERIALIZATION?
Best Answers
-
marcothesane
- Select Field - Administrator
How do you materialize a WITH clause?
By creating a temp table behind the scenes.
ACREATE LOCAL TEMPORARY TABLE foo ...is a DDL statement.
DDL statements cause an implicit COMMIT.
A COMMIT ends the current transaction, and opens a new one if you fire other SQL statements.
What you see is a logical consequence of that ...1 -
gzzz
Vertica Customer ✭
DDL statements cause an implicit COMMIT
I was guessing, but did not clarify.
Have to use existing temporary tables.Thank you.
0
Answers
And my version is:
Vertica Analytic Database v10.0.0-3.