We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


How to overcome "DDL statement interfered with this statement" — Vertica Forum

How to overcome "DDL statement interfered with this statement"

kfruchtmankfruchtman Vertica Customer ✭
in General Discussion

Hi Experts!
I am experiencing many issues with my 10.x vertica.I get a lot of failed queries per day with the reason
"DDL statement interfered with this statement"
what is a good way to deal with this in Vertica?

The backround:
I am pumping data to the db every 5 minutes to a specific table from other databases. so I can imagine that sometimes it is trying to run DDL statements on the same table and this issue is happening.
Is there a way to workaround this problem? e.g - put the queries in some kind of queue in Vertica maybe?

Thanks in advance
Keren

Answers

  • moshegmosheg Vertica Employee Administrator

    Hello Keren,
    An object acquires an O lock (Owner lock) in some DDL operations, such as DROP_PARTITIONS, TRUNCATE TABLE, and ADD COLUMN. When an object is locked in O mode, it cannot be locked simultaneously by another transaction in any mode.
    For many files or bulk-loads it is advised to use one COPY statement and for trickle feed to use INSERT statements.
    An Insert (I) lock is required to insert data into a table and multiple transactions can lock an object in Insert mode simultaneously, enabling multiple inserts and bulk loads to occur at the same time.

  • kfruchtmankfruchtman Vertica Customer ✭

    Hi!
    Thx for the answer.I am still a bit confused and cannot overcome this issue.
    I am using MERGE statements every5 minutes and getting this error.No other transaction is happening at the same time other than a simple select statement.
    So I am not sure how to debug this issue further.Can I get some more hints?
    Keren

  • kfruchtmankfruchtman Vertica Customer ✭

    I am attaching the failed queries log from the vertica mc maybe that can help.
    I think sometimes 2 MERGE statements happen at the same time.should it be the problem?
    file attached.
    Please advise!
    Keren

  • kfruchtmankfruchtman Vertica Customer ✭

    Here I attach the vertica.log, maybe it can help understand whey the DDL error occurres?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...