"set nice" running a transaction in a lower priority?

On a related topic:

Is it possible to run a background query (I use JDBC)?

For example, I would like to run a query which I know takes a long time to
process, without holding the connection open and/or synchronously waiting
for the query response.

This is related to the "import" because I typically do my import to a temp
table, then INSERT...SELECT into the real table which is very slow because
of all the indexes which have to be handled.

Thanks,

Doug

On Wednesday 12 Jun 2002 6:33 pm, Clark C . Evans wrote:

I was wondering if there is a way to set transaction
priority so that it takes its good sweet time and
handles other queries quickly?

Not really - and it might be counter-productive to slow down the import. If
another transaction can't complete until the import is finished then
everything could end up delayed.

I guess you could nice the pid of the relevant backend process, but I'd be
inclined to batch the import into sections of 5000 records or so and sleep
for a second between batches.

- Richard Huxton

Richard Huxton <dev@archonet.com> writes:

On Wednesday 12 Jun 2002 6:33 pm, Clark C . Evans wrote:

I was wondering if there is a way to set transaction
priority so that it takes its good sweet time and
handles other queries quickly?

Not really - and it might be counter-productive to slow down the import. If
another transaction can't complete until the import is finished then
everything could end up delayed.

I guess you could nice the pid of the relevant backend process,

That would be a bad idea for the same reason --- you'd get into
priority-inversion scenarios where foreground processes are waiting for
the low-priority process, which can't run because other foreground
processes are hogging all the CPU.

but I'd be
inclined to batch the import into sections of 5000 records or so and sleep
for a second between batches.

Some kind of throttle on the client side seems like a better idea to me
too.

regards, tom lane

On Thu, Jun 13, 2002 at 09:09:46AM -0400, Tom Lane wrote:
| Some kind of throttle on the client side seems like a better idea

Thanks. This is exactly what is needed.

;) Clark