agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedDoes export operation cancel SQLs?
17+ messages / 8 participants
[nested] [flat]
* Does export operation cancel SQLs?
@ 2025-02-19 08:31 Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Siraj G @ 2025-02-19 08:31 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Experts!
We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we are
noticing these messages excessively:
db=postgres,user=postgres ERROR: canceling statement due to user request
Does Export cancel the SQLs? If not, why are we noticing these messages
excessively during the export operation? The load on the instance is
minimal.
Regards
Siraj
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
@ 2025-02-19 08:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 10:55 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 11:10 ` Re: Does export operation cancel SQLs? shammat@gmx.net
1 sibling, 2 replies; 17+ messages in thread
From: Laurenz Albe @ 2025-02-19 08:43 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2025-02-19 at 14:01 +0530, Siraj G wrote:
> We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we
> are noticing these messages excessively:
>
> db=postgres,user=postgres ERROR: canceling statement due to user request
>
> Does Export cancel the SQLs? If not, why are we noticing these messages
> excessively during the export operation? The load on the instance is minimal.
I am not sure what exactly you mean by "export". A pg_dump?
But I am not aware of any operation that would cancel statements in other
sessions. Perhaps someone or something in your application sends cancel
requests when a statement is running too long, and the concurrent export
slows down the system.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-19 10:55 ` Siraj G <tosiraj.g@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Siraj G @ 2025-02-19 10:55 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Laurenz!
GCP export uses pg_dump internally.
Not sure, but as soon as we stopped the export operation, the errors
stopped.
Regards
Siraj
On Wed, Feb 19, 2025 at 2:13 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2025-02-19 at 14:01 +0530, Siraj G wrote:
> > We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we
> > are noticing these messages excessively:
> >
> > db=postgres,user=postgres ERROR: canceling statement due to user request
> >
> > Does Export cancel the SQLs? If not, why are we noticing these messages
> > excessively during the export operation? The load on the instance is
> minimal.
>
> I am not sure what exactly you mean by "export". A pg_dump?
> But I am not aware of any operation that would cancel statements in other
> sessions. Perhaps someone or something in your application sends cancel
> requests when a statement is running too long, and the concurrent export
> slows down the system.
>
> Yours,
> Laurenz Albe
>
> --
>
> *E-Mail Disclaimer*
> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
> oder
> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
> *CONFIDENTIALITY NOTICE & DISCLAIMER
> *This message and any attachment are
> confidential and may be privileged or otherwise protected from disclosure
> and solely for the use of the person(s) or entity to whom it is intended.
> If you have received this message in error and are not the intended
> recipient, please notify the sender immediately and delete this message
> and
> any attachment from your system. If you are not the intended recipient, be
> advised that any use of this message is prohibited and may be unlawful,
> and
> you must not copy this message or attachment or disclose the contents to
> any other person.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-19 11:10 ` shammat@gmx.net
2025-02-19 11:18 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 17+ messages in thread
From: shammat@gmx.net @ 2025-02-19 11:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Laurenz Albe schrieb am 19.02.2025 um 09:43:
>> We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we
>> are noticing these messages excessively:
>>
>> db=postgres,user=postgres ERROR: canceling statement due to user request
>>
>> Does Export cancel the SQLs? If not, why are we noticing these messages
>> excessively during the export operation? The load on the instance is minimal.
>
> I am not sure what exactly you mean by "export". A pg_dump?
> But I am not aware of any operation that would cancel statements in other
> sessions. Perhaps someone or something in your application sends cancel
> requests when a statement is running too long, and the concurrent export
> slows down the system.
What if the "export" is done on a read-only replica?
Could this be a "canceling statement due to conflict with recovery" in disguise?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 11:10 ` Re: Does export operation cancel SQLs? shammat@gmx.net
@ 2025-02-19 11:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 13:51 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Laurenz Albe @ 2025-02-19 11:18 UTC (permalink / raw)
To: shammat@gmx.net; pgsql-admin@lists.postgresql.org
On Wed, 2025-02-19 at 12:10 +0100, shammat@gmx.net wrote:
> Laurenz Albe schrieb am 19.02.2025 um 09:43:
> > > We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we
> > > are noticing these messages excessively:
> > >
> > > db=postgres,user=postgres ERROR: canceling statement due to user request
> > >
> > > Does Export cancel the SQLs? If not, why are we noticing these messages
> > > excessively during the export operation? The load on the instance is minimal.
> >
> > I am not sure what exactly you mean by "export". A pg_dump?
> > But I am not aware of any operation that would cancel statements in other
> > sessions. Perhaps someone or something in your application sends cancel
> > requests when a statement is running too long, and the concurrent export
> > slows down the system.
>
> What if the "export" is done on a read-only replica?
>
> Could this be a "canceling statement due to conflict with recovery" in disguise?
That would be a different error message.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 11:10 ` Re: Does export operation cancel SQLs? shammat@gmx.net
2025-02-19 11:18 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-19 13:51 ` Siraj G <tosiraj.g@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Siraj G @ 2025-02-19 13:51 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
Agreed, we are not running this operation in the READ replica.
Regards
Siraj
On Wed, Feb 19, 2025 at 4:48 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2025-02-19 at 12:10 +0100, shammat@gmx.net wrote:
> > Laurenz Albe schrieb am 19.02.2025 um 09:43:
> > > > We have PostgreSQL 16 running in GCP cloud SQL. When export job runs
> we
> > > > are noticing these messages excessively:
> > > >
> > > > db=postgres,user=postgres ERROR: canceling statement due to user
> request
> > > >
> > > > Does Export cancel the SQLs? If not, why are we noticing these
> messages
> > > > excessively during the export operation? The load on the instance is
> minimal.
> > >
> > > I am not sure what exactly you mean by "export". A pg_dump?
> > > But I am not aware of any operation that would cancel statements in
> other
> > > sessions. Perhaps someone or something in your application sends
> cancel
> > > requests when a statement is running too long, and the concurrent
> export
> > > slows down the system.
> >
> > What if the "export" is done on a read-only replica?
> >
> > Could this be a "canceling statement due to conflict with recovery" in
> disguise?
>
> That would be a different error message.
>
> Yours,
> Laurenz Albe
>
> --
>
> *E-Mail Disclaimer*
> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
> oder
> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
> *CONFIDENTIALITY NOTICE & DISCLAIMER
> *This message and any attachment are
> confidential and may be privileged or otherwise protected from disclosure
> and solely for the use of the person(s) or entity to whom it is intended.
> If you have received this message in error and are not the intended
> recipient, please notify the sender immediately and delete this message
> and
> any attachment from your system. If you are not the intended recipient, be
> advised that any use of this message is prohibited and may be unlawful,
> and
> you must not copy this message or attachment or disclose the contents to
> any other person.
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
@ 2025-02-19 13:54 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2025-02-19 13:54 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wednesday, February 19, 2025, Siraj G <tosiraj.g@gmail.com> wrote:
> Hello Experts!
>
> We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we
> are noticing these messages excessively:
>
> db=postgres,user=postgres ERROR: canceling statement due to user request
>
> Does Export cancel the SQLs? If not, why are we noticing these messages
> excessively during the export operation? The load on the instance is minimal
>
It says “user request”. I’d advise figuring out what was cancelled and
what “user” did so. Probably DDL commands failing to get exclusive locks.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
@ 2025-02-19 15:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Laurenz Albe @ 2025-02-19 15:00 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2025-02-19 at 06:54 -0700, David G. Johnston wrote:
> > We have PostgreSQL 16 running in GCP cloud SQL. When export job runs we are noticing these messages excessively:
> >
> > db=postgres,user=postgres ERROR: canceling statement due to user request
> >
> > Does Export cancel the SQLs? If not, why are we noticing these messages excessively during the export operation? The load on the instance is minimal
>
> It says “user request”. I’d advise figuring out what was cancelled and what “user” did so.
> Probably DDL commands failing to get exclusive locks.
No, that message is from a cancel request, like when you interrupt your
currently running query with Ctrl+C in "psql" or invoke pg_cancel_backend().
PostgreSQL doesn't do that by itself.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-19 15:42 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:03 ` Re: Does export operation cancel SQLs? Tom Lane <tgl@sss.pgh.pa.us>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Ron Johnson @ 2025-02-19 15:42 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 19, 2025 at 10:00 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2025-02-19 at 06:54 -0700, David G. Johnston wrote:
> > > We have PostgreSQL 16 running in GCP cloud SQL. When export job runs
> we are noticing these messages excessively:
> > >
> > > db=postgres,user=postgres ERROR: canceling statement due to user
> request
> > >
> > > Does Export cancel the SQLs? If not, why are we noticing these
> messages excessively during the export operation? The load on the instance
> is minimal
> >
> > It says “user request”. I’d advise figuring out what was cancelled and
> what “user” did so.
> > Probably DDL commands failing to get exclusive locks.
>
> No, that message is from a cancel request, like when you interrupt your
> currently running query with Ctrl+C in "psql" or invoke
> pg_cancel_backend().
> PostgreSQL doesn't do that by itself.
>
The Linux oom killer? I don't remember the exact error message that PG
gives to the user, but ISTR that it's "user request”. Had to search
through /var/log/messages to see that oomkiller was the culprit.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-02-19 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2025-02-19 16:03 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Wed, Feb 19, 2025 at 10:00 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> No, that message is from a cancel request, like when you interrupt your
>> currently running query with Ctrl+C in "psql" or invoke
>> pg_cancel_backend().
>> PostgreSQL doesn't do that by itself.
> The Linux oom killer? I don't remember the exact error message that PG
> gives to the user, but ISTR that it's "user request”.
No, the OOM killer does "kill -9", which stops the PG process in
its tracks. So no message at all will be issued. You'll get
some kind of "connection lost" message from the connected client,
but for sure that is not going to claim it was a "user request".
I do seem to recall that there are some edge cases where we can't
really tell the source of a SIGINT signal, but there's no reason
to think that any of those cases are in play here. The OP should
be looking first at the other moving parts in his installation
to see what might be issuing a query cancel.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-02-19 16:07 ` Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Jeff Janes @ 2025-02-19 16:07 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 19, 2025 at 10:43 AM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:
> On Wed, Feb 19, 2025 at 10:00 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>>
>> No, that message is from a cancel request, like when you interrupt your
>> currently running query with Ctrl+C in "psql" or invoke
>> pg_cancel_backend().
>> PostgreSQL doesn't do that by itself.
>>
>
> The Linux oom killer? I don't remember the exact error message that PG
> gives to the user, but ISTR that it's "user request”. Had to search
> through /var/log/messages to see that oomkiller was the culprit.
>
OOM killer kills a process with sig 9. This reboots the entire cluster,
and you would get some variant of "server closed the connection
unexpectedly" or "terminating connection because of crash of another server
process". So not a "user request".
Most likely some client (or client library) has an internal timer and
cancels its own query after a certain amount of time.
I know that JDBC's setQueryTimeout operates this way, it sets a client side
timeout which then kicks in to cancel the query by "user request".
The server load caused by an export could cause the other queries to
run long enough for this logic to kick in, when they otherwise would not.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
@ 2025-02-19 20:46 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 20:51 ` Re: Does export operation cancel SQLs? Scott Ribe <scott_ribe@elevated-dev.com>
2025-02-19 20:57 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Siraj G @ 2025-02-19 20:46 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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"
On Wed, Feb 19, 2025 at 9:38 PM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wed, Feb 19, 2025 at 10:43 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Wed, Feb 19, 2025 at 10:00 AM Laurenz Albe <laurenz.albe@cybertec.at>
>> wrote:
>>
>>>
>>> No, that message is from a cancel request, like when you interrupt your
>>> currently running query with Ctrl+C in "psql" or invoke
>>> pg_cancel_backend().
>>> PostgreSQL doesn't do that by itself.
>>>
>>
>> The Linux oom killer? I don't remember the exact error message that PG
>> gives to the user, but ISTR that it's "user request”. Had to search
>> through /var/log/messages to see that oomkiller was the culprit.
>>
>
> OOM killer kills a process with sig 9. This reboots the entire cluster,
> and you would get some variant of "server closed the connection
> unexpectedly" or "terminating connection because of crash of another server
> process". So not a "user request".
>
> Most likely some client (or client library) has an internal timer and
> cancels its own query after a certain amount of time.
> I know that JDBC's setQueryTimeout operates this way, it sets a client
> side timeout which then kicks in to cancel the query by "user request".
>
> The server load caused by an export could cause the other queries to
> run long enough for this logic to kick in, when they otherwise would not.
>
> Cheers,
>
> Jeff
>
>>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
@ 2025-02-19 20:51 ` Scott Ribe <scott_ribe@elevated-dev.com>
2025-02-19 20:56 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Scott Ribe @ 2025-02-19 20:51 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Feb 19, 2025, at 1:46 PM, Siraj G <tosiraj.g@gmail.com> wrote:
>
> Waiting processes get cancelled:
> In the case, all the waiting pid (663355, 675442, 675407) got cancelled and the messages are:
As people have described, clients are timing out and canceling queries.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 20:51 ` Re: Does export operation cancel SQLs? Scott Ribe <scott_ribe@elevated-dev.com>
@ 2025-02-19 20:56 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 22:20 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Siraj G @ 2025-02-19 20:56 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Scott
The sessions that were getting cancelled were from Google DMS. It appears
the google's service is setting some session specific timeout
(lock_timeout) due to which the sessions are getting cancelled.
But I wonder if pg_dump does acquire locks? If so, are these locks on the
dictionary/catalog objects or on the data tables that are getting exported?
Regards
Siraj
On Thu, Feb 20, 2025 at 2:21 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Feb 19, 2025, at 1:46 PM, Siraj G <tosiraj.g@gmail.com> wrote:
> >
> > Waiting processes get cancelled:
> > In the case, all the waiting pid (663355, 675442, 675407) got cancelled
> and the messages are:
>
> As people have described, clients are timing out and canceling queries.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 20:51 ` Re: Does export operation cancel SQLs? Scott Ribe <scott_ribe@elevated-dev.com>
2025-02-19 20:56 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
@ 2025-02-19 22:20 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 22:27 ` Re: Does export operation cancel SQLs? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2025-02-19 22:20 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Feb 19, 2025 at 1:57 PM Siraj G <tosiraj.g@gmail.com> wrote:
> But I wonder if pg_dump does acquire locks?
>
Everything acquires locks (basically). It is the specific nature of the
locks held and requested that matter.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 20:51 ` Re: Does export operation cancel SQLs? Scott Ribe <scott_ribe@elevated-dev.com>
2025-02-19 20:56 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 22:20 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
@ 2025-02-19 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2025-02-19 22:27 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Siraj G <tosiraj.g@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Feb 19, 2025 at 1:57 PM Siraj G <tosiraj.g@gmail.com> wrote:
>> But I wonder if pg_dump does acquire locks?
> Everything acquires locks (basically). It is the specific nature of the
> locks held and requested that matter.
For more color on that see
https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
particularly the table of lock mode conflicts. pg_dump generally
takes ACCESS SHARE locks on the tables it plans to dump, which is
the same lock level taken by a plain SELECT. That will only
block ACCESS EXCLUSIVE locks, which by and large are only taken
by major DDL operations.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Does export operation cancel SQLs?
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` Re: Does export operation cancel SQLs? David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Re: Does export operation cancel SQLs? Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Re: Does export operation cancel SQLs? Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:07 ` Re: Does export operation cancel SQLs? Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Re: Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
@ 2025-02-19 20:57 ` Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Ron Johnson @ 2025-02-19 20:57 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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@gmail.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!
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2025-02-19 22:27 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-19 08:31 Does export operation cancel SQLs? Siraj G <tosiraj.g@gmail.com>
2025-02-19 08:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 10:55 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 11:10 ` shammat@gmx.net
2025-02-19 11:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 13:51 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 13:54 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 15:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-19 15:42 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-19 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-19 16:07 ` Jeff Janes <jeff.janes@gmail.com>
2025-02-19 20:46 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 20:51 ` Scott Ribe <scott_ribe@elevated-dev.com>
2025-02-19 20:56 ` Siraj G <tosiraj.g@gmail.com>
2025-02-19 22:20 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-19 20:57 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox