pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes PostgreSQL support job?
16+ messages / 9 participants
[nested] [flat]
* Does PostgreSQL support job?
@ 2006-02-01 22:53 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 2 replies; 16+ messages in thread
From: Daniel Caune @ 2006-02-01 22:53 UTC (permalink / raw)
To: pgsql-sql
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 :-))
--
Daniel CAUNE
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-01 22:56 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 0 replies; 16+ messages in thread
From: Bruce Momjian @ 2006-02-01 22:56 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-01 22:59 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 0 replies; 16+ messages in thread
From: Daniel Caune @ 2006-02-01 22:59 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql
> -----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!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-01 22:59 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 16+ messages in thread
From: Owen Jacobson @ 2006-02-01 22:59 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-01 23:00 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 0 replies; 16+ messages in thread
From: Andrew Sullivan @ 2006-02-01 23:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-01 23:25 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 2 replies; 16+ messages in thread
From: Daniel Caune @ 2006-02-01 23:25 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> -----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 $$
<a-lot-of-complex-stuff-here>
END;
$$ LANGUAGE PLPGSQL;
Does that make sense?
--
Daniel CAUNE
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 00:28 Alvaro Herrera <alvherre@commandprompt.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2006-02-02 00:28 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 02:53 Daniel CAUNE <d.caune@free.fr>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 16+ messages in thread
From: Daniel CAUNE @ 2006-02-02 02:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 08:49 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 16+ messages in thread
From: Magnus Hagander @ 2006-02-02 08:49 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> > > 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 14:16 Markus Schaber <schabi@logix-tt.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
1 sibling, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-02-02 14:16 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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 $$
> <a-lot-of-complex-stuff-here>
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 14:19 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 1 reply; 16+ messages in thread
From: Daniel Caune @ 2006-02-02 14:19 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> > 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 $$
> > <a-lot-of-complex-stuff-here>
> > 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!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 14:25 Markus Schaber <schabi@logix-tt.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 0 replies; 16+ messages in thread
From: Markus Schaber @ 2006-02-02 14:25 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 15:21 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 0 replies; 16+ messages in thread
From: Daniel Caune @ 2006-02-02 15:21 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 15:38 Markus Schaber <schabi@logix-tt.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Markus Schaber @ 2006-02-02 15:38 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Daniel Caune <daniel.caune@ubisoft.com>; Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 16:33 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleus Mantzios @ 2006-02-02 16:33 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Daniel Caune <daniel.caune@ubisoft.com>; Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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 $$
> > <a-lot-of-complex-stuff-here>
> > 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does PostgreSQL support job?
@ 2006-02-02 16:49 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 0 replies; 16+ messages in thread
From: Achilleus Mantzios @ 2006-02-02 16:49 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Daniel Caune <daniel.caune@ubisoft.com>; Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2006-02-02 16:49 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-01 22:53 Does PostgreSQL support job? Daniel Caune <daniel.caune@ubisoft.com>
2006-02-01 22:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-02-01 23:00 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-01 22:59 Re: Does PostgreSQL support job? Daniel Caune <daniel.caune@ubisoft.com>
2006-02-01 22:59 Re: Does PostgreSQL support job? Owen Jacobson <ojacobson@osl.com>
2006-02-01 23:25 Re: Does PostgreSQL support job? Daniel Caune <daniel.caune@ubisoft.com>
2006-02-02 00:28 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-02-02 02:53 ` Daniel CAUNE <d.caune@free.fr>
2006-02-02 14:16 ` Markus Schaber <schabi@logix-tt.com>
2006-02-02 16:33 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-02-02 15:38 ` Markus Schaber <schabi@logix-tt.com>
2006-02-02 16:49 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-02-02 08:49 Re: Does PostgreSQL support job? Magnus Hagander <mha@sollentuna.net>
2006-02-02 14:19 Re: Does PostgreSQL support job? Daniel Caune <daniel.caune@ubisoft.com>
2006-02-02 14:25 ` Markus Schaber <schabi@logix-tt.com>
2006-02-02 15:21 Re: Does PostgreSQL support job? Daniel Caune <daniel.caune@ubisoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox