Обсуждение: "set nice" running a transaction in a lower priority?

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?  I have an "import"
process that takes a while, and I don't mind having
the user who is doing the import wait; but it would
be nice to have the box more responsive while the
import is occuring.

Best,

Clark

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