|
Home > Archive > PostgreSQL SQL > February 2006 > Does PostgreSQL support job?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Does PostgreSQL support job?
|
|
| Daniel Caune 2006-02-01, 8:28 pm |
| 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
| |
| Bruce Momjian 2006-02-01, 8:28 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Daniel Caune 2006-02-01, 8:28 pm |
|
> -----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:
>
> The unix cron systems is what most people use.
>
OK. Thanks. That's fine!
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Owen Jacobson 2006-02-01, 8:28 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Andrew Sullivan 2006-02-01, 8:28 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Daniel Caune 2006-02-01, 8:28 pm |
|
> -----Message d'origine-----
> De_: Owen Jacobson & #91;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:
>
> 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 anothe
r.
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Alvaro Herrera 2006-02-01, 8:28 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Daniel CAUNE 2006-02-02, 3:25 am |
|
> -----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:
>
>
> ..., the current alpha MySQL, ...
>
>
>
> 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
| |
| Magnus Hagander 2006-02-02, 7:24 am |
| > > > I try to find in the documentation whether PostgreSQL
> supports job,
> not, what
> you have a
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Markus Schaber 2006-02-02, 9:27 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Daniel Caune 2006-02-02, 9:28 am |
|
> different from executing a series of SQL commands? I mean, I can run
a
> script defined as follows:
>
> 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!
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Markus Schaber 2006-02-02, 9:28 am |
| Hi, Daniel,
Daniel Caune wrote:
& #91;snip][color=dark
red]
> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Daniel Caune 2006-02-02, 9:28 am |
| > Daniel Caune wrote:
is[color=darkred]
>
> [snip]
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Achilleus Mantzios 2006-02-02, 11:24 am |
| O Markus Schaber Ýãñáøå óôéò Feb 2, 2006 :
> Hi, Daniel,
>
> Daniel Caune wrote:
>
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Markus Schaber 2006-02-02, 11:24 am |
| H, Achilleus,
Achilleus Mantzios wrote:
> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Achilleus Mantzios 2006-02-02, 11:24 am |
| O Markus Schaber Ýãñáøå óôéò Feb 2, 2006 :
> H, Achilleus,
>
> Achilleus Mantzios wrote:
>
>
> 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.
>
>
> Now, you're kidding. :-)
Well, if we add one stack to SQL it will kick some major PASCAL XXX!
>
> HTH,
> Markus
>
--
-Achilleus
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|