Re: Does export operation cancel SQLs?

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

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.g@xxxxxxxxx> 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!