Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

pgagent — when triggers are too much

Posted by smathermather on July 8, 2010

This will be one of my shorter posts. pgadmin III allows for the development of scheduled tasks that run on your PostgreSQL database. As my PostGIS triggers have gotten more complicated, and since they run per transaction to keep ACID compliance, a big mess can be made by just updating a dataset (this is what vacuum is good for), and the time for inserts can be astronomical. Take, e.g. my previous post. Each record insert, because of the trigger, takes about 30 seconds to complete. That’s way to slow, especially if I want updates to happen during the business day. Enter pgagent.

pgagent has been well covered by other’s blogs, so I’ll post two links that were helpful to me for administering it in Windows:

http://www.mkyong.com/database/how-to-install-pgagent-on-windows-postgresql-job-scheduler/

http://www.postgresonline.com/journal/index.php?/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html

One Response to “pgagent — when triggers are too much”

  1. victor said

    Not so far I have found new cool tool to work with postgresql – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: