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?