public inbox for [email protected]  

help / color / mirror / Atom feed
From: Durgamahesh Manne <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Regarding query optimisation (select for update)
Date: Mon, 16 Feb 2026 06:08:41 +0530
Message-ID: <CAJCZko+o7JsmkY02tf2QHfLVaykORKoWXT5facBuAUJqSOBkcA@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoJWLPdM223+n1dLKBR27LFPt_8uVXTzuZAg0DWGNXjzVw@mail.gmail.com>
References: <CAJCZko+fiU+=NSEGY7d7YMpFxg_jANpiO-LU6ur+yqRc_Ej6eQ@mail.gmail.com>
	<[email protected]>
	<CAJCZkoLJ6G9J3iK591o35UaKu3x7Kn96ma-u_yqGNgkrLjf6ag@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAKAnmmK5mJh+3_p5i41UVV41M_dr9wLz-m7c=V2O343X-MDWfw@mail.gmail.com>
	<CAJCZkoJWLPdM223+n1dLKBR27LFPt_8uVXTzuZAg0DWGNXjzVw@mail.gmail.com>

On Mon, 16 Feb, 2026, 05:56 Durgamahesh Manne, <[email protected]>
wrote:

>
>
> On Tue, 15 Jul, 2025, 20:21 Greg Sabino Mullane, <[email protected]>
> wrote:
>
>> 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
>>
>> --
>> Crunchy Data - https://www.crunchydata.com
>> 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

>


reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Regarding query optimisation (select for update)
  In-Reply-To: <CAJCZko+o7JsmkY02tf2QHfLVaykORKoWXT5facBuAUJqSOBkcA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox