Postgres table queries not responding

Question

I have been trying to truncate a table using SQlWorkbench. Suddenly, SqlWorkbench got freezed, while the truncate was in progress. I had to kill workbench from taskmanager. But now none of the queries are working on the table on which the truncate was aborted abruptly. For other tables queries are working fine. Need help, as I have to upload fresh data on the same table. Currently I am not even able to drop the table. What can be done to resolve this issue?

You can try query pg_stat_activity and check whether the truncate command still running or not. — Soni Harriz
– Soni Harriz, Commented Feb 14, 2018 at 9:13

Laurenz Albe · Accepted Answer · 2018-02-14 09:14:10Z

5

This looks like the TRUNCATE got stuck behind a lock, and then you killed the front end, while TRUNCATE kept running.

Connect to the database as superuser and examine the pg_stat_activity view; you should see some long running transactions.

Use the function pg_terminate_backend to kill these sessions by their pid.

answered Feb 14, 2018 at 9:14

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cikatomo Over a year ago

how exaclty do you call pg_stat_activity?

Collectives™ on Stack Overflow

Postgres table queries not responding

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related