Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy.

This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes the query to take more and more memory until the entire machine gets stuck.

I'm looking for a PostgreSQL mechanism that can:

  • Limit the memory consumption (and perhaps other resources) used by a specific query.
  • Automatically stop queries whose execution time exceeds a certain threshold.

Any ideas?

share|improve this question
    
Why do you keep using a version that has a bug that bites you? Use a version that is fixed and your problem is gone. –  Frank Heikens Feb 21 '11 at 7:42
    
True, but sometimes there are bugs in our code, or just queries that take toooooooo long. I want them to die with log error message. –  Adam Matan Feb 21 '11 at 12:05
add comment

2 Answers

up vote 5 down vote accepted

To limit memory consumption, the main configuration parameter is work_mem. Since this applies per operation, not per query, you cannot simply set it to N if you want to spend N amount of memory, but you have to tweak and tune it a bit to get the desired result.

This doesn't help, however, in case of bugs and other memory leaks in the server code or extensions. You could control that with the process-specific resource limits controlled by ulimit. But if you reach the limit and memory allocation fails, what do you want to have happen? It probably won't behave too nicely. Better fix those bugs or use a different version.

The stop queries whose execution time passed a threshold, use the parameter `statement_timeout', e.g.,

SET statement_timeout TO '10min';
share|improve this answer
    
Great. Just what I was looking for. –  Adam Matan Feb 21 '11 at 12:05
add comment

Have a look at the Postgres Wiki:

PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

You can adjust performance settings on a per user or per database but not when to abort certain queries.

share|improve this answer
    
+1 What about setting a maximum to the amount of memory the entire cluster can consume? –  Adam Matan Feb 20 '11 at 12:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.