Re: Breaking up a PostgreSQL COPY command into chunks?

Try this,

max_standby_archive_delay = 600s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
max_standby_streaming_delay = 600s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay

or try
pg_xlog_replay_pause()
pg_xlog_replay_resume()

On Fri, Nov 8, 2013 at 10:06 AM, Victor Hooi <victorhooi(at)yahoo(dot)com> wrote:

> Hi,
>
> We're using psycopg2 with COPY to dump CSV output from a large query.
>
> The actual SELECT query itself is large (both in number of
> records/columns, and also in width of values in columns), but still
> completes in around under a minute on the server.
>
> However, if you then use a COPY with it, it will often time out.
>
> We're using psycopg2 to run the command, the trace we get is something
> like:
>
> Traceback (most recent call last):
> File "foo.py", line 259, in <module>
> jobs[job].run_all()
> File "foo.py", line 127, in run_all
> self.export_to_csv()
> File "foo.py", line 168, in export_to_csv
> cur.copy_expert(self.export_sql_statement, f)
> psycopg2.extensions.TransactionRollbackError: canceling statement due to
> conflict with recovery
> DETAIL: User was holding shared buffer pin for too long.
>
> My question is, what are some simple ways we can use to chunk up the query?
>
> Could we pull down a list of all the ids (auto-incrementing int), break
> this list up, then use a WHERE clause to break it up, running multiple COPY
> commands?
>
> Or would it be better to use LIMIT/OFFSET to break it up? I'm not sure how
> we'd figure out when we reached the end of the results set though (apart
> from just counting the results?).
>
> Or are there other approaches you guys could recommend?
>
> Cheers,
> Victor
>