ERROR, Message: DDL statement interfered with this statement, Sqlstate: 55006
Hello everyone!
Recently we've faced a problem and i want to ask is that "error" an issue or that's ok (like it should be)))
Here is what's happening:
We have one heavy SQL like this "CREATE newtbl AS SELECT tbl1.col1, tbl2.col2 FROM tbl1 LEFT JOIN tbl2 USING id; " (in fact it's more complicated=))
usualy It takes about 2 hours for table creation.
But also we have another parallel ETL process in which we are creating temp table with new data and then swapping prod table:
"ALTER TABLE tbl2, tbl2_tmp, tmp
RENAME TO tmp, tbl2, tbl2_tmp;"
Actually we are changing tbl2 data set (altering with ddl statement) while querying it.
Most interesting thing is that we don't get any errors on the client side (like transaction stop, rollback or smthng), it works as usual. Instead we get only error in Vertica logs:
QueryError: Severity: ERROR, Message: DDL statement interfered with this statement, Sqlstate: 55006, Routine: finalizePlan, File: /scratch_a/release/svrtar17271/vbuild/vertica/Optimizer/LocalPlanner/LoadPlanner.cpp, Line: 214
Our CREATE TABLE AS SELECT still working, but execution time increasing (in twice)
So probably our CREATE transaction is restarting when source dataset (tbl2) changing (and restarts every time when it changes by ALTER command). Transaction start time is also resetting in "query events" system view.
If this situation is ok, could we get any "workaroud" somehow? Or we just must not do source table alters while querying it?)) I thought that Vertica's isolation level rules must protect us from this situation.
I also cannot find any description of this ERROR in Vertica documentation!...it is sadly.
And i cannot set manual "block" on any table object (like mysql "SELECT FOR UPDATE") to prevent this situation.
Thanks in advance!