Thread: ERROR: canceling query due to user request
Hi All,
I had been having some problems since yesterday with dumping/loading data and even running vacuum analyse. The good news is that I've sorted it out and things seem to running smoothly again.
There may be a bug in there somewhere, or at least a degree of fragility that may deserve some attention. I'm writing this now in the hope of getting any feedback on what happened that anyone might have, and perhaps it might help someone out in the future.
After a successful stress test of backup and restore 2 days ago, nothing was working today.
The problem seemed to stem from some (I thought) innocuous changes I made to the postgresql.conf file.
Reverting to the original file has rectified the problem.This is typical of the errors I got:
pg_dump: ERROR: canceling query due to user request
pg_dump: SQL command to dump the contents of table "d_hi_mise_item_uri" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: canceling query due to user request
pg_dump: The command was: COPY public.d_hi_mise_item_uri (ymd_uri, tenpo_cd, item_id, urisuu, urikin, araridaka,
lst_genka, ave_genka, logic_tana_suu, henpintyuu_suu, tenji_suu, tuika_suu, idou_in_suu, idou_out_suu, rank_new
_suu, rank_old_suu) TO stdout;
pg_dump: SQL command to dump the contents of table "d_hi_mise_item_uri" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: canceling query due to user request
pg_dump: The command was: COPY public.d_hi_mise_item_uri (ymd_uri, tenpo_cd, item_id, urisuu, urikin, araridaka,
lst_genka, ave_genka, logic_tana_suu, henpintyuu_suu, tenji_suu, tuika_suu, idou_in_suu, idou_out_suu, rank_new
_suu, rank_old_suu) TO stdout;
The part "ERROR: canceling query due to user request" is the main part. It occured during vacuum analyse too.
I setup a new server from scratch and got similar errors. It was significant that I had copied the conf files from the previous installation.
These are the settings that I changed:
tcpip_socket = true
shared_buffers = 2048
wal_buffers = 16
checkpoint_segments = 8
effective_cache_size = 50000
random_page_cost = 2
statement_timeout = 30000
statement_timeout = 30000
I thought that these are pretty conservative settings, even for my system. but maybe not.
On the old server (Version is 7.4 running on turbo linux 7 (kernal 2.4.18-15, libc ver 2.2.4-13)) under virtual pc under windows 2000) . The Linux kernal settings are default, and the PC has 1GB of RAM, at about half of which should be available to the the linux system.
I'm going to experiment with the conf settings one at a time from now and see what happens. I'm also hoping to remove the need for virtual PC as it seems to complicate configuration. Unfortunately the development server doesn't resemble the intended production configuration in the least, so it seems somewhat pointless anyway.
regards
Iain
"Iain" <iain@mst.co.jp> writes:
> This is typical of the errors I got:
> pg_dump: ERROR: canceling query due to user request
If you didn't actually do anything to cancel the query, then this
indicates that something is sending SIGINT signals to the backend
process. I'd bet on a "ulimit" or equivalent resource-limit setting
being tripped. Check what conditions the postmaster is being started
under --- generally, you do not want *any* active ulimit restrictions
on the server processes.
regards, tom lane
Hi Tom, I agree with your assessment of this. I'm no expert on Linux, and I'm new to Postgres but I'm learning. I hope to have a chance to play with and test these kinds of configuration settings before the system goes live. Unfortunately there is no plan for a test environment that is comparable to the production environment. By the way, I've been using 7.4 for a little while now (in development) and my feeling is that the DB has matured considerably since the previous version I used which was 7.1. It's looking good so far. Regards Iain ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Iain" <iain@mst.co.jp> Cc: <pgsql-admin@postgresql.org> Sent: Saturday, December 20, 2003 3:11 AM Subject: Re: [ADMIN] ERROR: canceling query due to user request > "Iain" <iain@mst.co.jp> writes: > > This is typical of the errors I got: > > pg_dump: ERROR: canceling query due to user request > > If you didn't actually do anything to cancel the query, then this > indicates that something is sending SIGINT signals to the backend > process. I'd bet on a "ulimit" or equivalent resource-limit setting > being tripped. Check what conditions the postmaster is being started > under --- generally, you do not want *any* active ulimit restrictions > on the server processes. > > regards, tom lane