Re: Large transaction

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