elegant and effective way for running jobs inside a database

2012/3/5 Artur Litwinowicz <admin@ybka.com>

Dear Developers,
I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.
OK if You say "use cron" or "pgAgent" I say I know that solutions, but
the are not effective and elegant. Compilation of "pgAgent" is a pain
(especially wxWidgets usage on system with no X) - it can run jobs with
minimal 60s periods but what when someone needs run it faster for eg.
with 5s period ? Of course using cron I can do that but it is not
effective and elegant solution. Why PostgreSQL can not have so elegant
solution like Oracle database ? I am working with Oracle databases for
many years, but I like much more PostgreSQL project but this one
thing... I can not understand - the lack of jobs inside the database...

IMHO it is not necessary add this feature to the PostgreSQL core, because
the OS already has the capability to schedule and maintain the tasks.

Best regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something. Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like. For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump? What if the
program crashes -- should it cause the server to restart? And so on.
It's not a trivial problem.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hello

2012/3/5 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
   I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something.  Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like.  For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump?  What if the
program crashes -- should it cause the server to restart?  And so on.
It's not a trivial problem.

I agree - it is not simple

* workflow support
* dependency support

a general ACID scheduler can be nice (in pg) but it is not really
simple. There was some proposal about using autovacuum demon like
scheduler.

Pavel

W dniu 2012-03-05 20:56, Alvaro Herrera pisze:

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something. Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like. For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump? What if the
program crashes -- should it cause the server to restart? And so on.
It's not a trivial problem.

Yes, yes it is not a trivial problem... - tools like "pgAgent" are good
when someone starts play with PostgreSQL - but this great environment
(only one serious against something like Oracle or DB2) needs something
professional, production ready. It can not happen when we are upgrading
database or OS and can not compile "pgAgent" because of "strange"
dependences... and for example whole sofisticated solution like web
application with complicated data flow has a problem... For example I am
using stored functions developed in Lua language, which are writing and
reading data to and from Redis server with a periods less then one
minute. Without "heart beat" like precise job manager it can not works
as professional as it can. Every one can use CRON or something like that
- yes it works but PostgreSQL has so many features and something like
job manager is inalienable in mine mind.

Best regards,
Artur

2012/3/5 Artur Litwinowicz <admin@ybka.com>:

W dniu 2012-03-05 20:56, Alvaro Herrera pisze:

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
   I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something.  Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like.  For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump?  What if the
program crashes -- should it cause the server to restart?  And so on.
It's not a trivial problem.

Yes, yes it is not a trivial problem... - tools like "pgAgent" are good
when someone starts play with PostgreSQL - but this great environment
(only one serious against something like Oracle or DB2) needs something
professional, production ready. It can not happen when we are upgrading
database or OS and can not compile "pgAgent" because of "strange"
dependences... and for example whole sofisticated solution like web
application with complicated data flow has a problem... For example I am
using stored functions developed in Lua language, which are writing and
reading data to and from Redis server with a periods less then one
minute. Without "heart beat" like precise job manager it can not works
as professional as it can. Every one can use CRON or something like that
- yes it works but PostgreSQL has so many features and something like
job manager is inalienable in mine mind.

Long time a strategy for PostgreSQL was a minimal core and extensible
modules without duplication some system services. This strategy is
valid still but some services are in core - example should be
replication.

Some proposals about custom scheduler exists
http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and
it is part of ToDo - so this feature should be in core (in next 2-4
years).

Why this is not in core? Nobody wrote it :).

Regards

Pavel Stehule

W dniu 2012-03-05 22:09, Pavel Stehule pisze:

2012/3/5 Artur Litwinowicz <admin@ybka.com>:

W dniu 2012-03-05 20:56, Alvaro Herrera pisze:

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something. Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like. For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump? What if the
program crashes -- should it cause the server to restart? And so on.
It's not a trivial problem.

Yes, yes it is not a trivial problem... - tools like "pgAgent" are good
when someone starts play with PostgreSQL - but this great environment
(only one serious against something like Oracle or DB2) needs something
professional, production ready. It can not happen when we are upgrading
database or OS and can not compile "pgAgent" because of "strange"
dependences... and for example whole sofisticated solution like web
application with complicated data flow has a problem... For example I am
using stored functions developed in Lua language, which are writing and
reading data to and from Redis server with a periods less then one
minute. Without "heart beat" like precise job manager it can not works
as professional as it can. Every one can use CRON or something like that
- yes it works but PostgreSQL has so many features and something like
job manager is inalienable in mine mind.

Long time a strategy for PostgreSQL was a minimal core and extensible
modules without duplication some system services. This strategy is
valid still but some services are in core - example should be
replication.

Some proposals about custom scheduler exists
http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and
it is part of ToDo - so this feature should be in core (in next 2-4
years).

Why this is not in core? Nobody wrote it :).

Regards

Pavel Stehule

Best regards,
Artur

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Ouch... "in next 2-4 years" - it broke my heart like a bullet - You
should not write it... ;)
I feel that I need to set aside SQL, Python, PHP and so on and take to
my hands old book about C programming language from university ;)
I hope my words are like drops of water for this idea and in the future
some people will be happy to use professional job manager :)

Best regards,
Artur

Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:

Ouch... "in next 2-4 years" - it broke my heart like a bullet - You
should not write it... ;)
I feel that I need to set aside SQL, Python, PHP and so on and take to
my hands old book about C programming language from university ;)
I hope my words are like drops of water for this idea and in the future
some people will be happy to use professional job manager :)

Keep in mind that it's not about coding in C but mostly about figuring
out what a sane design out to look like.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

W dniu 2012-03-05 22:44, Alvaro Herrera pisze:

Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:

Ouch... "in next 2-4 years" - it broke my heart like a bullet - You
should not write it... ;)
I feel that I need to set aside SQL, Python, PHP and so on and take to
my hands old book about C programming language from university ;)
I hope my words are like drops of water for this idea and in the future
some people will be happy to use professional job manager :)

Keep in mind that it's not about coding in C but mostly about figuring
out what a sane design out to look like.

I understand it... (I meant if you wanna something... do it for your
self - it is the fastest way).
Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?
It will be so powerfull feature - I am working with systems made for
goverment (Orcale) - jobs are the core gears for data flow between many
systems and other goverment bureaus.

Best regards,
Artur

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

I understand it... (I meant if you wanna something... do it for your
self - it is the fastest way).

other way is to fund the work so someone can use his/her time to do it

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

is not unappreciated, is just a problem that already *has* a solution
if it were something that currently you can't do it then there would
be more people after it

It will be so powerfull feature - I am working with systems made for
goverment (Orcale) - jobs are the core gears for data flow between many
systems and other goverment bureaus.

me too, and we solve it with cron

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

W dniu 2012-03-05 23:09, Jaime Casanova pisze:

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

I understand it... (I meant if you wanna something... do it for your
self - it is the fastest way).

other way is to fund the work so someone can use his/her time to do it

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

is not unappreciated, is just a problem that already *has* a solution
if it were something that currently you can't do it then there would
be more people after it

It will be so powerfull feature - I am working with systems made for
goverment (Orcale) - jobs are the core gears for data flow between many
systems and other goverment bureaus.

me too, and we solve it with cron

And You can modulate the jobs frequency, stop them and start from inside
the database automatically using only algorithms and interenal events
without administrator hand work... with cron... I can not belive... I do
not meant just simple: run stored procedure... I am using cron as well,
but in my work I like elegant, complex solutions - many "lego" blocks is
not always the best and simplest solution...

On Mon, Mar 5, 2012 at 4:44 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:

Ouch... "in next 2-4 years" - it broke my heart like a bullet - You
should not write it... ;)
I feel that I need to set aside SQL, Python, PHP and so on and take to
my hands old book about C programming language from university ;)
I hope my words are like drops of water for this idea and in the future
some people will be happy to use professional job manager :)

Keep in mind that it's not about coding in C but mostly about figuring
out what a sane design out to look like.

Just so.

And it seems to me that the Right Thing here is to go down the road to
having the fabled Stored Procedure Language, which is *not* pl/pgsql,
in that iIt needs to run *outside* transactional context. It needs to
be able to start transactions, not to run inside them.

Given a language which can do some setup of transactions and then run
them, this could be readily used for a number of useful purposes, of
which a job scheduler would be just a single example.

It would enable turning some backend processes from hand-coded C into
possibly more dynamically-flexible scripted structures.

I'd expect this to be useful for having more customizable/dynamic
policies for the autovacuum process, for instance.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Mon, Mar 5, 2012 at 12:17 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

2012/3/5 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:

Dear Developers,
   I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something.  Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like.  For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump?  What if the
program crashes -- should it cause the server to restart?  And so on.
It's not a trivial problem.

I agree - it is not simple

* workflow support
* dependency support

a general ACID scheduler can be nice (in pg) but it is not really
simple. There was some proposal about using autovacuum demon like
scheduler.

I've been thinking about making autovacuum a special case of a general
*non*-transactional job-running system because dealing with large
physical changes to a database (where one wants to rewrite 300GB of
data, or whatever) that are prohibitive in a transaction are -- to
understate things -- incredibly painful. Painful enough that people
will risk taking their site down with a large UPDATE or ALTER TABLE,
hoping that they can survive the duration (and then when they cancel
it and are left with huge volumes of dead tuples, things get a lot
more ugly).

The closest approximation a client program can make is "well, I guess
I'll paginate through the database and rewrite small chunks". Instead,
it may make more sense to have the database spoon-feed work to do the
transformations little-at-a-time ala autovacuum.

--
fdr

Keep in mind that it's not about coding in C but mostly about figuring
out what a sane design out to look like.

While I can straddle the fence pretty my first reaction is that we are talking about "application" functionality that falls outside what belongs in "core" PostgreSQL. I'd rather see pgAgent be improved and act as a basic implementation while, for more complex use-cases, letting the community/marketplace provide solutions.

Even with simple use-cases you end up having a separate process continually running anyway. The main benefit to linking with core would be the ability to startup that process after the server starts and shutdown the process before the server shutdown. That communication channel is something to consider outside this specific application and, if done, could be used to talk with whatever designated "pgAgent"-like application the user chooses. Other applications could also be communicated with in this way. Basically some form of API where in the postgres.conf file you specify which IP addresses and ports you wish to synchronize and which executable to launch just prior to communicating on said port. If the startup routine succeeds that Postgres will, within reason, attempt to communicate and wait for these external process to finish before shutting down. If the external application closes it should proactively notify Postgres that it is doing so AND if you startup a program manually it can look for and talk with a running Postgres instance.

David J.

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

I think it's a bit unfair to say that this idea is unappreciated.
There are LOTS of good features that we don't have yet simply because
nobody's had time to implement them. There are many things I'd really
like to have that I have spent no time at all on as yet, just because
there are other things that I (or my employer) would like to have even
more. The good news is that this is an open-source project and there
is always room at the table for more people who would like to
contribute (or fund others so that they can contribute).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

I think it's a bit unfair to say that this idea is unappreciated.

Well, there is the question of why we should re-invent the cron wheel.

There are LOTS of good features that we don't have yet simply because
nobody's had time to implement them.

Implementation work is only part of it. Any large feature will create
an ongoing, distributed maintenance overhead. It seems entirely
possible to me that we'd not accept such a feature even if someone
dropped a working implementation on us.

But having said that, it's not apparent to me why such a thing would
need to live "inside the database" at all. It's very easy to visualize
a task scheduler that runs as a client and requires nothing new from the
core code. Approaching the problem that way would let the scheduler
be an independent project that stands or falls on its own merits.

regards, tom lane

2012/3/6 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

I think it's a bit unfair to say that this idea is unappreciated.

Well, there is the question of why we should re-invent the cron wheel.

There are LOTS of good features that we don't have yet simply because
nobody's had time to implement them.

Implementation work is only part of it.  Any large feature will create
an ongoing, distributed maintenance overhead.  It seems entirely
possible to me that we'd not accept such a feature even if someone
dropped a working implementation on us.

But having said that, it's not apparent to me why such a thing would
need to live "inside the database" at all.  It's very easy to visualize
a task scheduler that runs as a client and requires nothing new from the
core code.  Approaching the problem that way would let the scheduler
be an independent project that stands or falls on its own merits.

There are a few arguments for scheduler in core

* platform independence
* possible richer workflow and loging possibilities or as minimum -
better integration with SP
* when application has lot of business logic in stored procedures,
then outer scheduler is little bit foreign element - harder
maintaining, harder configuration
* when somebody would to implement some like materialised views, then
have to have use outer schedule for very simple task - just exec SP
every 5 minutes

so I think there are reason why we can have a scheduler on core -
simple or richer, but it can helps. cron and similar works, but
maintaining of external scheduler is more expensive then using some
simple scheduler in core.

Regards

Pavel

On Tue, Mar 6, 2012 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:

Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?

I think it's a bit unfair to say that this idea is unappreciated.

Well, there is the question of why we should re-invent the cron wheel.

There are LOTS of good features that we don't have yet simply because
nobody's had time to implement them.

