Thread: Does PostgreSQL support job?
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 10.0pt;font-family:Arial">I try to find in the documentation whether PostgreSQL supports job, but I miserably failed. DoesPostgreSQL support job? If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs againstPostgreSQL? I was thinking about using cron/plsql/sql-scripts on Linux.</span></font><p class="MsoNormal"><font face="Arial"size="2"><span lang="EN-CA" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 10.0pt;font-family:Arial">Thanks (Tom Lane </span></font><font face="Wingdings" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:Wingdings">J</span></font><fontface="Arial" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:Arial">)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 10.0pt;font-family:Arial">--</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Arial" size="2"><spanlang="EN-CA" style="font-size: 10.0pt;font-family:Arial">Daniel CAUNE</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Times NewRoman" size="3"><span lang="EN-CA" style="font-size:12.0pt"> </span></font></div>
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, Pennsylvania19073
> -----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!
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 commandswith no interaction involved, you can just put those commands in a file (your-job-name.sql) and run it using psqland 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 maybe required. IME that's fairly rare. -Owen
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
> -----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 froma 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
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
> -----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 systemswhere cron exists (even on Windows, which supports scheduled tasks). Anyway, job support within a RDBMS sounds morelike a facility. "While I have built a number of large and small applications with various time-based event scheduling tables stored in anSQL database, including things like triggers that send asynchronous notifications to daemon clients to advise them to re-queryfor updated schedules, it never in my wildest imaginings occured to me to actually initiate execution autonomouslyfrom 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 businessintelligence systems (OLAP). -- Daniel CAUNE
> > > 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
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
> > 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!
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
> 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
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
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
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