Обсуждение: Scheduling Database Tasks

Relying of fine-grained time for sheduling tasks is usually a bad idea... If for some reason the previous task will
takelonger, you will load your server with the next one, which will take even longer, and over-load it with the
followingone... 
You can rely on cron, and serialize your tasks, using the standard Linux /etc/cron.hourly /etc/cron.daily scheme : put
yourscripts in these directories, and tasks will be simply scheduled, without interfering... 

Nicolas huillard

-----Message d'origine-----
De:    dialFree administration [SMTP:admin@dialfree.net]
Date:    jeudi 25 mai 2000 16:23
À:    pgsql-admin@postgresql.org
Objet:    [ADMIN] Scheduling Database Tasks

Hi there.
I am a PostgreSQL newbie recently rescued from the depths of despair (MS SQL
Server). In this state however I find my self pining for some of the
functionality I have become accustomed too. We have a number of tasks that
we like to perform using our database like sending emails to folk or billing
them or generating invoices.

MS SQL Server has a Jobs interface as part of it's database management
'suite' that allows various types of scripts (SQL, Active X) to be performed
with fine granularity (1 Second and up). Does PsotsgreSQL have any similar
functionality?

Crontab does not provide the granularity we require (3 seconds or so for
some of our tasks). We are also investigating Expect. If this functionality
is not available can it be created efficiently with any of the PL/tcl etc.
scripting/functional languages?

Thanks for your time.

-Ian

Ian Marshall
ian@talarion.com
www.talarion.com
(617) 527-1994

<< Fichier: ATT00023.html>>

i hav a field type text, but this field i need save more than 450000
caracters.

and text don't support thats...

Anyone knows which data field support that dimmensions.


Sorry, for the VERY VERY bad english.

regards.

Hi all,

    I have a few miscellaneous questions. If anyone has any ideas I'd
appreciate hearing them.


1. What happens when the primary key index runs out. That is, if we use an
INT(4) for a key and we create and delete enough to reach that number then
what happens?


2. Does anyone one know a standard SQL method (that works with Postgres) to
retrieve the primary key of a newly INSERTed record? We've found one way but
it will not work with order databases since it relies on reading data from
the sequence tables.


3. Does anyone know of any way to perform a database backup/synchronization
that somehow does its work when the database is otherwise not busy? Which is
preferred, backing up the PG files or doing some sort of sync?


4. Does anyone have any advice on how often to run VACUUM? Any rules of
thumb, etc?


Thanks,

--Rainer

Hello Ian!

Nicolas Huillard proposed the use of cron based scheduling of jobs.
This approach will overload you postgres server.

There is another solution, which I found, will give you the granularity
you will need:
you can combine cron based scheuling with the ability of batch job based
processing.
You can set up process queues which will allow to limit the number of
concurrent jobs running in this queue.

For further information, please contact http://www.gnqs.org


--
Mit freundlichen Gruessen / With best regards
   Reiner Dassing

On Fri, 26 May 2000, Rainer Mager wrote:

> 1. What happens when the primary key index runs out. That is, if we use an
> INT(4) for a key and we create and delete enough to reach that number then
> what happens?

If you are talking about serial values, then you'll get an error.
Otherwise it's of course up to you what you do with your primary keys.
Eventually we'll move to int8 for serial probably.

> 2. Does anyone one know a standard SQL method (that works with Postgres) to
> retrieve the primary key of a newly INSERTed record? We've found one way but
> it will not work with order databases since it relies on reading data from
> the sequence tables.

Again, what you do with your primary keys is up to you. Ideally you won't
need to retrieve the primary key value because you know what you just
inserted. If you are talking about serial, then the answer is there isn't
one because serial isn't SQL. But as someone already said, the curval
function will do.

> 3. Does anyone know of any way to perform a database backup/synchronization
> that somehow does its work when the database is otherwise not busy? Which is
> preferred, backing up the PG files or doing some sort of sync?

For backup use pg_dump, for synchronization you're currently out of luck.

> 4. Does anyone have any advice on how often to run VACUUM? Any rules of
> thumb, etc?

If there's a lot of write activity in your database, nightly might be a
good idea. Otherwise less often.


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

On Fri, May 26, 2000 at 04:15:31PM +0200, Peter Eisentraut wrote:
> On Fri, 26 May 2000, Rainer Mager wrote:
>
> > 2. Does anyone one know a standard SQL method (that works with Postgres) to
> > retrieve the primary key of a newly INSERTed record? We've found one way but
> > it will not work with order databases since it relies on reading data from
> > the sequence tables.
>
> Again, what you do with your primary keys is up to you. Ideally you won't
> need to retrieve the primary key value because you know what you just
> inserted. If you are talking about serial, then the answer is there isn't
> one because serial isn't SQL. But as someone already said, the curval
> function will do.

Another approach is something like this:
pkey=sql("select nextval('some-sequence')")
sql("insert into table values ($pkey, $data)")



--
Ragnar Kjørstad

Hi all,

    I would expect this to be a FAQ but I can't find anything in the docs or on
the net. I'm trying to restore a pg_dumped database (via "psql <
databasefile") and get this error:

    query buffer max length of 16384 exceeded

Is there anyway to adjust this buffer (preferrably without rebuilding PG)?
Or is there a better way to restore a database?


Thanks,

--Rainer

Well, I fixed my problem. I'm sure everyone already knows this but if you
use the -d flag to pg_dump (Dump data as proper insert strings) then it
doesn't use the COPY command to insert data. Hence no HUGE strings to exceed
the buffer. Using this flag helped.


--Rainer