Thread: Batch processing

I'm in the need of something similar to Oracle's batch processing, in
Postgres.
I would like to have something like a queue of "requests" (SQL selects),
run one by one, so not to overload the server -- every SELECT is
'intensive'.

How would you implement such a thing?

ciao
Guido

On 29 Jul 2003 at 10:28, gnotari@linkgroup.it wrote:

> I'm in the need of something similar to Oracle's batch processing, in
> Postgres.
> I would like to have something like a queue of "requests" (SQL selects),
> run one by one, so not to overload the server -- every SELECT is
> 'intensive'.
>
> How would you implement such a thing?

psql databasename < selectfile

Would that do? You need to prepare the select file beforehand but something on
similar line should work..

Bye
 Shridhar

--
QOTD:    "It's been Monday all week today."

>  On 29 Jul 2003 at 10:28, gnotari@linkgroup.it wrote:
>
>  > I'm in the need of something similar to Oracle's batch processing, in
>  > Postgres.
>  > I would like to have something like a queue of "requests" (SQL
selects),
>  > run one by one, so not to overload the server -- every SELECT is
>  > 'intensive'.
>  >
>  > How would you implement such a thing?
>
>  psql databasename < selectfile
>
>  Would that do? You need to prepare the select file beforehand but
something on
>  similar line should work..

This, combined with the 'batch' command, could be a beginning.
Actually, I want to integrate all this with Zope, so I'll probably won't
use psql but python, serialize the results and put them in a table as text
objects.

thanks
Guido

Maybe the best option (specialy if you need users to control the jobs) would be to create a table like this:

CREATE TABLE batchjobs (
   id SERIAL,
   sql TEXT NOT NULL,
   done BOOLEAN NOT NOOL DEFAULT false
);

Then you create a pl/pgsql function that iterates every "undone" record from this table
and EXECUTEs the query from the sql field (and sets the done field to true).

You can run this process from crontab let's say, every 15 minutes  (only if this process is not already running) with a
echo "SELECT * FROM runJobs();"| psql ...

Hope this helps.


On Tue, 2003-07-29 at 05:28, gnotari@linkgroup.it wrote:

I'm in the need of something similar to Oracle's batch processing, in
Postgres.
I would like to have something like a queue of "requests" (SQL selects),
run one by one, so not to overload the server -- every SELECT is
'intensive'.

How would you implement such a thing?

ciao
Guido



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster