Does PostgreSQL support job?

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

Does PostgreSQL support job?

От:
"Daniel Caune" <daniel.caune@ubisoft.com>
Дата:

Hi,

 

I try to find in the documentation whether PostgreSQL supports job, but I miserably failed.  Does PostgreSQL support job?  If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs against PostgreSQL?  I was thinking about using cron/plsql/sql-scripts on Linux.

 

Thanks (Tom Lane J)

 

--

Daniel CAUNE

 

Re: Does PostgreSQL support job?

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Daniel Caune wrote:
> Hi,
> 
>  
> 
> I try to find in the documentation whether PostgreSQL supports job, but
> I miserably failed.  Does PostgreSQL support job?  If not, what is the
> mechanism mostly adopted by PostgreSQL administrators for running jobs
> against PostgreSQL?  I was thinking about using cron/plsql/sql-scripts
> on Linux.

The unix cron systems is what most people use.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610) 359-1001 +  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Does PostgreSQL support job?

От:
"Daniel Caune" <daniel.caune@ubisoft.com>
Дата:


> -----Message d'origine-----
> De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de Bruce Momjian
> Envoyé : mercredi, février 01, 2006 17:57
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Does PostgreSQL support job?
> 
> Daniel Caune wrote:
> > Hi,
> >
> >
> >
> > I try to find in the documentation whether PostgreSQL supports job, but
> > I miserably failed.  Does PostgreSQL support job?  If not, what is the
> > mechanism mostly adopted by PostgreSQL administrators for running jobs
> > against PostgreSQL?  I was thinking about using cron/plsql/sql-scripts
> > on Linux.
> 
> The unix cron systems is what most people use.
> 

OK.  Thanks.  That's fine!

Re: Does PostgreSQL support job?

От:
"Owen Jacobson" <ojacobson@osl.com>
Дата:
Daniel Caune wrote:
> Hi,
>  
> I try to find in the documentation whether PostgreSQL supports job, 
> but I miserably failed.  Does PostgreSQL support job?  If not, what
> is the mechanism mostly adopted by PostgreSQL administrators for 
> running jobs against PostgreSQL?  I was thinking about using
> cron/plsql/sql-scripts on Linux.

The answer really depends on what you mean by "jobs".  If you have a database task that can be expressed as a series of commands with no interaction involved, you can just put those commands in a file (your-job-name.sql) and run it using psql and cron:

# replace leading stars with cron time settings
* * * * *     psql your-database -i your-job-name.sql

If you need something more complex, either a function which is executed from a script or a full-blown client program may be required.  IME that's fairly rare.

-Owen

Re: Does PostgreSQL support job?

От:
Andrew Sullivan <ajs@crankycanuck.ca>
Дата:
On Wed, Feb 01, 2006 at 05:53:52PM -0500, Daniel Caune wrote:
> I try to find in the documentation whether PostgreSQL supports job, but
> I miserably failed.  Does PostgreSQL support job?  If not, what is the

I don't know what "job" is, but it sounds like you want "cron" (since
you mention it).  Yes, use that.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.	--Roger Brinner

Re: Does PostgreSQL support job?

От:
"Daniel Caune" <daniel.caune@ubisoft.com>
Дата:


> -----Message d'origine-----
> De : Owen Jacobson [mailto:ojacobson@osl.com]
> Envoyé : mercredi, février 01, 2006 18:00
> À : Daniel Caune; pgsql-sql@postgresql.org
> Objet : RE: [SQL] Does PostgreSQL support job?
> 
> Daniel Caune wrote:
> > Hi,
> >
> > I try to find in the documentation whether PostgreSQL supports job,
> > but I miserably failed.  Does PostgreSQL support job?  If not, what
> > is the mechanism mostly adopted by PostgreSQL administrators for
> > running jobs against PostgreSQL?  I was thinking about using
> > cron/plsql/sql-scripts on Linux.
> 
> The answer really depends on what you mean by "jobs".  If you have a
> database task that can be expressed as a series of commands with no
> interaction involved, you can just put those commands in a file (your-job-
> name.sql) and run it using psql and cron:
> 
> # replace leading stars with cron time settings
> * * * * *     psql your-database -i your-job-name.sql
> 

Yes, that's it.  A job is a task, i.e. set of statements, which is scheduled to run against a RDBMS at periodical times.  Some RDBMS, such as SQL Server and Oracle, support that feature, even if such a feature is managed differently from a RDBMS to another.

OK.  I get it.  I will use cron and psql as I was planning to do so.


> If you need something more complex, either a function which is executed
> from a script or a full-blown client program may be required.  IME that's
> fairly rare.
> 

I'm not sure to understand.  Why calling a function from a script is different from executing a series of SQL commands?  I mean, I can run a script defined as follows:

SELECT myjob();

where myjob is a stored procedure such as:

CREATE OR REPLACE FUNCTION myjob() RETURNS void
AS $$ 
END;
$$ LANGUAGE PLPGSQL;

