Thread: ERROR: canceling query due to user request

ERROR: canceling query due to user request

From
Tore Halset
Date:
Hello.

I am trying to port an old java application from MS SQL Server to
PostgreSQL running on Mac OS X. I have access to the java source code
and can make modifications. I have tried with PostgreSQL version 7.4.2
and 8.0.0beta2, but get the same error. I am using pgdev.305.jdbc3.jar
for 8.0.0beta2. The PostgreSQL installation are standard, except for
log_statement = 'all' to debug this problem.

The problem is that the server returns "ERROR:  canceling query due to
user request" on some of the queries. I do not find any (other) signs
of small timeouts or that the query could be cancelled in the java
client. I have tried the queries from the psql promt, and they are all
pretty fast. Where should I start looking? Are there any more debug
flags than log_statement that should be turned on?

Regards,
  - Tore.


Re: ERROR: canceling query due to user request

From
Paul Thomas
Date:
On 07/09/2004 12:33 Tore Halset wrote:
> Hello.
>
> I am trying to port an old java application from MS SQL Server to
> PostgreSQL running on Mac OS X. I have access to the java source code and
> can make modifications. I have tried with PostgreSQL version 7.4.2 and
> 8.0.0beta2, but get the same error. I am using pgdev.305.jdbc3.jar for
> 8.0.0beta2. The PostgreSQL installation are standard, except for
> log_statement = 'all' to debug this problem.
>
> The problem is that the server returns "ERROR:  canceling query due to
> user request" on some of the queries. I do not find any (other) signs of
> small timeouts or that the query could be cancelled in the java client. I
> have tried the queries from the psql promt, and they are all pretty fast.
> Where should I start looking? Are there any more debug flags than
> log_statement that should be turned on?


It sounds to my like your Java app is issueing the cancel.

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

Re: ERROR: canceling query due to user request

From
Mr Pink
Date:
Hi,

We were getting that problem on an old, low spec server that we know has memory problems. The
error message occured only on larger jobs, such as restoring a large table.

IIRC, I got the same problem last year after messing around with postgres config parameters,
probably also a memory issue since it went away when I reset my parameters to more reasonable
settings.

The server log may have some information.

I hope this gives you some clue as to where to start looking for your problem.
regards
Mr Pink

--- Tore Halset <halset@pvv.ntnu.no> wrote:

> Hello.
>
> I am trying to port an old java application from MS SQL Server to
> PostgreSQL running on Mac OS X. I have access to the java source code
> and can make modifications. I have tried with PostgreSQL version 7.4.2
> and 8.0.0beta2, but get the same error. I am using pgdev.305.jdbc3.jar
> for 8.0.0beta2. The PostgreSQL installation are standard, except for
> log_statement = 'all' to debug this problem.
>
> The problem is that the server returns "ERROR:  canceling query due to
> user request" on some of the queries. I do not find any (other) signs
> of small timeouts or that the query could be cancelled in the java
> client. I have tried the queries from the psql promt, and they are all
> pretty fast. Where should I start looking? Are there any more debug
> flags than log_statement that should be turned on?
>
> Regards,
>   - Tore.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>




__________________________________
Do you Yahoo!?
Y! Messenger - Communicate in real time. Download now.
http://messenger.yahoo.com

Re: ERROR: canceling query due to user request

From
Tom Lane
Date:
Tore Halset <halset@pvv.ntnu.no> writes:
> I am trying to port an old java application from MS SQL Server to
> PostgreSQL running on Mac OS X.
> ...
> The problem is that the server returns "ERROR:  canceling query due to
> user request" on some of the queries.

The proximate cause of this has to be that something is sending SIGINT
to the backend process that's running the query.  You'll have to look
around for reasons for that to happen, if you're sure that your client
code isn't doing it.  A couple of possibilities come to mind:

1. You accidentally typed ^C on the terminal window the postmaster was
launched from.

2. The postmaster was launched under non-infinite resource limits
(ulimit settings) and whatever enforces that on OS X does it by sending
SIGINT rather than the more standard signals for such things.  Do the
SIGINTs come at predictable times, such as when the backend has
accumulated X amount of runtime?

