Thread: How to write jobs in postgresql

On Wed, Aug 03, 2005 at 09:08:48AM +0530, chiranjeevi.i wrote:
>     Is it possible to write jobs in postgresql & if possible how should I
> write .please help me.

Please explain what you mean by "job."  What are you trying to do?

--
Michael Fuhr

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Fri, Aug 05, 2005 at 06:28:04PM +0530, chiranjeevi.i wrote:
> I have to dump up my database into a text file(using copy) at 2 am on every
> day. And from that I have to get the database details. & please don't
> suggest me to go for CRON as it has caused so many problems in my server.

I'll suggest cron anyway because that's the usual way to schedule
commands to run at regular times on Unix-like platforms.  If you're
having problems with cron then you should investigate and fix those
problems.

The pgAdmin folks have been working on a scheduler called pgAgent.
Use a search engine to find more information about it.

--
Michael Fuhr

"chiranjeevi.i" <chiranjeevi.i@pyronetworks.com> writes:

>  Is it possible to write jobs in postgresql & if possible how should I
> write .please help me.

What does "write jobs" mean?

-Doug

Douglas McNaught wrote:
>> Is it possible to write jobs in postgresql & if possible how should I
>>write .please help me.
>
>
> What does "write jobs" mean?
>
I'm assuming this person has an Oracle background, if so, jobs are Oracle's
equivalent to a built-in cron scheduler. This has been discussed extensively in
these lists in the past - whether or not to implement an pg version of Oracle's
job mechanism, etc.

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

chiranjeevi.i@pyronetworks.com ("chiranjeevi.i") writes:
>     Is it possible to write jobs in postgresql & if possible how
> should I write .please help me.:p>

What do you mean by "job?"

If you mean "run some sort of scheduled script," then that represents
something that you could do using your favorite operating system's
scheduling system.

On Unix, we normally schedule batch jobs using cron.

If you are running on Windows, I'm not particularly familiar with
whatever it is that you would run there.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

chiranjeevi.i wrote:
> Hi Team Members,
>
>     Is it possible to write jobs in postgresql & if possible how
> should I write .please help me.

See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's in
the planning stages.

--
Guy Rouillier

On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote:
> chiranjeevi.i wrote:
> > Hi Team Members,
> >
> >     Is it possible to write jobs in postgresql & if possible how
> > should I write .please help me.
>
> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's in
> the planning stages.

Actually, it's currently in the going nowhere stage since no one's
expressed any interest in it. Anyone who's interested is encouraged to
join the mailing list and post what they'd like to see from the project.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

# jnasby@pervasive.com / 2005-08-15 20:25:20 -0500:
> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote:
> > chiranjeevi.i wrote:
> > > Hi Team Members,
> > >
> > >     Is it possible to write jobs in postgresql & if possible how
> > > should I write .please help me.
> >
> > See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's in
> > the planning stages.
>
> Actually, it's currently in the going nowhere stage since no one's
> expressed any interest in it. Anyone who's interested is encouraged to
> join the mailing list and post what they'd like to see from the project.

    What's the advantage over system-native (cron etc) means?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Roman Neuhauser wrote:
> # jnasby@pervasive.com / 2005-08-15 20:25:20 -0500:
>> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote:
>>> chiranjeevi.i wrote:
>>>> Hi Team Members,
>>>>
>>>>     Is it possible to write jobs in postgresql & if possible how
>>>> should I write .please help me.
>>>
>>> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's
>>> in the planning stages.
>>
>> Actually, it's currently in the going nowhere stage since no one's
>> expressed any interest in it. Anyone who's interested is encouraged
>> to join the mailing list and post what they'd like to see from the
>> project.
>
>     What's the advantage over system-native (cron etc) means?

Search the archives, you'll find numerous discussions on this topic,
including the one that prompted Jim to create the project.  As of now,
the project is pre-concept stage, making it impossible to identify its
advantages.  One possible advantage would be recording job schedules in
the database where they can be easily managed, but that's small.  A
bigger advantage can be seen in the approach that Oracle takes, where
authentication happens when the job is created.  So you don't need to
provide credentials at run time, which in the case of cron jobs would
mean putting passwords into shell scripts.

--
Guy Rouillier

