Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to generate a stalled truncate query?

we observed that sometimes truncate queries were not returning without throwing an error. A DROP TABLE command did also not work. Howerver, that returned with an error that other objects depend on it.
We have difficulties devloping a solution that handles stalled truncate queries, because we we not able to reproduce them and test our solution accordingly.
So the question is if someone knows, how we can produce a truncate query that does not return?

Best Answer

  • marcothesanemarcothesane Administrator
    Answer ✓

    A TRUNCATE is a DDL operation, and it requires both an exclusive lock on the truncated table and on the global catalogue. And if it cannot acquire that lock, it waits for 5 minutes, by default. The database parameter LockTimeout, whose default is 300 (seconds) controls that at table level.

    If a table is locked by a transaction, and another transaction tries to truncate it, the second tries to acquire an exclusive lock on that table before proceeding. After five minutes, it times out.

    Try this for a table you can play with. Mine was public.from_kafka:

    • connect once:
    sbx=> lock table public.from_kafka in exclusive mode;
    • connect in another session:
    sbx=> TRUNCATE TABLE public.from_kafka;

    For five minutes, nothing happens.

    sbx=> TRUNCATE TABLE public.from_kafka;
    ROLLBACK 5157:  Unavailable: [Txn 0xa00000002842aa] O lock table - timeout error Timed out O locking Table:public.from_kafka. X held by [user dbadmin (LOCK TABLE)]. Your current transaction isolation level is SERIALIZABLE

    If you repeat the exercise in session 2:

    sbx=> TRUNCATE TABLE public.from_kafka;

    ... and then go back to session 1 and:

    sbx=> lock table public.from_kafka in exclusive mode;
    sbx=> COMMIT; -- <===

    ... you will instantly see this in session 2:

    sbx=> TRUNCATE TABLE public.from_kafka;

    I would just open another session and truncate the table in question in exclusive mode to simulate a stalled truncate attempt.


  • marcothesanemarcothesane Administrator

    Sorry, LOCK public.the_table IN EXCLUSIVE MODE; , NOT truncate.

  • Thanks for this comment! We will definetly tried this approach and let you know. However, I am pretty sure that the query was stalled for more than 300s, so it actually did not seem to return at all. However, I will have a closer look next time this occurs.

  • marcothesanemarcothesane Administrator

    What's your current LockTimeout value?

  • How can I query it? I tried SHOW SESSION PARAMETER ALL, but could not find it.

  • marcothesanemarcothesane Administrator
    marco ~/1/aaa $ vsql -c "SELECT get_config_parameter('LockTimeout')"
  • Thanks! It's the default: 300.

  • We tried your suggestion and it worked nicely! We could now develop and test a solution that handles stalled truncate queries. Thanks!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.