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?
