3

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins.

Since it was taking too long, I cancelled it.

Now I'm trying to run a query to count records from the inner join but it does not end.

Query:

select
    count(oa.*)
from schema.table1 oa
inner join schema.table2 au on
    oa.authorizationid = oa.authorizationid
inner join schema.table3 tx on
    oa.authorizationid = tx.authorizationid
where
    au.reconciliationid is null
    and tx.idstatus = 8
    and tx.idtransactiontype = 10

When I execute the above query to check what is locked:

select
    nspname,
    relname,
    l.*
from
    pg_locks l
join pg_class c on
    (relation = c.oid)
join pg_namespace nsp on
    (c.relnamespace = nsp.oid)
where
    pid in (
        select pid
    from
        pg_stat_activity
    where
        datname = 'op')

This is the output that I've see that looks strange for me.

|  nspname   |              relname              | locktype | database | relation | virtualtransaction |  pid  |      mode       | granted | fastpath |
|------------|-----------------------------------|----------|----------|----------|--------------------|-------|-----------------|---------|----------|
| schema     | uk_table1_transactionid_idtxtype  | relation |    16403 |   151357 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | table1_accountid_idx              | relation |    16403 |   146396 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | ix_table1_updateddate             | relation |    16403 |    23903 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | ix_table1_transactionid           | relation |    16403 |    23902 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | ix_uala_table2_updateddate        | relation |    16403 |   145863 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | ix_uala_table2_authorizationid    | relation |    16403 |   145325 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | uk_uala_table2_authorizationid    | relation |    16403 |   140867 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | uk_uala_table3_authorizationid    | relation |    16403 |   145877 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | ix_uala_table3_authorizationid    | relation |    16403 |   145876 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | table1                            | relation |    16403 |    23894 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | table2                            | relation |    16403 |   140858 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| schema     | table3                            | relation |    16403 |   140869 | 175/46338          |  5770 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_namespace_oid_index            | relation |    16403 |     2685 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_namespace_nspname_index        | relation |    16403 |     2684 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_class_tblspc_relfilenode_index | relation |    16403 |     3455 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_class_relname_nsp_index        | relation |    16403 |     2663 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_class_oid_index                | relation |    16403 |     2662 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_stat_activity                  | relation |    16403 |    11797 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_namespace                      | relation |    16403 |     2615 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_class                          | relation |    16403 |     1259 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_locks                          | relation |    16403 |    11695 | 232/42921          | 15545 | AccessShareLock | "true"  | "true"   |
| pg_catalog | pg_authid_rolname_index           | relation |        0 |     2676 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |
| pg_catalog | pg_authid                         | relation |        0 |     1260 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |
| pg_catalog | pg_authid_oid_index               | relation |        0 |     2677 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |
| pg_catalog | pg_database_oid_index             | relation |        0 |     2672 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |
| pg_catalog | pg_database                       | relation |        0 |     1262 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |
| pg_catalog | pg_database_datname_index         | relation |        0 |     2671 | 232/42921          | 15545 | AccessShareLock | "true"  | "false"  |

Would someone provide me guidance on how to unlock this query?

Edit: Executed query shared by a_horse_with_no_name:

pg_blocking_pids|datid|datname|pid  |usesysid|usename|application_name        |client_addr|client_hostname|client_port|backend_start      |xact_start         |query_start        |state_change       |wait_event_type|wait_event|state |backend_xid|backend_xmin|
----------------|-----|-------|-----|--------|-------|------------------------|-----------|---------------|-----------|-------------------|-------------------|-------------------|-------------------|---------------|----------|------|-----------|------------|
{}              |16403|op     |24848|   16401|ualadba|pgAdmin 4 - DB:op       |172.31.3.43|               |      60876|2019-02-27 11:28:57|                   |2019-02-27 12:11:35|2019-02-27 12:11:35|Client         |ClientRead|idle  |           |            |
{}              |16403|op     | 6364|   16401|ualadba|DBeaver 5.3.4 - Main    |172.31.3.43|               |      39550|2019-02-27 11:42:20|2019-02-27 12:11:35|2019-02-27 12:11:35|2019-02-27 12:11:35|               |          |active|           |301686131   |
{}              |16403|op     | 6379|   16401|ualadba|DBeaver 5.3.4 - Metadata|172.31.3.43|               |      39552|2019-02-27 11:42:21|                   |2019-02-27 12:10:30|2019-02-27 12:10:30|Client         |ClientRead|idle  |           |            |
{}              |16403|op     |24957|   16401|ualadba|pgAdmin 4 - CONN:8010398|172.31.3.43|               |      60878|2019-02-27 11:29:03|                   |2019-02-27 11:55:11|2019-02-27 12:10:12|Client         |ClientRead|idle  |           |            |

Thank you.

Improve this question
8
  • All of the locks are granted, so no query is waiting for anything. To find sessions that are actually blocked, use: SELECT * FROM pg_stat_activity where pg_blocking_pids(pid) <> '{}'
    – user1822
    Commented Feb 27, 2019 at 14:39
  • Thank you for your edit and comment @a_horse_with_no_name. I've added the output of your query. Last record is my query.
    – nariver1
    Commented Feb 27, 2019 at 15:05
  • Can you add pg_blocking_pids(pid) to the output? select *, pg_blocking_pids(pid) from ... then you can control which session is blocking your queries
    – user1822
    Commented Feb 27, 2019 at 15:07
  • Done, it seems that there are no pid blocking. Am I right?
    – nariver1
    Commented Feb 27, 2019 at 15:12
  • Correct - which also means you didn't run the query with the where pg_blocking_pids(pid) <> '{}' condition. It seems that query is just incredibly slow.
    – user1822
    Commented Feb 27, 2019 at 15:15

0

Reset to default

Your Answer

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