Thread: Run procedure at startup

Is there a way to run a pl/pgsql automatically at server startup ?

Is there a way to run a pl/pgsql function with an infinite loop as a daemon ?

Is there a way to start a pl/pgsql function that would persist after the user session has closed ?

Is there a way for an unprivileged user to delegate a task (allowed by superuser) to a superuser ?

Thanks in advance.

Hello

2009/7/27 Saleem EDAH-TALLY <nmset@netcourrier.com>:
> Hello,
>
> Is there a way to run a pl/pgsql automatically at server startup ?

no - only you can modify startup scripts

>
> Is there a way to run a pl/pgsql function with an infinite loop as a daemon
> ?

infinite loop is possible, but probably you need orafce or similar to
be infinite loop usable:

http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#DBMS_ALERT
http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#DBMS_PIPE

>
> Is there a way to start a pl/pgsql function that would persist after the
> user session has closed ?

yes - with some pooling sw like pgpool, bucardo and others

>
> Is there a way for an unprivileged user to delegate a task (allowed by
> superuser) to a superuser ?

yes - look on security definer flag
http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

>
> Thanks in advance.
>
>

regards
Pavel Stehule

Saleem EDAH-TALLY wrote:
> Is there a way to run a pl/pgsql automatically at server startup ?

in your postgres startup script launch a session with `psql ... -c "some
sql commands"` or `psql ... -f somescript.sql` ...


> Is there a way to run a pl/pgsql function with an infinite loop as a
> daemon ?

functions are called from within a transaction.   if you did this, that
transaction would never end, and this would prevent VACUUM from cleaning
up any freed tuples from newer than the start of that transaction.   Not
good.   you could, however, have a system daemon that periodically
invokes a plpgsql function.


> Is there a way to start a pl/pgsql function that would persist after
> the user session has closed ?

no.


> Is there a way for an unprivileged user to delegate a task (allowed by
> superuser) to a superuser ?

someone else will have to chime in here.

On 2009-07-27, Saleem EDAH-TALLY <nmset@netcourrier.com> wrote:
> --Boundary-00=_NZcbKOfdE2cMluA
> Content-Type: text/plain;
>   charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Hello,
>
> Is there a way to run a pl/pgsql automatically at server startup ?

pgagent perhaps? modify the startup scripts?

> Is there a way to run a pl/pgsql function with an infinite loop as a daemon ?

write a daemon (as a shell script if neccessary).
OTOH crontab or pgagent may be enough?

> Is there a way to start a pl/pgsql function that would persist after the user
> session has closed ?

pgagent perhaps?

> Is there a way for an unprivileged user to delegate a task (allowed by
> superuser) to a superuser ?

superuser specifys "security definer" when the superuser defines the
function.


><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p>Is there a way to run a pl/pgsql automatically at server
startup?<br> 
><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p>Is there a way to run a pl/pgsql function with an
infiniteloop as a daemon ?<br> 
><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p>Is there a way to start a pl/pgsql function that would
persistafter the user session has closed ?<br> 
><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p>Is there a way for an unprivileged user to delegate a
task(allowed by superuser) to a superuser ?<br> 
><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p>Thanks in advance.<br> 
><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br></p></body></html> 
> --Boundary-00=_NZcbKOfdE2cMluA--
>