1

I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class and the indexes on pg_class are locked, lock type is relation and mode is RowExclusiveLock. I can add a column to a table but cannot create an index on a table.

If I create a table in another database, there are no locks on pg_class.

Every hint can help.

Here there is description of locks:

relname                             locktype    mode    
pg_class_tblspc_relfilenode_index   relation    RowExclusiveLock
pg_class_relname_nsp_index          relation    RowExclusiveLock
pg_class_oid_index                  relation    RowExclusiveLock
pg_class                            relation    RowExclusiveLock
Improve this question
4
  • 1
    Could you please show the relevant content of pg_class and pg_lock, and what exactly you are doing? Also, there should be something in the logs, too, if the statement is waiting for longer. Also, do you see the process in pg_stat_activity that holds the lock?
    – dezso
    Jul 17, 2019 at 12:15
  • I ran create table dba.arie2 (i int); The locks are: relname pid locktype database relation page tuple virtualxid transactionid classid objid objsubid virtualtransaction pid mode granted fastpath pg_class_tblspc_relfilenode_index 24757 relation 16384 3455 5/14 24757 RowExclusiveLock TRUE TRUE pg_class_relname_nsp_index 24757 relation 16384 2663 5/14 24757 RowExclusiveLock TRUE TRUE pg_class_oid_index 24757 relation 16384 2662 5/14 24757 RowExclusiveLock TRUE TRUE pg_class 24757 relation 16384 1259 5/14 24757 RowExclusiveLock TRUE TRUE
    – user115223
    Jul 17, 2019 at 12:55
  • 1
    Please edit your post with these details, it is hard to parse this from the comment.
    – dezso
    Jul 17, 2019 at 14:47
  • Thanks. I'll do it in the original question.
    – user115223
    Jul 18, 2019 at 10:21

1 Answer 1

Reset to default
1

Normal operations don't place a persistent lock on pg_class.

There must be a DDL command (CREATE TABLE, DROP TABLE or similar) that is blocked because of a lock, so that it cannot complete the operation.

Check for active sessions in pg_stat_activity, their query and if they are hanging.

Your CREATE TABLE is not blocked by a table lock, but by a row lock in pg_class.

Improve this answer
6
  • I tried to find the locking DDL, but I didn't see ant process/
    – user115223
    Jul 18, 2019 at 11:56
  • You see nothing in pg_locks? You could add the results to the question, maybe that tells us something. Does the problem persist, i.e., does CREATE TABLE still hang?
    – Laurenz Albe
    Jul 18, 2019 at 14:35
  • I see only the locks on pg_class. What do you mean by "the result". I am stuck, I don't get any results. Thanks !
    – user115223
    Jul 21, 2019 at 5:46
  • Could you look at the pg_stat_activity row for the hanging session and see what the wait event is?
    – Laurenz Albe
    Aug 5, 2019 at 5:47
  • wait_event is null. Thanks !
    – user115223
    Aug 6, 2019 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.