0

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
  • 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 '19 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 – ariestern Jul 17 '19 at 12:55
  • 1
    Please edit your post with these details, it is hard to parse this from the comment. – dezso Jul 17 '19 at 14:47
  • Thanks. I'll do it in the original question. – ariestern Jul 18 '19 at 10:21
0

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.

  • I tried to find the locking DDL, but I didn't see ant process/ – ariestern Jul 18 '19 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 '19 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 ! – ariestern Jul 21 '19 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 '19 at 5:47
  • wait_event is null. Thanks ! – ariestern Aug 6 '19 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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