X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0C24BD1B4B2 for ; Thu, 18 Dec 2003 09:25:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88564-02 for ; Thu, 18 Dec 2003 05:25:09 -0400 (AST) Received: from millenium.mst.co.jp (unknown [210.230.185.241]) by svr1.postgresql.org (Postfix) with ESMTP id A1719D1B4C2 for ; Thu, 18 Dec 2003 05:24:39 -0400 (AST) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id hBI9DbJ06532 for ; Thu, 18 Dec 2003 18:13:37 +0900 Message-ID: <007f01c3c548$aa0aaf50$7201a8c0@mst1x5r347kymb> From: "Iain" To: Subject: ERROR: canceling query due to user request Date: Thu, 18 Dec 2003 18:23:57 +0900 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007B_01C3C594.19CC31B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, HTML_FONT_FACE_ODD X-Spam-Level: X-Archive-Number: 200312/276 X-Sequence-Number: 11782 This is a multi-part message in MIME format. ------=_NextPart_000_007B_01C3C594.19CC31B0 Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: 7bit 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; 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 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 ------=_NextPart_000_007B_01C3C594.19CC31B0 Content-Type: text/html; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I had been having some problems s= ince=20 yesterday with dumping/loading data and even running vacuum analyse. The go= od=20 news is that I've sorted it out and things seem to running smoothly=20 again.
 
There may be a bug in there somew= here, or=20 at least a degree of fragility that may deserve some attention. I'm writing= this=20 now in the hope of getting any feedback on what happened that anyone might = have,=20 and perhaps it might help someone out in the future.
 
After a successful stress test of= backup=20 and restore 2 days ago, nothing was working today.=20
The problem seemed to stem from s= ome (I=20 thought) innocuous changes= I made=20 to the postgresql.conf file.
Reverting to the original file has= =20 rectified the problem.
 
This is typical of the errors I= =20 got:
 
pg_dump: ERROR:  canceling q= uery due=20 to user request
pg_dump: SQL command to dump the contents of table=20 "d_hi_mise_item_uri" failed: PQendcopy() failed.
pg_dump: Error message = from=20 server: ERROR:  canceling query due to user request
pg_dump: The co= mmand=20 was: COPY public.d_hi_mise_item_uri (ymd_uri, tenpo_cd, item_id, urisuu, ur= ikin,=20 araridaka,
 lst_genka, ave_genka, logic_tana_suu, henpintyuu_suu,= =20 tenji_suu, tuika_suu, idou_in_suu, idou_out_suu, rank_new
_suu, rank_old= _suu)=20 TO stdout;
 
 
The part "ERROR:  canceling = query due=20 to user request" is the main part. It occured during vacuum analyse=20 too.
I setup a new server = from=20 scratch and got similar errors. It was significant that I had copied the co= nf=20 files from the previous installation.
 
These are the settings that I=20 changed:
 
tcpip_socket =3D true 
shared_buffers =3D 2048 
wal_buffers =3D 16
checkpoint_segments =3D 8 
effective_cache_size =3D 50000
random_page_cost =3D 2
stateme= nt_timeout=20 =3D 30000  
 
I thought that these are pretty= =20 conservative settings, even for my system. but maybe not.
 
On=20 the old server (Version is 7.4 running on turbo linux 7 (kernal=20 2.4.18-15, libc ver 2.2.4-13)) under virtual pc under windows 2000) . = The=20 Linux kernal settings are default, and the PC has 1GB of RAM, at about half= of=20 which should be available to the the linux system.
 
I'm going to experiment with= the conf=20 settings one at a time from now and see what happens. I'm also hoping to re= move=20 the need for virtual PC as it seems to complicate=20 configuration. Unfortunately the development server doesn't resemble t= he=20 intended production configuration in the least, so it seems somewhat pointl= ess=20 anyway.
 
regards
Iain
 
 
------=_NextPart_000_007B_01C3C594.19CC31B0--