#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    13

    PL/PGSQL Stored Procs and Email


    Hi all. As some of you may remember I've been writing a CMS for the university where I work using PostgreSQL and PHP.

    I'm at the point where I now need to write some stored procedures to carry out some logic which I'd rather have in the DB than in PHP, but I have some questions before I proceed.

    1) Is it possible to send emails from within a stored procedure using PL/PGSQL or do I need to use PL/tcl?

    Has anyone had any experience with the procedure found at http://sourceforge.net/projects/pgmail/ ? It's a PL/tcl stored procedure which sends an email based on a few parameters passed to it.

    2) Oracle has a feature, DBMS JOBS, which allows jobs within the database to be run at a scheduled time. Is there a built in way to do this in PostgreSQL, or do I need to write a cron that will do this for me by executing a stored procedure?

    What I'm trying to do is this. I have two tables, cms_content and cms_pending_content, and I'm going to write a procedure that I want to run every minute that will examine these two tables and determine if there is any content to be updated, and if so it will perform the necessary updates.

    Is there a way to set this up native to the db, or do I need to write a cron job for this?

    If anyone out there could provide a little info for these two questions I would greatly appreciate it.

    Thanks in advance,
    Pablo
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Sorry I haven't had to do this, but you may want to also check out, plphp, the PHP procedural language extension for Postgres as well, although it's currently in beta if you're used to sending mail via PHP.

    Also, haven't heard of any postgres internal cron settings, so I can't help out with that either. I usually just use normal cron jobs either to a file with SQL commands, or if I need to do a little more processing/formatting write up a PHP script and use wget.
    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Hi Pablo,

    Here are some things to think about:

    1. Sending an email directly from a PostgreSQL procedure requires that this procedural language be installed in "untrusted" mode. For example, 'createlang plperlu' installes Perl as a procedural language in untrusted mode, while 'createlang plperl' installs it in trusted mode. Untrusted procedural languages have complete access to the operating system command-line and system calls (of course, these calls are run under the user of the postgres daemon). Currently, I believe Perl, TCL, and PHP can be set up in untrusted mode.

    2. Currently there is no built-in "jobs" control system, as in Oracle, although pgJobs is in the planning stage. So, for now you will need to use an external means to do scheduled execution. However the external script can be extremely simple, just making one query to a stored procedure every minute, while all the actual scheduling logic is preserved in tables. Just use the stored procedure as a look-up to the query to be executed at that point.

    3. A cron job is one way to do it, but you could also just establish a very simple PHP (CLI mode) or Perl script, running as a daemon, which wakes up every n seconds and queries your "jobs" table. This way, you can keep the action of sending emails or other such outside of the database, which is arguably a little more secure, and a little less likely to cause database lock-up, etc... One way to think of it is to actually place PHP code inside the jobs table, and use the external PHP daemon to eval() that code in order to execute the action (a nice little Code Tables experiment ).

    For an example of a PHP script that runs as a daemon, just think along these lines (lookout for pseudocode, and check for actual path to PHP executable on your system):
    PHP Code:
    #!/usr/bin/php
    <?php

    // put database connection here

    // remember, this script will keep a single database connection open as long as it is running,
    // so you might need to run a macro scheduler that closes and reopens the DB connection every few hours

    // endless loop
    while(true)
    {

       
    //sleep for 10 seconds, then wake up to perform the action below
       
    sleep(10); 

       
    // or, if you really need fine-grained scheduling, use usleep()
       //usleep(100000); //executes every 10th of a second

       
    if(!$result pg_query("my query goes here"))
       {
            
    // if there is an error, email me
            
    mail($me'DB scheduler error'$pg_last_error);
       }

         
    //if query is successful do something with results
        
    else
        {
              
    //get results here
              
    $code_from_table pg_resultetc...)

              if(!eval(
    $code_from_table))
              {
                   
    // OK, email me if the code doesn't eval
                   
    mail($mejob execution failure$code_from_table);
              }
        }

    }

    pg_close($connection);

    ?>
    Obviously, you will need extra logic for error handling, conditional execution, etc... but you get the basic idea. It's pretty simple, really. Just create this, and run as a daemon with './my_php_jobs &' and leave it running in the background. the sleep() or usleep() function will keep it from taking up a lot of resources, but you should keep a sharp eye on 'top' for the first few versions, until you are sure it is stable. Querying PostgreSQL even several times a second is not as big a CPU drag as you think it might be-- remember, PHP running as a CLI is not invoking the full Apache/PHP/DB-connection every time it runs a query; this is simply repeated querying on an already-open connection.

    Of course, an approach like this opens up a lot of possibilities for additional logic, system management, etc..., especially considering PHP's process-control, shared-memory. and POSIX functionality. Have fun!

    (P.S. somewhat off-topic, but also think about the possibilities for a simple PHP "middleware" layer that pools DB connections and passes off objects to the PHP/Apache process. Ooohhh, shiny... )
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    P.S. somewhat off-topic, but also think about the possibilities for a simple PHP "middleware" layer that pools DB connections and passes off objects to the PHP/Apache process. Ooohhh, shiny.
    Hehe, I've thought about this as well. Not only DB connection pooling but object sharing as well (after all the connections will be objects so managing other objects should be trivial.) Imagine having a result object whose handle can be passed from page to page....

    However, I've decided to shelve the project until PHP5 is stable to take advantage of the new sockets implementation inside streams (and until I have some more free time).

    Only one thing to make it complete, one of the (still under development) PHP compilers...
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Rod, want any help?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Sure. Like I said I don't have much time to work on it... time should be freeing up around the end of January. But it would be good to kick around a few ideas. Send me an email and we can get started with that.

    BTW, I don't like the GPL, if we release it I'd like to use BSDL or maybe the PHP license.

    On another note, too bad Vulcan SRM appears to be in a development black hole...

IMN logo majestic logo threadwatch logo seochat tools logo