On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> IMNSHO, an 'in core' scheduler would be useful. however, I think >> before you tackle a scheduler, we need proper stored procedures. Our >> existing functions don't cut it because you can manage the transaction >> state yourself. > > Did you mean that you "can't" manage the transaction state yourself? > > Has anyone given any thought to what would be required to relax this > restriction? Is this totally impossible given our architecture, or > just a lack of round tuits?
yeah...that's what I meant. plpgsql exceptions are no help because there are many cases where you simply don't want the whole sequence of operations to run in a single transaction. loading lots of data to many tables is one. any operation that depends on transaction commit to do something (like notifications) and then hook on the results is another. you always have the heavy hitting administrative functions like vacuum, etc. another case is if you want a procedure to simply run forever...trivially done in a procedure, impossible in a function. The way people do this stuff now is to involve an 1) external scheduler such as cron and 2) .sql scripts for relatively simple things and/or a external scripting language like bash/perl. The external scheduler has a couple of annoying issues...completely not portable to code against and scheduling sub minute accuracy is a big headache. Also, adjusting the scheduling based on database events is, while not impossible, more difficult than it should be. External .sql scripts are portable but extremely limited. Involving something like perl just so I can jump outside the database to do manual transaction management is fine but ISTM these type of things are much better when done inside the database IMNSHO. Another factor here is that a sizable percentage of our user base is bargain hunters coming in from other systems like oracle and ms sql and having to rely in o/s scheduler is very distasteful to them. It's a hole, one of the last remaining IMO, in postgres being able to provide a complete server side development environment without having to deal with the o/s at all. I stand by my statements earlier. Any moderate level and up complexity database has all kinds of scheduling and scripting going on supporting it. These things really should be part of the database, dump with it, and run in a regular way irregardless of platform and server environment etc. With that, 90% of the code I have to write outside of the database goes away. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers