Обсуждение: 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
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