[PostgreSQL] Identifying locked objects

my client called me. Help I m doing big insert on partitionned tables and it s running very low.

Beside that the statement is faster during the midnight.

You want to increase the max_lock_per_transactions : 

PostgreSQL: You might need to increase max_locks_per_transaction (cybertec-postgresql.com)

You want some tips to deal with locks 

When Postgres blocks: 7 tips for dealing with locks (citusdata.com)

You want to detect the locks : 

Postgresql : détecter efficacement les locks – Blog | SETRA-Conseil

You want to reduce the AccessShareLock : 

postgres=# begin;    
BEGIN    
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());    
INSERT 0 1    
postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());    
INSERT 0 1    
postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());    
INSERT 0 1    
postgres=# end;    
COMMIT   

How Does PostgreSQL Implement Batch Update, Deletion, and Insertion? – Alibaba Cloud Community

Explaination : 
Before you did not close well your connections, the locks stay active until you don’t close your transactions with end ; 

Indeed my client didnot use a BEGIN… END;  statement

And during midnight we had some admin jobs who closed the idle process.

Other solution is to kill the « idle » connections if the client doesn’t change anything….

Solution 1 : 

Write several batchs : 

batch 1

BEGIN

insert 1 on table 1

insert 2  on table 2 

loop of insert .. on table 3

END;

batch 2

BEGIN

insert 1 on table 1

insert 2 on table 2 

loop of insert .. on table 3

END;

BEGIN

insert 1 on table 1

insert 2 on table 2

loop of insert .. on table 3

END;

etc …

Solution 2 : 

Increase the max_lock_per_transactions

Solution 3 : 

Detect the idle connections and kill them.

SELECT oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, object_type, DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE (v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lmode)) mode_held FROM v$locked_object v, dba_objects d, v$lock l, v$session s WHERE v.object_id = d.object_id AND v.object_id = l.id1 AND v.session_id = s.sid ORDER BY oracle_username, session_id;

https://emarcel.com/identifyandkill/