Does that make sense?


--
Daniel CAUNE

Re: Does PostgreSQL support job?

От:
Alvaro Herrera <alvherre@commandprompt.com>
Дата:
Daniel Caune wrote:

> Yes, that's it.  A job is a task, i.e. set of statements, which is
> scheduled to run against a RDBMS at periodical times.  Some RDBMS,
> such as SQL Server

..., the current alpha MySQL, ...

> and Oracle, support that feature, even if such a
> feature is managed differently from a RDBMS to another.


I was amused when I read the MySQL news in LWN.net, because most
comments were things like "what the hell has this half-baked feature has
to do in a RDBMS anyway".

http://lwn.net/Articles/167895/

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Does PostgreSQL support job?

От:
Daniel CAUNE <d.caune@free.fr>
Дата:


> -----Message d'origine-----
> De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de Alvaro Herrera
> Envoyé : mercredi 1 février 2006 19:28
> À : Daniel Caune
> Cc : Owen Jacobson; pgsql-sql@postgresql.org
> Objet : Re: [SQL] Does PostgreSQL support job?
> 
> Daniel Caune wrote:
> 
> > Yes, that's it.  A job is a task, i.e. set of statements, which is
> > scheduled to run against a RDBMS at periodical times.  Some RDBMS,
> > such as SQL Server
> 
> ..., the current alpha MySQL, ...
> 
> > and Oracle, support that feature, even if such a
> > feature is managed differently from a RDBMS to another.
> 
> 
> I was amused when I read the MySQL news in LWN.net, because most
> comments were things like "what the hell has this half-baked feature has
> to do in a RDBMS anyway".
> 
> http://lwn.net/Articles/167895/
> 

It's true that implementing a job management within an RDBMS is somewhat reinventing the wheel, especially on UNIX systems where cron exists (even on Windows, which supports scheduled tasks).  Anyway, job support within a RDBMS sounds more like a facility.

