Thread: Does export operation cancel SQLs?
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
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.
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.
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?
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.
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.
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 requestDoes 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.
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? Theload 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.
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!
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
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
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:
timestamp:
timestamp:
Waiting processes get cancelled:
In the case, all the waiting pid (663355, 675442, 675407) got cancelled and the messages are:
textPayload:
timestamp:
timestamp:
timestamp:
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
> 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.
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.
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: 675181Locks observed:Soon after I started noticing:timestamp:timestamp:Waiting processes get cancelled:In the case, all the waiting pid (663355, 675442, 675407) got cancelled and the messages are:textPayload:timestamp:timestamp:timestamp:
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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.
"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