Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vrmdf-005pMg-1h for pgsql-general@arkaria.postgresql.org; Mon, 16 Feb 2026 00:38:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vrmde-002uON-0h for pgsql-general@arkaria.postgresql.org; Mon, 16 Feb 2026 00:38:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vrmdd-002uOD-2D for pgsql-general@lists.postgresql.org; Mon, 16 Feb 2026 00:38:57 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vrmdb-00000000o2W-07Os for pgsql-general@lists.postgresql.org; Mon, 16 Feb 2026 00:38:56 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-b8849dc12f6so325203366b.1 for ; Sun, 15 Feb 2026 16:38:55 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771202334; cv=none; d=google.com; s=arc-20240605; b=lppDvyTHwAaig4MB3kZzlO7KGTfHhgoClzS5/DYkVbAKVR1PH6mF5yMod4U1Bmcg0U 10aZO17AJ8FVMQvok1rPIL7ghZHCgfIHKuRDmAtFRwbW3RNbltF7xFRJhu3kEN6rXQ8Z YyyjIyn+nSPWFIuBMvfzQ5w3py3XhtyFLoXDTdY+DKOD+yDh2sMYitiPhyqMarWoWTP0 RoiPyn3p7523yNgKxDALyQAk57HOas4qwC1xyuIhbYLtgE/tS35qAZzIZ+JkBGvy/Yqd 1uE3VSKj+VHrqxRHwqioIuKcJra57RbXSSQmFWrRFsDyp/v0XzdSVZx4N+bOI8xPltXJ M3/g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=9fbrHvuEfpbtMM6u1bdcs5TiP7uD57VB0xartSMYECI=; fh=Rkp43EwPKGbLcPB655ZawSnhBAYlnjp+pLZKBj8hj4M=; b=EJnbF/eJBYQ5dqxTiLEwF0LYRM2Vg2Cdhc6QypeOgHmZt8wcmPCqd//U90k3Zv4XjI vNxfvXR6uAnhSpXiTjrRdmPZoH91wi+MopFcQRJL00mQ8v+3QPAEBdP0xexieYSj6K7D vavM4jYi2CQj560b8C1k/J1zq+OFzIHDWMrS2XFla3rfv6/JhzseE5ylqRBMdUxIxppx ZVSO+zr/X+ClDCqiXQN9UiYvqMmkyuSfWOHq0U/q6ju1cEev8hhuOzBk2O3N03XuEvPB ZzzZbfs9GCr8snjKRmC/jUqfeg414ClppeycgXdLFFwEx7SEitKhMVUKU7kZ2ZI9wW/R bprw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771202334; x=1771807134; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=9fbrHvuEfpbtMM6u1bdcs5TiP7uD57VB0xartSMYECI=; b=Ku8+3ea8C7MLLfYD2PjkRJhqBRwxmOZM0Fvw0wICAB4zth48q57y0CLHFWW4q5/qBY vZaeh9FaZimjSA1VwFYRJpZHFF8kIvDCy7iysXoLko1DeMGprg6rIOUEeUfKJLXkMTHY S031Gz7cy4dG0mS5naDmYdR1zIghRow12jMRLfS6g/ulcmMOh2GOAUTwsEMU5gNN/kxO dwDnyJzPTK0Zs/6LzcatjjXyGYNKqd+lD03kYj7LIzuVXXY5RSt5eR06bD38AOzuCeNI +yGrdWJbEVN9OywiyW+N7QHk/UuyQL4cNOLVg9JMh71QHz5O/uYJi7OMHrw+286MnrxD 42AA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771202334; x=1771807134; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=9fbrHvuEfpbtMM6u1bdcs5TiP7uD57VB0xartSMYECI=; b=ka/6vFa2NoIkvJVxHlus7+0M3usObW52XsHjq0pfOpetIIRiBbQBzTW2LkFBDuJvrj iZrnaCqzIClOmXgrPz5aWNHamEGI+HH31J9DQxrkxDWsWiRkRVw60opfwVqvjLwqllhC ANv3bvJchG6T8NT0x3E56mYuPk6/JYgVkfq+/6ao4QvobMIFTlyB/E3yIFi+KUTAOGWl Z2hAbNi5l/5PH4K9eGB5a9vtYX0wCWzi08GSJO5APjwUFe6gVfjwJY2ALM7Zbm0k1Hnx OEv56uKT7NymU9FmGmycaySLQbBD8K2Bj2KusdGG8jbp2Ts42QGd4a1xXXIcU5JKoQNT Pzag== X-Forwarded-Encrypted: i=1; AJvYcCXZcPV3QpacsX/0ls3/7//M77/gsZrWbt1k4Upa7P+Q701YeokncweqwTKwu5m1z5PoTH4xWorgADj1YjHw@lists.postgresql.org X-Gm-Message-State: AOJu0YxBw0X1Owa7SJbk4LN84q68Rl6rWFI0ZNghyiORHctJF7WnlvIe vXC5m93XWHGwODnlnxrZ2BVWQP1k7eK7yQKL0fAailX4D5S2eA79pkF49auaKiN4sSwWu7uEmCp G8WFQY625NqYLdi8MwRjo5OBGhZmBcO4= X-Gm-Gg: AZuq6aKM8qOKtKOJDSDwwM7oNa7RYOKrOPw/uLyg1f1ZEI1EDs75hMWeUXk9C7fdS5i gitkep35S/nX2IAJvn3j8K+w0PfG7PW6PYOXhgEJyDbhW2jb3M/9mdTxcjZUf4SXW1i19gt5vbN psDd9Ysbp4GeGdWq4Vs95epHm3Ok4CWOEJynVFvXXdG0AbjT3XyLfIUkMCr4+NfkHJ5OZwmBcHV //tX7yaE5G2FrjPAyGuleIjzTN8HOBqa/JsTXixWzg2LFdAaD3W/8SZPTwKKNuhtQO+pf098OSv 6MHem/L3 X-Received: by 2002:a17:907:988:b0:b8a:f225:ede8 with SMTP id a640c23a62f3a-b8fb44d63a5mr439601566b.41.1771202334027; Sun, 15 Feb 2026 16:38:54 -0800 (PST) MIME-Version: 1.0 References: <826536fd65cb6b25e996ac449336ce9c2476174b.camel@cybertec.at> <903134.1752590744@sss.pgh.pa.us> In-Reply-To: From: Durgamahesh Manne Date: Mon, 16 Feb 2026 06:08:41 +0530 X-Gm-Features: AaiRm51oyqney5PTyGeBkHZxL3hzfNlHT8uQ0ne1DNtwfdLaBRqo1PNH-0XSaAU Message-ID: Subject: Re: Regarding query optimisation (select for update) To: Greg Sabino Mullane Cc: Tom Lane , Laurenz Albe , pgsql-general Content-Type: multipart/alternative; boundary="000000000000f1f443064ae62ddc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f1f443064ae62ddc Content-Type: text/plain; charset="UTF-8" On Mon, 16 Feb, 2026, 05:56 Durgamahesh Manne, wrote: > > > On Tue, 15 Jul, 2025, 20:21 Greg Sabino Mullane, > 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 > --000000000000f1f443064ae62ddc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, 16 Feb, 2026, 05:56 Durg= amahesh Manne, <maheshpostg= res9@gmail.com> wrote:


On Tue, 15 Jul, 2025, 20:21 Greg Sabino Mullane, <htamfi= ds@gmail.com> wrote:
You might want to examine the SKIP LOCKED featur= e 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 & T= ech Support

Hi

We are facing issues with session blocking=C2=A0
=
e3scoring=3D> \d+ competition_category Table "e3scoring.com= petition_category" Column | Type | Collation | Nullable | Default | St= orage | 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_fac= tor | real | | | | plain | | | brand_id | character varying(36) | | not nul= l | | extended | | | created_at | timestamp without time zone | | | now() |= plain | | | modified | timestamp without time zone | | | | plain | | | ver= sion | integer | | not null | 0 | plain | | | Indexes: "competition_ca= tegory_pk" PRIMARY KEY, btree (id) "unique_name_brand_sport"= UNIQUE CONSTRAINT, btree (name, brand_id, sport_id) Foreign-key constraint= s: "competition_category_fk" FOREIGN KEY (brand_id) REFERENCES br= and(brandid) Access method: heap Options: fillfactor=3D75
=

select "version", competitions from competition_category cc whe= re name =3D $1 and brand_id =3D $2 and sport_id =3D $3 FOR UPDATE

Is there any best approach to opt= imize this with out sessions blocking most of the time=C2=A0 while running = mentioned query?

Regards=
Durga Mahesh

Hi=C2= =A0

Does this work in be= tter way please check once=C2=A0
WITH c AS ( =C2=A0= SELECT * =C2=A0 FROM competition_category =C2=A0 WHERE name =3D $1 =C2= =A0=C2=A0=C2=A0 AND brand_id =3D $2 =C2=A0=C2=A0=C2=A0 AND sport_id =3D $3= ) SELECT "version", competitions FROM c FOR UPDATE;=C2=A0
<= div dir=3D"auto">(OR)=C2=A0
SELECT "version&quo= t;, competitions FROM competition_category WHERE id =3D ( =C2=A0 SELECT id= =C2=A0 FROM competition_category =C2=A0 WHERE name =3D $1 =C2=A0=C2=A0= =C2=A0 AND brand_id =3D $2 =C2=A0=C2=A0=C2=A0 AND sport_id =3D $3 ) FOR UP= DATE;=C2=A0
=C2=A0(OR)=C2=A0
= UPDATE competition_category SET version =3D version WHERE name =3D $1 AND= brand_id =3D $2 AND sport_id =3D $3 RETURNING "version", compe= titions;=C2=A0=C2=A0

I a= m not a data developer.Was getting this multiple sources

Regards=C2=A0
Durga= Mahesh
--000000000000f1f443064ae62ddc--