This isn't the first time I've heard of unexpected SIGINTs being
delivered on OS X.  (I vaguely recall seeing the MySQL guys complaining
of just that.)  I have not seen it happen in my own testing, though,
so I'm not sure what's going on.  Let us know if you find the cause.

            regards, tom lane

Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 7, 2004, at 15:11, Paul Thomas wrote:

> It sounds to my like your Java app is issueing the cancel.

Something fishy is going on here as the application is very stable
under MS SQL Server. I have tried to set log_statement to 'all'. All
queries are displayed, but everything looks ok except for the error
message. Are there any other debug flags that I should try? I want to
see all trafic between the java app and postgresql.

Regards,
  - Tore.


Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 7, 2004, at 16:54, Tom Lane wrote:

> Tore Halset <halset@pvv.ntnu.no> writes:
>> I am trying to port an old java application from MS SQL Server to
>> PostgreSQL running on Mac OS X.
>> ...
>> The problem is that the server returns "ERROR:  canceling query due to
>> user request" on some of the queries.
>
> The proximate cause of this has to be that something is sending SIGINT
> to the backend process that's running the query.  You'll have to look
> around for reasons for that to happen, if you're sure that your client
> code isn't doing it.  A couple of possibilities come to mind:
>
> 1. You accidentally typed ^C on the terminal window the postmaster was
> launched from.

No. The same PostgreSQL server are serving other java clients without
problems. It is all running on my PowerBook.

> 2. The postmaster was launched under non-infinite resource limits
> (ulimit settings) and whatever enforces that on OS X does it by sending
> SIGINT rather than the more standard signals for such things.  Do the
> SIGINTs come at predictable times, such as when the backend has
> accumulated X amount of runtime?

I have not changed any ulimit settings in the OS and have not changed
anything besides the log_statement in postgresql.conf.

I need to debug all of the trafic between the server and the client to
track down this problem.

Regards,
  - Tore.


Re: ERROR: canceling query due to user request

From
Tom Lane
Date:
Tore Halset <halset@pvv.ntnu.no> writes:
> I need to debug all of the trafic between the server and the client to
> track down this problem.

I suspect that it's got nothing at all to do with the traffic between
the server and the client, and that the SIGINT is coming from some
outside agency.  However, if you want to be sure about it, set the
postmaster's log_min_messages to DEBUG2, and then the next time the
problem happens, look in the postmaster log to see if you see anything
about
    processing cancel request: sending SIGINT to process %d

            regards, tom lane

Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 7, 2004, at 20:03, Tom Lane wrote:

> I suspect that it's got nothing at all to do with the traffic between
> the server and the client, and that the SIGINT is coming from some
> outside agency.  However, if you want to be sure about it, set the
> postmaster's log_min_messages to DEBUG2, and then the next time the
> problem happens, look in the postmaster log to see if you see anything
> about
>     processing cancel request: sending SIGINT to process %d

Yes, you are correct. I added the debug flag and reproduced the
problem. This was written to the log:

<2004-09-08 08:55:43 CEST 413eacee.3402>DEBUG:  processing cancel
request: sending SIGINT to process 13311
<2004-09-08 08:55:43 CEST >DEBUG:  server process (PID 13314) exited
with exit code 0
<2004-09-08 08:55:43 CEST 413eace4.33ff>ERROR:  canceling query due to
user request
<2004-09-08 08:55:43 CEST >DEBUG:  forked new backend, pid=13315
socket=60
<2004-09-08 08:55:43 CEST 413eacef.3403>DEBUG:  processing cancel
request: sending SIGINT to process 13311

Are the OS sending the SIGINT to PostgreSQL or are postmaster sending
it to one of its child?

Regards,
  - Tore.


Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 8, 2004, at 9:07, Tore Halset wrote:

