Feedback-ID: i76984098:Fastmail
Message-ID: <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com>
Date: Wed, 18 Sep 2024 14:32:48 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: IO related waits
To: veem v <veema0000@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>,
 Christophe Pettus <xof@thebuild.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
 <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com>
 <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
 <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com>
 <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
 <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com>
 <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
 <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/586d988d-978b-4b08-84b4-ef3ebb8736a8%40aklaver.com>
Precedence: bulk


On 9/18/24 1:40 PM, veem v wrote:
> 

>     You were spot on. When we turned off the "auto commit" we started
>     seeing less number of commits as per the number of batches.
> 
>     However we also started seeing deadlock issues. We have foreign key
>     relationships between the tables and during the batch we do insert
>     into the parent first and then to the child , but this does happen
>     from multiple sessions for different batches. So why do we see below
>     error, as we ensure in each batch we first insert into parent and
>     then into the child tables?
> 
>     caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
>        Detail: Process 10443 waits for ShareLock on transaction
>     220972157; blocked by process 10454.
>     Process 10454 waits for ShareLock on transaction 220972155; blocked
>     by process 10443.
>        Hint: See server log for query details.
>        Where: while inserting index tuple (88736,28) in relation
>     "TAB1_p2024_08_29"
> 
> 
> As we are able to get hold of one session, we see "insert into <parent 
> partition table>" was blocked by "insert into <child partition table>". 
> And the "insert into <child partition table> " was experiencing a 
> "client read" wait event. Still unable to understand why it's happening 
> and how to fix it?


This needs clarification.

1) To be clear when you refer to parent and child that is:
                 FK
parent_tbl.fld <--> child_tbl.fld_fk

not parent and child tables in partitioning scheme?

2) What are the table schemas?

3) What is the code that is generating the error?


Overall it looks like this process needs a top to bottom audit to map 
out what is actually being done versus what needs to be done.


-- 
Adrian Klaver
adrian.klaver@aklaver.com