1

In my database, there is a table that provides no result whenever I issue a query through the command line.

For example, if I type:

select * from <table>

Nothing happens. The terminal stops responding until I press ^C to cancel the request.

All the other tables work fine, I am not sure what is causing this error. It just started happening out of nowhere.

flutterbug98
  • 111
  • 2
  • 11
  • What happens with `select * from limit 1;` - with `limit 1` and with semicolon?
     – Erwin Brandstetter Apr 23 '20 at 02:06
  • The terminal just sits there with no response. – flutterbug98 Apr 23 '20 at 02:08
  • Then please add your version of Postgres and psql, the exact name and definition of the table and the result of `SELECT pg_size_pretty(pg_relation_size(''));` Also, any concurrent activity? There may be locks ... Look at: `SELECT * FROM pg_stat_activity;` and `SELECT * FROM pg_locks;`
     – Erwin Brandstetter Apr 23 '20 at 02:11
  • I have posted a solution that I believe solves the problem. Please let me know if I have made any mistake. – flutterbug98 Apr 23 '20 at 02:18
  • Just to be sure: you do use a trailing `;` to end the statement? –  Apr 23 '20 at 06:19

1 Answers1

1

Okay, I found a way to solve it.

First run:

select * from pg_locks where not granted;

Then, from that table, find the relation id. It should be in the column under relation.

Then run this command:

select * from pg_locks where relation = <relationid>;

After that, determine the pid of the relation that has granted equal to true. There should be a granted column with boolean t and f values. Determine that relation's pid.

Finally, run:

select pg_terminate_backend(<pid>);

Then, you should now be able to access the table.

flutterbug98
  • 111
  • 2
  • 11
  • Start with `select * from pg_locks where relation = ''::regclass;`. And also have a look at `pg_stat_activity` to check what the session with `pid = X` actually does before you terminate it ... If it's "idle in transaction", someone / something started a transaction and left it in limbo without commit / rollback. May be something to investigate.
     – Erwin Brandstetter Apr 23 '20 at 02:25
  • A `select` can only be blocked, by an exclusive lock on the table. The connection holding that lock most probably issued a DDL statement that wasn't committed. –  Apr 23 '20 at 06:21