Implementation work is only part of it.  Any large feature will create
an ongoing, distributed maintenance overhead.  It seems entirely
possible to me that we'd not accept such a feature even if someone
dropped a working implementation on us.

But having said that, it's not apparent to me why such a thing would
need to live "inside the database" at all.  It's very easy to visualize
a task scheduler that runs as a client and requires nothing new from the
core code.  Approaching the problem that way would let the scheduler
be an independent project that stands or falls on its own merits.

I was trying to make a general comment about PostgreSQL development,
without diving too far into the merits or demerits of this particular
feature. I suspect you'd agree with me that, in general, a lot of
valuable things don't get done because there aren't enough people or
enough hours in the day, and we can always use more contributors.

But since you brought it up, I think there is a lot of value to having
a scheduler that's integrated with the database. There are many
things that the database does which could also be done outside the
database, but people want them in the database because it's easier
that way. If you have a web application that talks to the database,
and which sometimes needs to schedule tasks to run at a future time,
it is much nicer to do that by inserting a row into an SQL table
somewhere, or executing some bit of DDL, than it is to do it by making
your web application know how to connect to a PostgreSQL database and
also how to rewrite crontab (in a concurrency-safe manner, no less).

Now, the extent to which such a schedule requires core support is
certainly arguable. Maybe it doesn't, and can be an entirely
stand-alone project. pgAgent aims to do something like this, but it
has a number of deficiencies, including a tendency to quit
unexpectedly and a very klunky interface. Those are things that could
presumably fixed, or done differently in a new implementation, and
maybe that's all anyone needs. Or maybe it's not. But at any rate I
think the idea of a better job scheduler is a good one, and if anyone
is interested in working on that, I think we should encourage them to
do so, regardless of what happens vis-a-vis core. This is a very
common need, and the current solutions are clearly more awkward than
our users would like.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Mar 6, 2012 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

But having said that, it's not apparent to me why such a thing would
need to live "inside the database" at all. �It's very easy to visualize
a task scheduler that runs as a client and requires nothing new from the
core code. �Approaching the problem that way would let the scheduler
be an independent project that stands or falls on its own merits.

But since you brought it up, I think there is a lot of value to having
a scheduler that's integrated with the database. There are many
things that the database does which could also be done outside the
database, but people want them in the database because it's easier
that way. If you have a web application that talks to the database,
and which sometimes needs to schedule tasks to run at a future time,
it is much nicer to do that by inserting a row into an SQL table
somewhere, or executing some bit of DDL, than it is to do it by making
your web application know how to connect to a PostgreSQL database and
also how to rewrite crontab (in a concurrency-safe manner, no less).

Sure, and I would expect that a client-side scheduler would work just
the same way: you make requests to it through database actions such
as inserting a row in a task table.

Now, the extent to which such a schedule requires core support is
certainly arguable. Maybe it doesn't, and can be an entirely
stand-alone project. pgAgent aims to do something like this, but it
has a number of deficiencies, including a tendency to quit
unexpectedly and a very klunky interface.

Well, if they didn't get it right the first time, that suggests that
it's a harder problem than people would like to think. All the more
reason to do it as an external project, at least to start with.
I would much rather entertain a proposal to integrate a design that's
been proven by an external implementation, than a proposal to implement
a design that's never been tested at all (which we'll nonetheless have
to support for eternity, even if it turns out to suck).

regards, tom lane

Excerpts from Tom Lane's message of mar mar 06 12:47:46 -0300 2012:

Robert Haas <robertmhaas@gmail.com> writes:

But since you brought it up, I think there is a lot of value to having
a scheduler that's integrated with the database. There are many
things that the database does which could also be done outside the
database, but people want them in the database because it's easier
that way. If you have a web application that talks to the database,
and which sometimes needs to schedule tasks to run at a future time,
it is much nicer to do that by inserting a row into an SQL table
somewhere, or executing some bit of DDL, than it is to do it by making
your web application know how to connect to a PostgreSQL database and
also how to rewrite crontab (in a concurrency-safe manner, no less).

Sure, and I would expect that a client-side scheduler would work just
the same way: you make requests to it through database actions such
as inserting a row in a task table.

What such an external scheduler would need from core is support for
starting up and shutting down along postmaster (as well as restarts at
appropriate times). Postmaster already has the ability to start and
shut down many processes depending on several different policies; I
think it's mostly a matter of exporting that funcionality in a sane way.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support