ERROR: canceling query due to user request

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

I am experiencing a similar problem with PostgreSQL running on Debian. I am not doing anything special when it happens, just once and a while. It is not a production system (on which this problem does not occur) so I just restart and everything is fine.

I would be interested in seeing if this is happening to anyone else though.

Thanks,

Andrew

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, September 07, 2004 9:55 AM
To: Tore Halset
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ERROR: canceling query due to user request

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"Andrew Janian" <ajanian@scottrade.com> writes:

I am experiencing a similar problem with PostgreSQL running on Debian.
I am not doing anything special when it happens, just once and a
while. It is not a production system (on which this problem does not
occur) so I just restart and everything is fine.

Restart? Do you actually need to restart to make the problem stop?
If so, do you mean restarting the PG postmaster, or rebooting the whole
machine? Would maybe just starting a fresh backend (psql session) make
the problem stop?

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.

I restart postmaster. Keep in mind this is only in development. I am using a postgres manager (EMS PostgreSQL Manager 2 http://ems-hitech.com/pgmanager/) and that is where I am seeing this problem. I was sure there were better ways to solve it than restarting, but I am not worried since we do not have this problem with any production databases.

The DB is a Debian machine running postgreSQL 7.4.2.

Thanks,

Andrew

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 07, 2004 10:40 AM
To: Andrew Janian
Cc: Tore Halset; pgsql-general@postgresql.org
Subject: Re: [GENERAL] ERROR: canceling query due to user request

"Andrew Janian" <ajanian@scottrade.com> writes:

I am experiencing a similar problem with PostgreSQL running on Debian.
I am not doing anything special when it happens, just once and a
while. It is not a production system (on which this problem does not
occur) so I just restart and everything is fine.

Restart? Do you actually need to restart to make the problem stop?
If so, do you mean restarting the PG postmaster, or rebooting the whole
machine? Would maybe just starting a fresh backend (psql session) make
the problem stop?

regards, tom lane

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