Обсуждение: Large transaction

<font face="Andale Mono">I'm trying to move data from an Oracle database into postgresql 7.4.3.  I have a program that
isextracting data and writing it to an sql script as a long series of inserts (20,000 at a time).  When I run these
scriptsusing psql, I occassionally get a problem that is reported as <br /><br /> ERROR:  canceling query due to user
request<br/><br /> And then it won't clear.  If I quit psql and start it again, every sql statement on that database
givesthis error after 30 seconds (approx) of hanging.  The only way to clear this is to restart the postgresql backend
andthen try vacuuming a couple of times.  It eventually clears in this manner.<br /><br /> What am I doing wrong?  I
increasedthe 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?<br/><br /> Thanks.<br /><br /> Paul<br /><br /></font> 

Paul B. Anderson wrote:
> 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?

Maybe you should use COPY instead, but this is the wrong list for 
questions like that; use pgsql-sql or pgsql-performance instead.

Regards,
Andreas