> On Sep 7, 2004, at 20:03, Tom Lane wrote:
>
>> I suspect that it's got nothing at all to do with the traffic between
>> the server and the client, and that the SIGINT is coming from some
>> outside agency.  However, if you want to be sure about it, set the
>> postmaster's log_min_messages to DEBUG2, and then the next time the
>> problem happens, look in the postmaster log to see if you see anything
>> about
>>     processing cancel request: sending SIGINT to process %d
>
> Yes, you are correct. I added the debug flag and reproduced the
> problem. This was written to the log:
>
> <2004-09-08 08:55:43 CEST 413eacee.3402>DEBUG:  processing cancel
> request: sending SIGINT to process 13311
> <2004-09-08 08:55:43 CEST >DEBUG:  server process (PID 13314) exited
> with exit code 0
> <2004-09-08 08:55:43 CEST 413eace4.33ff>ERROR:  canceling query due to
> user request
> <2004-09-08 08:55:43 CEST >DEBUG:  forked new backend, pid=13315
> socket=60
> <2004-09-08 08:55:43 CEST 413eacef.3403>DEBUG:  processing cancel
> request: sending SIGINT to process 13311
>
> Are the OS sending the SIGINT to PostgreSQL or are postmaster sending
> it to one of its child?

I installed beta2 on a local linux box instead and now everything is
working :/ Something must trigger Mac OS X to send those evil SIGINTs.

Regards,
  - Tore.


Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 8, 2004, at 11:25, Tore Halset wrote:

> I installed beta2 on a local linux box instead and now everything is
> working :/ Something must trigger Mac OS X to send those evil SIGINTs.

Will it happens on the linux box as well, but not that often. Grr.

  - Tore.


Re: ERROR: canceling query due to user request

From
Tom Lane
Date:
Tore Halset <halset@pvv.ntnu.no> writes:
> On Sep 7, 2004, at 20:03, Tom Lane wrote:
>> I suspect that it's got nothing at all to do with the traffic between
>> the server and the client, and that the SIGINT is coming from some
>> outside agency.

> Yes, you are correct.

No, I'm not ;-)

> I added the debug flag and reproduced the
> problem. This was written to the log:

> <2004-09-08 08:55:43 CEST 413eacee.3402>DEBUG:  processing cancel
> request: sending SIGINT to process 13311

This is a smoking gun: your client *is* issuing cancel requests, whether
you know it or not.  (Either that or some other process has magically
acquired the secret cancel key that was issued to your connection.)

            regards, tom lane

Re: ERROR: canceling query due to user request

From
Tore Halset
Date:
On Sep 8, 2004, at 16:00, Tom Lane wrote:

> This is a smoking gun: your client *is* issuing cancel requests,
> whether
> you know it or not.  (Either that or some other process has magically
> acquired the secret cancel key that was issued to your connection.)

Thanks! I think I found the problem and a solution to it. The java
framework reused Statement objects and issued a st.cancel() between
every query performed with the Statement object. I just commented out
the st.cancel() line and everything seems to work ok. I googled for
others having the same problem and found this one:

http://archives.postgresql.org/pgsql-jdbc/2003-09/msg00167.php

Perhaps it is the same problem? Is this something that can be fixed in
the jdbc driver or are PostgreSQL(+jdbc) doing the right thing here?

Regards,
  - Tore.


Re: [JDBC] ERROR: canceling query due to user request

From
Paul Thomas
Date:
On 09/09/2004 08:15 Tore Halset wrote:
> On Sep 8, 2004, at 16:00, Tom Lane wrote:
>
>> This is a smoking gun: your client *is* issuing cancel requests, whether
>> you know it or not.  (Either that or some other process has magically
>> acquired the secret cancel key that was issued to your connection.)
>
> Thanks! I think I found the problem and a solution to it. The java
> framework reused Statement objects and issued a st.cancel() between every
> query performed with the Statement object. I just commented out the
> st.cancel() line and everything seems to work ok. I googled for others
> having the same problem and found this one:
>
> http://archives.postgresql.org/pgsql-jdbc/2003-09/msg00167.php
>
> Perhaps it is the same problem? Is this something that can be fixed in
> the jdbc driver or are PostgreSQL(+jdbc) doing the right thing here?
>
> Regards,
>  - Tore.

