node ace db:truncate throws the error deadlock detected

[mdsadiqueinam]

Package version

@adonisjs/lucid@20.5.1

Describe the bug

node ace db:truncate throws the error deadlock detected but when I run all truncate queries from Datagrip console it runs successfully

Reproduction repo

No response

Proof of error

Screencast.2024-05-13.16.14.16.mp4

[mdsadiqueinam]

Hi @thetutlage 👋
Added the proof of error in the description of this issue

[diego-sepulveda-lavin]

I'm experiencing the same issue with node ace db:truncate and setup: [() => testUtils.db().truncate()]

I've tested the same database schema in both postgres and mysql (around 40 tables). While everything works fine in mysql and tables get truncated without problems, I'm encountering the issue with the truncate command inpostgres.

Specifically, the command finds a deadlock in postgres, and I believe this is due to the way Lucid executes the TRUNCATE tablename CASCADE concurrently for all tables.

Basically, the command will get all tablenames from the DB and then will execute Promise.all[table_a, table_b,...]

Depending on the table names and the relations between them, you might get a deadlock (image table_c) if its parents (table_a and table_b) are getting truncated at the same time, because they will be locking their children.

I've also tested what OP mentions (running truncate sequentially inside a for loop) and the issue is gone, but the best solution in my opinion would be what PostgreSQL documentation here mentions, where using a single TRUNCATE command that includes all the tables is recommended, which would avoid the locking issue.
I have tested TRUNCATE table_a, table_b, table_c, ... [other options]; and it works perfectly!

[Dyoma3]

I'm having the same issue with running group.setup(() => testUtils.db().truncate()) on a test.group.

[RomainLanz]

Hey all! 👋🏻

@diego-sepulveda-lavin, may you send a PR with a failing test and a change in the PSQL dialect to use only one TRUNCATE call?

[thetutlage]

Yeah, a PR will be appreciated

[mrsafalpiya]

I'm having the same issue with running group.setup(() => testUtils.db().truncate()) on a test.group.

Can confirm. I'm having the same issue when using Postgresql.