Thread: ERROR: canceling query due to user request
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.
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 | +------------------------------+-------------------------------------------+
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
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
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.
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.
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
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.
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.
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.
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
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.
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 | +------------------------------+-------------------------------------------+
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
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 | +------------------------------+-------------------------------------------+
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.
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
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