You might want to examine the SKIP LOCKED feature as well, if you are using this query to have multiple workers grab chunks of the table to work on concurrently.
Cheers,
Greg
--
Enterprise Postgres Software Products & Tech Support
Hi
We are facing issues with session blocking
e3scoring=> \d+ competition_category Table "e3scoring.competition_category" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+------------- id | character varying(36) | | not null | | extended | | | name | character varying | | | | extended | | | short_name | character varying | | | | extended | | | sport_id | character varying(36) | | | | extended | | | competitions | jsonb | | | | extended | | | sort_factor | real | | | | plain | | | brand_id | character varying(36) | | not null | | extended | | | created_at | timestamp without time zone | | | now() | plain | | | modified | timestamp without time zone | | | | plain | | | version | integer | | not null | 0 | plain | | | Indexes: "competition_category_pk" PRIMARY KEY, btree (id) "unique_name_brand_sport" UNIQUE CONSTRAINT, btree (name, brand_id, sport_id) Foreign-key constraints: "competition_category_fk" FOREIGN KEY (brand_id) REFERENCES brand(brandid) Access method: heap Options: fillfactor=75
select "version", competitions from competition_category cc where name = $1 and brand_id = $2 and sport_id = $3 FOR UPDATE
Is there any best approach to optimize this with out sessions blocking most of the time while running mentioned query?
Regards
Durga Mahesh
Hi
Does this work in better way please check once
WITH c AS ( SELECT * FROM competition_category WHERE name = $1 AND brand_id = $2 AND sport_id = $3 ) SELECT "version", competitions FROM c FOR UPDATE;
(OR)
SELECT "version", competitions FROM competition_category WHERE id = ( SELECT id FROM competition_category WHERE name = $1 AND brand_id = $2 AND sport_id = $3 ) FOR UPDATE;
(OR)
UPDATE competition_category SET version = version WHERE name = $1 AND brand_id = $2 AND sport_id = $3 RETURNING "version", competitions;
I am not a data developer.Was getting this multiple sources
Regards
Durga Mahesh
