Обсуждение: 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