On Tue, Sep 06, 2005 at 04:29:31PM -0500, Guy Rouillier wrote:
> Roman Neuhauser wrote:
> > # jnasby@pervasive.com / 2005-08-15 20:25:20 -0500:
> >> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote:
> >>> chiranjeevi.i wrote:
> >>>> Hi Team Members,
> >>>>
> >>>>     Is it possible to write jobs in postgresql & if possible how
> >>>> should I write .please help me.
> >>>
> >>> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's
> >>> in the planning stages.
> >>
> >> Actually, it's currently in the going nowhere stage since no one's
> >> expressed any interest in it. Anyone who's interested is encouraged
> >> to join the mailing list and post what they'd like to see from the
> >> project.
> >
> >     What's the advantage over system-native (cron etc) means?
>
> Search the archives, you'll find numerous discussions on this topic,
> including the one that prompted Jim to create the project.  As of now,
> the project is pre-concept stage, making it impossible to identify its
> advantages.  One possible advantage would be recording job schedules in
> the database where they can be easily managed, but that's small.  A
> bigger advantage can be seen in the approach that Oracle takes, where
> authentication happens when the job is created.  So you don't need to
> provide credentials at run time, which in the case of cron jobs would
> mean putting passwords into shell scripts.

As Guy points out, this is all in a very formative stage right now
(although someone is supposed to be sending me some code), but here's
some other advantages:

This would be platform-independant, which is important now that we
support windows natively.

The interface would be in SQL (probably a set of functions), making it
much easier to control programatically.

Scheduling modes that are either difficult or impossible to do with cron
become available, such as sub-minute scheduling (ie: every 30 seconds),
running something at server start-up/shut-down, running something based
on a notify, etc.

I encourage anyone who's interested in this to join the mailing list at
http://lists.pgfoundry.org/mailman/listinfo/pgjob-devel
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

neuhauser@sigpipe.cz (Roman Neuhauser) writes:
> # jnasby@pervasive.com / 2005-08-15 20:25:20 -0500:
>> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote:
>> > chiranjeevi.i wrote:
>> > > Hi Team Members,
>> > >
>> > >     Is it possible to write jobs in postgresql & if possible how
>> > > should I write .please help me.
>> >
>> > See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/.  It's in
>> > the planning stages.
>>
>> Actually, it's currently in the going nowhere stage since no one's
>> expressed any interest in it. Anyone who's interested is encouraged to
>> join the mailing list and post what they'd like to see from the project.
>
>     What's the advantage over system-native (cron etc) means?

You could doubtless do things with "pgcron" you cannot readily do with
cron, such as:

 1.  Expressing dependancies between jobs (e.g. - run A after running
     B, don't start C if B is still running)

 2.  More specific time increments than cron offers (e.g. - heading
     nearer to granularity of seconds)

 3.  An integrated way of logging what ran when (rather than either
     stuffing logging code into each cron job or rummaging thru
     cron logs)

 4.  You could use SQL queries to request information about jobs.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/sap.html
"LISP car-and-cdr worlds are a  more reasonable representation of  the
things   that make life   interesting  than fixed  decimal(15) or FILE
OLDMSTR RECORD IS PAYROLL."  -- Bernie Greenberg.

>  3.  An integrated way of logging what ran when (rather than either
>      stuffing logging code into each cron job or rummaging thru
>      cron logs)
Cron can log to syslog.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Karsten Hilbert wrote:

>> 3.  An integrated way of logging what ran when (rather than either
>>     stuffing logging code into each cron job or rummaging thru
>>     cron logs)
>>
>>
>Cron can log to syslog.
>
>Karsten
>
>
And your cron scripts could log to your database log tables if that is
what you were getting at.......

Chris Travers
Metatron Technology Consulting

On Thu, Sep 08, 2005 at 10:27:59AM -0700, Chris Travers wrote:
> Karsten Hilbert wrote:
>
> >>3.  An integrated way of logging what ran when (rather than either
> >>    stuffing logging code into each cron job or rummaging thru
> >>    cron logs)
> >>
> >>
> >Cron can log to syslog.
> >
> >Karsten
> >
> >
> And your cron scripts could log to your database log tables if that is
> what you were getting at.......

True and true, but both of those require more work to setup. I'm not
even sure if you can log only specific cronjobs to syslog.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461