On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure <mmoncure@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