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

Поиск
Список
Период
Сортировка

Scheduling Database Tasks

От
"dialFree administration"
Дата:

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

 

RE: Scheduling Database Tasks

От
Nicolas Huillard
Дата:
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>>

Data field whit long + 45000 car

От
"J Carlos Morales Duarte."
Дата:


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.




A few misc. questions.

От
"Rainer Mager"
Дата:
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


Re: Scheduling Database Tasks

От
Reiner Dassing
Дата:
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

Re: A few misc. questions.

От
Peter Eisentraut
Дата:
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


Re: A few misc. questions.

От
Ragnar Kjørstad
Дата:
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

query buffer max length of 16384 exceeded

От
"Rainer Mager"
Дата:
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


RE: query buffer max length of 16384 exceeded

От
"Rainer Mager"
Дата:
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