Given that statement.cancel() should only be used to cancel a running
query, I think the problem is more in your framework's misuse of cancel()
rather than in the driver itself.

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

Re: [JDBC] ERROR: canceling query due to user request

From
Oliver Jowett
Date:
Paul Thomas wrote:

> Given that statement.cancel() should only be used to cancel a running
> query, I think the problem is more in your framework's misuse of
> cancel() rather than in the driver itself.

JDBC gives you no way to ensure you only call cancel() on a running
query (there's a race between query execution returning and the call to
cancel()). Calling cancel() on a statement that's not currently
executing should do nothing; if it ends up cancelling a future query,
it's a driver bug.

-O

Re: [JDBC] ERROR: canceling query due to user request

From
Paul Thomas
Date:
On 09/09/2004 10:41 Oliver Jowett wrote:
> Paul Thomas wrote:
>
>> Given that statement.cancel() should only be used to cancel a running
>> query, I think the problem is more in your framework's misuse of
>> cancel() rather than in the driver itself.
>
> JDBC gives you no way to ensure you only call cancel() on a running query
> (there's a race between query execution returning and the call to
> cancel()). Calling cancel() on a statement that's not currently executing
> should do nothing; if it ends up cancelling a future query, it's a driver
> bug.

Thanks for the explaination Oliver. Maybe there is a driver bug then?

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

Byte Sizes

From
Pierre-Frédéric Caillaud
Date:
    Hello,

* On the size of arrays :

    I did not find any info in the Docs on this.
    How many bytes does an array take on disk ?

    My bet would be 4 bytes for number of dimensions, 4 per dimension for the
length, and then the storage requirements of whatever's in the array.

    If the array contains fixed size elements like integers, it'd be 4 bytes
per element.
    However if it contains variable length elements like text, is there a
pointer table ? Or are the elements packed together ?

    Is there any advantage in using a smallint[] over an integer[] ?

    Does a smallint[] with 2 elements really take 12 bytes ?

* On Alignment :

    The docs say fields are aligned on 4-bytes boundaries.
    Does this mean that several consecutive smallint fields will take 4 bytes
each ?
    What about seleral consecutive "char" fields ?

    I ask this because I'll have a lot of columns with small values to store
in a table, and would like it to be small and to fit in the cache.


    Thanks for any info.


Re: [JDBC] ERROR: canceling query due to user request

From
Tom Lane
Date:
Paul Thomas <paul@tmsl.demon.co.uk> writes:
> On 09/09/2004 10:41 Oliver Jowett wrote:
>> JDBC gives you no way to ensure you only call cancel() on a running query
>> (there's a race between query execution returning and the call to
>> cancel()). Calling cancel() on a statement that's not currently executing
>> should do nothing; if it ends up cancelling a future query, it's a driver
>> bug.

> Thanks for the explaination Oliver. Maybe there is a driver bug then?

IIRC there was such a bug at one time, but I thought it had been fixed.
Maybe the problem is use of an old driver?

            regards, tom lane

Re: [JDBC] ERROR: canceling query due to user request

From
Oliver Jowett
Date:
Tom Lane wrote:
> Paul Thomas <paul@tmsl.demon.co.uk> writes:
>
>>On 09/09/2004 10:41 Oliver Jowett wrote:
>>
>>>JDBC gives you no way to ensure you only call cancel() on a running query
>>>(there's a race between query execution returning and the call to
>>>cancel()). Calling cancel() on a statement that's not currently executing
>>>should do nothing; if it ends up cancelling a future query, it's a driver
>>>bug.
>
>
>>Thanks for the explaination Oliver. Maybe there is a driver bug then?
>
>
> IIRC there was such a bug at one time, but I thought it had been fixed.
> Maybe the problem is use of an old driver?

My patch for this never got applied, AFAIK. The current driver still
seems to send the cancellation request and continue without waiting for
an EOF from the server.

-O