X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 99C1E3A40EF for ; Mon, 8 Nov 2004 22:58:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68169-06 for ; Mon, 8 Nov 2004 22:58:14 +0000 (GMT) Received: from mail712.megamailservers.com (mail712.megamailservers.com [69.49.98.22]) by svr1.postgresql.org (Postfix) with ESMTP id F137F3A4077 for ; Mon, 8 Nov 2004 22:58:16 +0000 (GMT) X-Authenticated-User: paul.pnlassociates.com Received: from [69.3.2.106] (h-69-3-2-106.mclnva23.covad.net [69.3.2.106]) (authenticated bits=0) by mail712.megamailservers.com (8.12.10/8.12.9) with ESMTP id iA8MwCZe026908 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Mon, 8 Nov 2004 17:58:15 -0500 Message-ID: <418FFA04.8090106@pnlassociates.com> Date: Mon, 08 Nov 2004 17:58:12 -0500 From: "Paul B. Anderson" User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Large transaction problem Content-Type: multipart/alternative; boundary="------------080006040806050508010303" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200411/66 X-Sequence-Number: 15451 This is a multi-part message in MIME format. --------------080006040806050508010303 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I'm trying to move data from an Oracle database into postgresql 7.4.3. I have a program that is extracting data and writing it to an sql script as a long series of inserts (20,000 at a time). When I run these scripts using psql, I occassionally get a problem that is reported as ERROR: canceling query due to user request And then it won't clear. If I quit psql and start it again, every sql statement on that database gives this error after 30 seconds (approx) of hanging. The only way to clear this is to restart the postgresql backend and then try vacuuming a couple of times. It eventually clears in this manner. What am I doing wrong? I increased the WAL segments from 3 to 30 but this did not get rid of the problem. Is there a limit on the size of a transaction? Thanks. Paul --------------080006040806050508010303 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I'm trying to move data from an Oracle database into postgresql 7.4.3.  I have a program that is extracting data and writing it to an sql script as a long series of inserts (20,000 at a time).  When I run these scripts using psql, I occassionally get a problem that is reported as

ERROR:  canceling query due to user request

And then it won't clear.  If I quit psql and start it again, every sql statement on that database gives this error after 30 seconds (approx) of hanging.  The only way to clear this is to restart the postgresql backend and then try vacuuming a couple of times.  It eventually clears in this manner.

What am I doing wrong?  I increased the WAL segments from 3 to 30 but this did not get rid of the problem.  Is there a limit on the size of a transaction?

Thanks.

Paul
--------------080006040806050508010303--