Hi, We are trying to load data around 1Bil records into one table with INSERT statements (not able to use COPY command) and they are been waiting for a lock and the wait_event is "transactionid", I didn't find any information in the documents. Queries have been waiting for hours. Table DDL'sCREATE TABLE test_load( billg_acct_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL, accs_mthd_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL, soc character varying(10) COLLATE pg_catalog."default" NOT NULL, soc_desc character varying(100) COLLATE pg_catalog."default", service_type_cd character varying(10) COLLATE pg_catalog."default", soc_start_dt date, soc_end_dt date, product_eff_dt date, product_exp_dt date, curr_ind character varying(1) COLLATE pg_catalog."default", load_dttm timestamp without time zone NOT NULL, updt_dttm timestamp without time zone, md5_chk_sum character varying(100) COLLATE pg_catalog."default", deld_from_src_ind character(1) COLLATE pg_catalog."default", orphan_ind character(1) COLLATE pg_catalog."default", CONSTRAINT test_load_pk PRIMARY KEY (billg_acct_cid_hash, accs_mthd_cid_hash, soc)); query results from pg_locks ; SELECT COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, left(blockeda.query,7) AS blocked_query, blockedl.mode AS blocked_mode, blockinga.pid AS blocking_pid, left(blockinga.query,7) AS blocking_query, blockingl.mode AS blocking_mode FROM pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_locks blockingl ON (blockingl.transactionid = blockedl.transactionid OR blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype) AND blockedl.pid <> blockingl.pid JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted order by blockeda.query_start "transactionid" "18:20:06.068154" 681216 "INSERT " "ShareLock" 679840 "INSERT " "ExclusiveLock""transactionid" "18:19:05.504781" 679688 "INSERT " "ShareLock" 679856 "INSERT " "ExclusiveLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679612 "INSERT " "ShareLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679580 "INSERT " "ShareLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 681108 "INSERT " "ExclusiveLock""transactionid" "18:14:17.969603" 681080 "INSERT " "ShareLock" 681204 "INSERT " "ExclusiveLock""transactionid" "18:13:41.531575" 681112 "INSERT " "ShareLock" 679636 "INSERT " "ExclusiveLock""transactionid" "18:04:16.195069" 679556 "INSERT " "ShareLock" 679776 "INSERT " "ExclusiveLock""transactionid" "17:58:54.284211" 679696 "INSERT " "ShareLock" 678940 "INSERT " "ExclusiveLock""transactionid" "17:57:54.220879" 681144 "INSERT " "ShareLock" 679792 "INSERT " "ExclusiveLock""transactionid" "17:57:28.736147" 679932 "INSERT " "ShareLock" 679696 "INSERT " "ExclusiveLock""transactionid" "17:53:48.701858" 679580 "INSERT " "ShareLock" 679572 "INSERT " "ShareLock"
query results from pg_stat_activity ; SELECT pg_stat_activity.pid, pg_stat_activity.usename, pg_stat_activity.state, now() - pg_stat_activity.query_start AS runing_time, LEFT(pg_stat_activity.query,7) , pg_stat_activity.wait_event FROM pg_stat_activity ORDER BY (now() - pg_stat_activity.query_start) DESC; | | | | | | | | 681216 | postgres | active | 07:32.7 | INSERT | transactionid | | 679688 | postgres | active | 06:32.2 | INSERT | transactionid | | 679572 | postgres | active | 05:44.0 | INSERT | transactionid | | 681080 | postgres | active | 01:44.6 | INSERT | transactionid | | 681112 | postgres | active | 01:08.2 | INSERT | transactionid | | 679556 | postgres | active | 51:42.9 | INSERT | transactionid | | 679696 | postgres | active | 46:20.9 | INSERT | transactionid | | 681144 | postgres | active | 45:20.9 | INSERT | transactionid | | 679932 | postgres | active | 44:55.4 | INSERT | transactionid | | 679580 | postgres | active | 41:15.4 | INSERT | transactionid | | 679400 | postgres | active | 39:51.2 | INSERT | transactionid | | 679852 | postgres | active | 37:05.3 | INSERT | transactionid | | 681188 | postgres | active | 36:23.2 | INSERT | transactionid | | 679544 | postgres | active | 35:33.4 | INSERT | transactionid | | 675460 | postgres | active | 26:06.8 | INSERT | transactionid | select version (); PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit CPU: v32RAM: 320 GBshared_buffers = 64GB effective_cache_size = 160 GB any comments on the issue? Thanks,Rj
