Re: Does export operation cancel SQLs?

It's quite reasonable that pg_dump would block a TRUNCATE statement.

Two suggestions:
1. Add more threads to run the pg_dump faster,
2, Reschedule the TRUNCATE command so that it doesn't run when the pg_dump
is running.

On Wed, Feb 19, 2025 at 3:46 PM Siraj G <tosiraj(dot)g(at)gmail(dot)com> wrote:

> Hello Everyone!
>
> I did a little bit of digging in the logs. Here is the observation:
>
> Export start timestamp:
> "2025-02-19 05:29:16.911 UTC [675181]: [2-1]
> db=postgres,user=cloudsqladmin LOG: connection authorized:
> user=cloudsqladmin database=postgres application_name=pg_dump SSL enabled
> (protocol=TLSv1.3, cipher=TLS_AES_128_GCM_SHA256, bits=128)"
>
> The process is: *675181*
>
> *Locks observed:*
> Soon after I started noticing:
> "2025-02-19 05:31:38.856 UTC [663355]: [483-1] db=postgres,user=postgres
> DETAIL: Process holding the lock: 675181. Wait queue: 663355."
> timestamp: "2025-02-19T05:31:38.857111Z"
>
> "2025-02-19 05:32:04.030 UTC [675442]: [22-1] db=postgres,user=postgres
> DETAIL: Process holding the lock: 675181. Wait queue: 675442, 675407."
> timestamp: "2025-02-19T05:32:04.030807Z"
>
>
> *Waiting processes get cancelled:*
> In the case, all the waiting pid (663355, 675442, 675407) got cancelled
> and the messages are:
>
> textPayload: "2025-02-19 05:32:33.040 UTC [675442]: [30-1]
> db=postgres,user=postgres ERROR: canceling statement due to user request"
> timestamp: "2025-02-19T05:32:33.040612Z"
>
> "2025-02-19 05:32:33.040 UTC [675442]: [31-1] db=postgres,user=postgres
> STATEMENT: truncate "gcp_dms_fc129ac2e00c01c5"."t_e136e66f7f9bab10_8""
> timestamp: "2025-02-19T05:32:33.040645Z"
> "2025-02-19 05:32:33.042 UTC [675442]: [32-1] db=postgres,user=postgres
> LOG: disconnection: session time: 0:00:30.074 user=postgres
> database=postgres host=[local]"
> timestamp: "2025-02-19T05:32:33.042884Z"
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!