Thread: Alarm function in PL/pgSQL

Hi,

Is it possible to set an alarm within a PL/pgSQL function?  By "alarm"
I mean a function which is invoked some defined time in the future,
even after the original function has terminated and returned a value
to the client.

I want an invocation of function FOO to set a "state" column of a given
table row to "temporary".  After a predefined period (say, one hour),
function BAR should then automatically be invoked; this latter function
would among other things change the "state" column to "permanent".

Thanks in advance!
Jon

On Mon, Jul 18, 2011 at 4:38 PM, Jon Smark <jon.smark@yahoo.com> wrote:
> Hi,
>
> Is it possible to set an alarm within a PL/pgSQL function?  By "alarm"
> I mean a function which is invoked some defined time in the future,
> even after the original function has terminated and returned a value
> to the client.
>
> I want an invocation of function FOO to set a "state" column of a given
> table row to "temporary".  After a predefined period (say, one hour),
> function BAR should then automatically be invoked; this latter function
> would among other things change the "state" column to "permanent".

I don't think this is really possible with postgres PLs generally.
Typically what you have to do is have a function that is called on an
interval that checks for alarms and runs them.

merlin

Hi,

> I don't think this is really possible with postgres PLs generally.
> Typically what you have to do is have a function that is called on an
> interval that checks for alarms and runs them.

Thanks for the prompt reply.  Just to clarify: you are saying that the
function that is called with a given periodicity must be so from *outside*
PG, ie, from the client application, right?  I mean, there is no way
strictly internal to PG to have a function be called every given number
of seconds?

Cheers,
Jon

Jon Smark wrote:
>> I don't think this is really possible with postgres PLs generally.
>> Typically what you have to do is have a function that is called on an
>> interval that checks for alarms and runs them.
>
> Thanks for the prompt reply.  Just to clarify: you are saying that the
> function that is called with a given periodicity must be so from *outside*
> PG, ie, from the client application, right?  I mean, there is no way
> strictly internal to PG to have a function be called every given number
> of seconds?

You should lookup Postgres' LISTEN and NOTIFY feature, and see if that is useful
in doing what you want.  For example, if this is possible, have a
database-transaction-external task running which is LISTENing for your state
setting signal, and then it waits for an hour and then calls the function.  Your
first function would send the NOTIFY signal. -- Darren Duncan

On 19/07/2011 6:35 AM, Jon Smark wrote:
> Thanks for the prompt reply. Just to clarify: you are saying that the
> function that is called with a given periodicity must be so from
> *outside* PG, ie, from the client application, right? I mean, there is
> no way strictly internal to PG to have a function be called every
> given number of seconds? Cheers, Jon
That's right. PostgreSQL has no built in scheduler feature.

PgAgent is designed to cover that need, so you should probably start there.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

On 07/19/2011 01:35 AM, Jon Smark wrote:

> Hi,
>
>> I don't think this is really possible with postgres PLs generally.
>> Typically what you have to do is have a function that is called on an
>> interval that checks for alarms and runs them.
> Thanks for the prompt reply.  Just to clarify: you are saying that the
> function that is called with a given periodicity must be so from *outside*
> PG, ie, from the client application, right?  I mean, there is no way
> strictly internal to PG to have a function be called every given number
> of seconds?
>
> Cheers,
> Jon
Hi Jon,

The generally accepted way of doing this is to have a cron job (outside
of the database) calling a function which checks a polling table for
things it should process. What you would do is put the time you want a
row processed at and then your cron job will call a function that
processes all rows that are overdue.
Pg Agent will do that same thing as a cron job, as Craig mentioned.

Sim