Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015

    Unanswered: One statement is blocking other statement (two different sessions)


    I would need help to understand what's going wrong internally and why postgres is locking two different statements which is not linked at all.

    Postgres Version: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit


    User 1: First Session
    CREATE TABLE  user1_tb1 as select user_id,count(1) FROM Test1 group by user_id;

    User 1: Second Session

    CREATE TABLE user1_tbl2 as select * FROM Test10;
    Second statement is waiting for first session's statement to finish. There is no link between two tables.

    SELECT     AS blocked_pid,
             blocked_activity.usename  AS blocked_user,
        AS blocking_pid,
             blocking_activity.usename AS blocking_user,
             blocked_activity.query    AS blocked_statement,
             blocking_activity.query   AS blocking_statement,
             blocking_locks.mode   AS blocking_mode
       FROM  pg_catalog.pg_locks         blocked_locks
        JOIN pg_catalog.pg_stat_activity blocked_activity  ON =
        JOIN pg_catalog.pg_locks         blocking_locks 
            ON blocking_locks.locktype = blocked_locks.locktype
            AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
            AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
            AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
            AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
            AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
            AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
            AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
            AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
            AND !=
        JOIN pg_catalog.pg_stat_activity blocking_activity ON =
       WHERE NOT blocked_locks.granted;
    blocked_user : user2
    blocking_user : user1
    blocked_Statement : CREATE TABLE user1_tbl2 as select * FROM Test10;
    blocking statement: CREATE TABLE user1_tb1 as select user_id,count(1) FROM Test1 group by user_id;
    blocking mode: AccessShareLock

    If we don't use GROUP BY clause in first statement then it doesn't blocks second statement. Is this something related to temporary files/sorting?. There is no default temp_tablespace is specified at database level.

    Any ideas what's going wrong?

    Last edited by mamajiwala; 03-23-15 at 19:04.

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Both statements are creating a table with the same name. As it is not allowed to have two tables with the same name, the second has to wait until it knows whether the first commits or rolls back.

    If the first one commits, the second one results in an error. If the first one does a rollback, the second one succeeds.

    It's the same as two statements inserting the same value for a primary key: the database needs to hold the second statement until it's clear if will violate the primary key constraint.

    Please learn to properly format SQL statements when posting using [code] tags
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

  3. #3
    Join Date
    Mar 2015
    Thanks for your reply. It was my typo mistake. Actually in second session, table name is different.

    User 1: Second Session
    CREATE TABLE user1_tbl2 as select * FROM Test10;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts