Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUllu-00010f-5n for pgsql-bugs@arkaria.postgresql.org; Fri, 09 Apr 2021 07:41:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lUllt-0002Fo-3r for pgsql-bugs@arkaria.postgresql.org; Fri, 09 Apr 2021 07:41:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUl18-0005ib-C7 for pgsql-bugs@lists.postgresql.org; Fri, 09 Apr 2021 06:53:24 +0000 Received: from [64.147.123.17] (helo=wnew3-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUl15-00058p-AQ for pgsql-bugs@lists.postgresql.org; Fri, 09 Apr 2021 06:53:21 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailnew.west.internal (Postfix) with ESMTP id 2B7DE15E9 for ; Fri, 9 Apr 2021 02:53:08 -0400 (EDT) Received: from imap36 ([10.202.2.86]) by compute3.internal (MEProxy); Fri, 09 Apr 2021 02:53:08 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fastmail.com; h= mime-version:message-id:date:from:to:subject:content-type; s= fm2; bh=cCG/FdMfC/u3nVs1trkd0oumpXzA1vaV35hol/QYmjM=; b=RQ/MU6WM r4hTiiDdtPn60/BOJoLse2Ibv/bnhABJRE33mT93Ju5qKS9cDiZqKDoKIke4suI9 XrSnEKJdQGETVRsQU3CnAZG63BibiCPCaTadzPN1bh2b9vNIPAAsDqCftp6HywWX gbC/uR0c9flH7utN8jCLY9JluYTQ7iP6DJgkQEUzgDUyNxgioByDPpPqj7+2ncDy khdu5gduPmcky+LpAiIcCDxveXNUzntY3oAJRg6uZ3b4oGuwKEs+rnzA92svUPaN VkPBsBdHJME6cFGtRwGpl2kp72mXJhm5N7MoRdiDY+i3I2ESITb0qIT1AeR/OWi8 vELBjIMJOvsUig== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-type:date:from:message-id :mime-version:subject:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm2; bh=cCG/FdMfC/u3nVs1trkd0oumpXzA1 vaV35hol/QYmjM=; b=PtwLZwcBonm2HZqWYLhDb8EuGlgQk4EPwVg2t1odx8PeE o0IrNaD5xFYoyTC9z2J5m0u9s0PCQmU4GcWPsXJqM8PrQk6fT72r6w0Q89CofRF2 MVGueGQ4130hUrkeMQ2KdC+ay69FrPZVRUD4OTFcXZwVxByLWqdxQHVTNPip2fUU PmHQGxM3GmBKrpC5ZXoAsSc7HtAoJsg7ISwdGJbwf7laRMB5udomaglp/AwalrKT tl5NEwG3ma2eEexwnrBAo/9hyeLzShIej0dyjiokV68UnfYfi0lMpkAH7UO6QFa3 Sg2NUERBk50B4K5haFYEM3rU6DYlLheMS9A/PAABg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduledrudektddgudduudcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepofgfggfkfffhvffutgesrgdtre erreertdenucfhrhhomheptfhomhgrnhcuoehfrghllhgvnhesfhgrshhtmhgrihhlrdgt ohhmqeenucggtffrrghtthgvrhhnpeejleehkeefhfefjedtveeijefgjeefteegieehtd eklefgveevffehgfegffeuudenucffohhmrghinheptghonhhnvggtthhiohhnrdhophgv nhenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehfrg hllhgvnhesfhgrshhtmhgrihhlrdgtohhm X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id 55F0510E0089; Fri, 9 Apr 2021 02:53:07 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.5.0-alpha0-273-g8500d2492d-fm-20210323.002-g8500d249 Mime-Version: 1.0 Message-Id: <33b04d82-830e-484f-bcb3-4c593612754d@www.fastmail.com> Date: Fri, 09 Apr 2021 09:52:10 +0300 From: Roman To: pgsql-bugs@lists.postgresql.org Subject: Update locks the row even if there is no row to update Content-Type: multipart/alternative; boundary=25d952e9d40a44be9813e41a7e1dfcbf X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.147.123.17 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --25d952e9d40a44be9813e41a7e1dfcbf Content-Type: text/plain Hello, Necessary steps to reproduce the problem are: 1. P-SQL preparing: create or replace function generate_random_string( length int, characters text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' ) returns text as $$ declare result text := ''; begin for __ in 1..length loop result := result || substr(characters, floor(random() * length(characters))::int + 1, 1); end loop; return result; end; $$ language plpgsql; create table table_for_update ( id uuid primary key, boolupdate bool not null, somedata text ); insert into table_for_update (id, boolupdate, somedata) select gen_random_uuid(), floor(2*random())::text::bool, generate_random_string (30) from generate_series(1,1000); --The next function gives us an opportunity to update 'boolupdate' and if 'boolupdate' doesn't satisfy the requirement we are waiting some time for another transaction that updates the row to satisfy. create or replace function "UpdateOrWait" ( updid uuid, retry_count int4, retry_timeout int4 ) returns int as $$ declare counter int; begin counter := 0; --The first attempt to update update table_for_update set boolupdate = true where id = updid and boolupdate = false; while not found loop counter := counter + 1; if counter > retry_count then return 1; end if; perform pg_sleep(retry_timeout / 1000.0); update table_for_update set boolupdate = true where id = updid and boolupdate = false; end loop; return 0; end; $$ language PLPGSQL; --We need to take one guid for the following steps. select * from table_for_update order by random() limit 1; 2. The next step we need to do the next code (example from PowerShell): #Open a connection (a disclaimer: it doesn't matter if we open and close this connection every time in a loop) $DBConn = New-Object System.Data.Odbc.OdbcConnection $DBConn.ConnectionTimeout = 0 $DBConn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;Database=test;Uid=postgres;Pwd=postgres;" $DBCmd = $DBConn.CreateCommand() $DBCmd.CommandTimeout = 0 $DBCmd.Connection.Open() #The variable $i shows us the process $i=0 while ($true) { $i+=1 write-host -nonewline "$i;" #displays the counter #The first command call the function $command = "select * from `"UpdateOrWait`" ('dfa58a44-a045-47d1-922a-4fddbc11cc06', 5, 1000);" #The ID is from the last P-SQL output $DBCmd.CommandText = $command $ds = New-Object system.Data.DataSet (New-Object system.Data.odbc.odbcDataAdapter($DBCmd)).fill($ds) | out-null $result = $ds.Tables[0].UpdateOrWait if ($result -ne 0) {write-host -foregroundcolor red "!!!!!!!!!! - $result;" -nonewline} #if it was not successful #the second command makes an Update operation $command = "update table_for_update set boolupdate = false where id = 'dfa58a44-a045-47d1-922a-4fddbc11cc06'" $DBCmd.CommandText = $command $DBCmd.ExecuteNonQuery() | out-null } $DBCmd.Connection.Close() 3. If we use the second step in one PowerShell process everything is OK. However, if we open another process and run the second step simultaneously, from time to time we will get an unsuccessful output. I've looked up at this process and have found that --The first attempt to update update table_for_update set boolupdate = true where id = updid and boolupdate = false; this instruction makes a lock even if there is no row to update: pid blockpid query mode wait_event_type wait_event locktype state granted 2,732 {} select * from "UpdateOrWait" ('dfa58a44-a045-47d1-922a-4fddbc11cc06', 5, 1000); RowExclusiveLock Timeout PgSleep relation active true 20,132 {2732} update table_for_update set boolupdate = false where id = 'dfa58a44-a045-47d1-922a-4fddbc11cc06' ShareLock Lock transactionid transactionid active false -- Best Regards, Roman --25d952e9d40a44be9813e41a7e1dfcbf Content-Type: text/html Content-Transfer-Encoding: quoted-printable
Hello,

Necessary steps to reproduce the problem are:
1. P-SQL preparing:
create or replace function ge= nerate_random_string(
  length int,
&nb= sp; characters text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefg= hijklmnopqrstuvwxyz   '
)
returns = text
as $$
declare
  resu= lt text :=3D '';
begin
  for __ in 1..l= ength loop
    result :=3D result || substr= (characters, floor(random() * length(characters))::int + 1, 1);
  end loop;
  return result;
end; $$
language plpgsql;

cr= eate table table_for_update (
  id uuid primary key,<= br>
  boolupdate bool not null,
  some= data text
);

insert into tabl= e_for_update (id, boolupdate, somedata)
select gen_random_= uuid(), floor(2*random())::text::bool, generate_random_string (30)
from generate_series(1,1000);

--Th= e next function gives us an opportunity to update 'boolupdate' and if 'b= oolupdate' doesn't satisfy the requirement we are waiting some time for = another transaction that updates the row to satisfy.
creat= e or replace function "UpdateOrWait" (
  updid uuid,<= br>
  retry_count int4,
  retry_timeou= t int4
)
returns int
as $$
=
declare
  counter int;
begin=
  counter :=3D 0;

 = ; --The first attempt to update
  update table_for_up= date
  set boolupdate =3D true
  w= here id =3D updid and boolupdate =3D false;

  while not found loop
    counter :=3D co= unter + 1;

    if counter &g= t; retry_count then
      return = 1;
    end if;

&nbs= p;   perform pg_sleep(retry_timeout / 1000.0);
<= br>
    update table_for_update
&= nbsp;   set boolupdate =3D true
  &nbs= p; where id =3D updid and boolupdate =3D false;

=
  end loop;
  return 0;
end; = $$
language PLPGSQL;

--We nee= d to take one guid for the following steps.
select *
from table_for_update
order by random()
=
limit 1;

2. The next step w= e need to do the next code (example from PowerShell):

=
#Open a connection (a disclaimer: it doesn't matter if we ope= n and close this connection every time in a loop)
$DBConn = =3D New-Object System.Data.Odbc.OdbcConnection
$DBConn.Con= nectionTimeout =3D 0
$DBConn.ConnectionString =3D "Driver=3D= {PostgreSQL Unicode(x64)};Server=3Dlocalhost;Port=3D5432;Database=3Dtest= ;Uid=3Dpostgres;Pwd=3Dpostgres;"
$DBCmd =3D $DBConn.Create= Command()
$DBCmd.CommandTimeout =3D 0
$DBCmd= .Connection.Open()

#The variable $i shows u= s the process
$i=3D0
while ($true)
=
{
  $i+=3D1
  write-host -non= ewline "$i;"  #displays the counter

&n= bsp; #The first command call the function
  $command = =3D "select * from `"UpdateOrWait`" ('dfa58a44-a045-47d1-922a-4fddbc11cc= 06', 5, 1000);" #The ID is from the last P-SQL output
&nbs= p; $DBCmd.CommandText =3D $command
  $ds =3D New-Obje= ct system.Data.DataSet
  (New-Object system.Data.odbc= .odbcDataAdapter($DBCmd)).fill($ds) | out-null
  $res= ult =3D $ds.Tables[0].UpdateOrWait
  if ($result -ne = 0) {write-host -foregroundcolor red "!!!!!!!!!! - $result;" -nonewline} = #if it was not successful

  #the secon= d command makes an Update operation
  $command =3D "u= pdate table_for_update set boolupdate =3D false where id =3D 'dfa58a44-a= 045-47d1-922a-4fddbc11cc06'"
  $DBCmd.CommandText =3D= $command
  $DBCmd.ExecuteNonQuery() | out-null
}

$DBCmd.Connection.Close()

3. If we use the second step in one PowerShell pr= ocess everything is OK. However, if we open another process and run the = second step simultaneously, from time to time we will get an unsuccessfu= l output.

I've looked up at this process an= d have found that
  --The first attempt to update
  update table_for_update
  set bool= update =3D true
  where id =3D updid and boolupdate =3D= false;
this instruction makes a lock even if there is no = row to update:
<= colgroup>query
Timeout
=
pid
blockpid
mode
=
wait_= event_type
wait_event
locktype
state
granted
2,732
{}
select * from "UpdateOrWait" ('dfa58a44-a045-47d1-922a-4fddbc11cc06', 5, 1000);
<= /td>
RowExclusiveLock
PgSleep
relation
activ= e
true
20,132
{2732}
update table_for_update set boolupdate =3D false where id =3D 'dfa58a44-a045-47d1-922a-4fddbc11cc06'
ShareLock
Lock
transactionid=
transactionid
active
false

--
Best Regards,
Roman<= br>

--25d952e9d40a44be9813e41a7e1dfcbf--