"While I have built a number of large and small applications with various time-based event scheduling tables stored in an SQL database, including things like triggers that send asynchronous notifications to daemon clients to advise them to re-query for updated schedules, it never in my wildest imaginings occured to me to actually initiate execution autonomously from the database back end."
[zblaxell, 2006-01-25, http://lwn.net/Articles/167895/]

Well, perhaps zblaxell has only worked on operational systems (OLTP), but such autonomy is sometimes useful in low-cost business intelligence systems (OLAP).

--
Daniel CAUNE


Re: Does PostgreSQL support job?

От:
"Magnus Hagander" <mha@sollentuna.net>
Дата:
> > > I try to find in the documentation whether PostgreSQL 
> supports job, 
> > > but I miserably failed.  Does PostgreSQL support job?  If 
> not, what 
> > > is the mechanism mostly adopted by PostgreSQL administrators for 
> > > running jobs against PostgreSQL?  I was thinking about using 
> > > cron/plsql/sql-scripts on Linux.
> > 
> > The answer really depends on what you mean by "jobs".  If 
> you have a 
> > database task that can be expressed as a series of commands with no 
> > interaction involved, you can just put those commands in a file 
> > (your-job-
> > name.sql) and run it using psql and cron:
> > 
> > # replace leading stars with cron time settings
> > * * * * *     psql your-database -i your-job-name.sql
> > 
> 
> Yes, that's it.  A job is a task, i.e. set of statements, 
> which is scheduled to run against a RDBMS at periodical 
> times.  Some RDBMS, such as SQL Server and Oracle, support 
> that feature, even if such a feature is managed differently 
> from a RDBMS to another.

You could look at pgagent, which comes with pgAdmin3
(http://www.pgadmin.org/docs/1.4/pgagent.html). It does some scheduling
that's a lot more advanced than you get from plain cron. And nice
pgadmin integrated management of course.

//Magnus

Re: Does PostgreSQL support job?

От:
Markus Schaber <schabi@logix-tt.com>
Дата:
Hi, Daniel,

Daniel Caune wrote:

> I'm not sure to understand.  Why calling a function from a script is different from executing a series of SQL commands?  I mean, I can run a script defined as follows:
> 
> SELECT myjob();
> 
> where myjob is a stored procedure such as:
> 
> CREATE OR REPLACE FUNCTION myjob()
>   RETURNS void
> AS $$
>   
> END;
> $$ LANGUAGE PLPGSQL;
> 
> Does that make sense?

It does make sense if myjob() does more than just execute a bunch of
statements, e. G. it contains if(), loops or something else.

PLPGSQL is turing complete, plain SQL is not.

Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Does PostgreSQL support job?

От:
"Daniel Caune" <daniel.caune@ubisoft.com>
Дата:

> > I'm not sure to understand.  Why calling a function from a script is
> different from executing a series of SQL commands?  I mean, I can run
a
> script defined as follows:
> >
> > SELECT myjob();
> >
> > where myjob is a stored procedure such as:
> >
> > CREATE OR REPLACE FUNCTION myjob()
> >   RETURNS void
> > AS $$
> >   
> > END;
> > $$ LANGUAGE PLPGSQL;
> >
> > Does that make sense?
> 
> It does make sense if myjob() does more than just execute a bunch of
> statements, e. G. it contains if(), loops or something else.
> 
> PLPGSQL is turing complete, plain SQL is not.
> 

Yes, indeed, that was the idea!

Re: Does PostgreSQL support job?

От:
Markus Schaber <schabi@logix-tt.com>
Дата:
Hi, Daniel,

Daniel Caune wrote:
>>> I'm not sure to understand.  Why calling a function from a script is
>>> different from executing a series of SQL commands? 

[snip]
>>>Does that make sense?
>>It does make sense if myjob() does more than just execute a bunch of
>>statements, e. G. it contains if(), loops or something else.
>>PLPGSQL is turing complete, plain SQL is not.
> Yes, indeed, that was the idea!

There's another reason: For updating the cron job SQL commands, you need
root access (or at least shell access) to the database machine. For
updating a stored procedure, you need just the appropriate rights in the
database.

On larger deployments, this can be an important difference.

Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Does PostgreSQL support job?

От:
"Daniel Caune" <daniel.caune@ubisoft.com>
Дата:
> Daniel Caune wrote:
> >>> I'm not sure to understand.  Why calling a function from a script
is
> >>> different from executing a series of SQL commands?
> 
> [snip]
> >>>Does that make sense?
> >>It does make sense if myjob() does more than just execute a bunch of
> >>statements, e. G. it contains if(), loops or something else.
> >>PLPGSQL is turing complete, plain SQL is not.
> > Yes, indeed, that was the idea!
> 
> There's another reason: For updating the cron job SQL commands, you
need
> root access (or at least shell access) to the database machine. For
> updating a stored procedure, you need just the appropriate rights in
the
> database.
> 
> On larger deployments, this can be an important difference.
> 

You are absolutely right.  That is such detail I was thinking over.
Managing stored procedures into a RDBMS seems less laborious than
modifying some SQL scripts on the file system.  I mean there is always a
need to define initially a script, run by the cron/psql couple, which
calls a stored procedure responsible for doing the job ("SELECT
myjob();").  Therefore it is easier to modify implementation details of
the job without having to modify the script run by the cron/psql.  On
another hand, it seems easier to test modification by patching a stored
procedure directly in the RDBMS and making some tests on-the-fly.

--
Daniel CAUNE

Re: Does PostgreSQL support job?

От:
Achilleus Mantzios <achill@matrix.gatewaynet.com>
Дата:
O Markus Schaber έγραψε στις Feb 2, 2006 :

> Hi, Daniel,
> 
> Daniel Caune wrote:
> 
> > I'm not sure to understand.  Why calling a function from a script is different from executing a series of SQL commands?  I mean, I can run a script defined as follows:
> > 
> > SELECT myjob();
> > 
> > where myjob is a stored procedure such as:
> > 
> > CREATE OR REPLACE FUNCTION myjob()
> >   RETURNS void
> > AS $$
> >   
> > END;
> > $$ LANGUAGE PLPGSQL;
> > 
> > Does that make sense?
> 
> It does make sense if myjob() does more than just execute a bunch of
> statements, e. G. it contains if(), loops or something else.
> 
> PLPGSQL is turing complete, plain SQL is not.

Hmmmm is SQL equally powerful as a pushdown automaton then???

Just kidding!

> 
> Markus
> 

-- 
-Achilleus


Re: Does PostgreSQL support job?

От:
Markus Schaber <schabi@logix-tt.com>
Дата:
H, Achilleus,

Achilleus Mantzios wrote:

>>PLPGSQL is turing complete, plain SQL is not.
> Hmmmm is SQL equally powerful as a pushdown automaton then???

SQL is _not_ a programming language, it is a query language. It is not
meant to be turing complete.

Just as e. G. HTML, CSS or RFC2822 are structural or layout languages,
but not programming languages.

> Just kidding!

Now, you're kidding. :-)

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Does PostgreSQL support job?

От:
Achilleus Mantzios <achill@matrix.gatewaynet.com>
Дата:
O Markus Schaber έγραψε στις Feb 2, 2006 :

> H, Achilleus,
> 
> Achilleus Mantzios wrote:
> 
> >>PLPGSQL is turing complete, plain SQL is not.
> > Hmmmm is SQL equally powerful as a pushdown automaton then???
> 
> SQL is _not_ a programming language, it is a query language. It is not
> meant to be turing complete.
> 
> Just as e. G. HTML, CSS or RFC2822 are structural or layout languages,
> but not programming languages.
> 
> > Just kidding!
> 
> Now, you're kidding. :-)

Well, if we add one stack to SQL it will kick some major PASCAL ass!

> 
> HTH,
> Markus
> 

-- 
-Achilleus


FAQ