ENABLE_WITH_CLAUSE_MATERIALIZATION-hint and CURRENT_TRANS_ID()

gzzz

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?

marcothesane

How do you materialize a WITH clause?
By creating a temp table behind the scenes.
A CREATE 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 ...

gzzz

DDL statements cause an implicit COMMIT

I was guessing, but did not clarify.
Have to use existing temporary tables.

Thank you.

gzzz

And my version is: Vertica Analytic Database v10.0.0-3.