If you run a transaction with lots of server side functions that use a lot of memory, this can trigger the OOM killer in Linux, causing the PosgreSQL backend to receive a SIGKILL and all the associated bad stuff.
Tuning the OOM killer is not sufficient. No setting I've found for the OOM killer will guarantee that it won't SIGKILL a process that's essentially untenable anyway (because it's going to use more memory than actually exists on the system at some point anyway). Additionally, "add more RAM" doesn't "solve" the problem, it only delays it until datasets scale up to even larger transactions that use even more memory. This is particularly prevelent with Postgis, because some Postgis functions are very memory intesive, but I'd be willing to bet real money that I could trigger it with just about any stored procedure that allocates memory in such as way that it doesn't get reclaimed until the transaction completes. See as an example: https://trac.osgeo.org/postgis/ticket/3445 If anyone wants to investigate this but is having trouble reproducing, I can construct specific failure scenarios fairly easily. Another workaround is to run the offending statements in smaller transactional batches. This is the best solution I've found so far, but it's not quite ideal. In particular it requires the client program to reimplement transaction guarantees on the client side. Sometimes this isn't necessary, but other times it is. What I feel is the best way to mitigate the situation, is to have some setting that limits the maximum RAM any backend can consume. Attempting to exceed this limit would cause an error and rollback for that particular backend without affecting other backends. This would provide information to the client process that can be rationally interpreted by client code to result in either an error that a developer can understand, or possibly adaptive code that changes behavior to accomodate limits on the server side. My first question: does this setting exist somewhere and I'm simply not finding it for some reason? Assuming this doesn't exist (I haven't found it) my next question is whether there's a philosophical or technical reason that such a feature doesn't exist? Should I take this discussion to -hackers? -- Bill Moran <wmo...@potentialtech.com> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
