How to generate a stalled truncate query?

joergschaber

Hi,
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?

marcothesane

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;
LOCK TABLE

• connect in another session:

sbx=> TRUNCATE TABLE public.from_kafka;

For five minutes, nothing happens.
Then:

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
sbx=> 

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;
LOCK TABLE
sbx=> COMMIT; -- <===

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

sbx=> TRUNCATE TABLE public.from_kafka;
TRUNCATE TABLE
sbx=> 

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

marcothesane

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

joergschaber

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.

marcothesane

What's your current LockTimeout value?

joergschaber

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

marcothesane

marco ~/1/aaa $ vsql -c "SELECT get_config_parameter('LockTimeout')"
 get_config_parameter 
----------------------
 300

joergschaber

Thanks! It's the default: 300.

joergschaber

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