Thread: Question about reliability?
I have my server set up with one production postgres installation, and each user can have their own database for their own use (usually web-related). There are a limited number of trusted users, and I recently allowed one to define a C-language stored procedure. Unfortunately, I *think* this causes the users database process to crash, roughly once a week (probably a memory leak). The result of this is that *all* postmaster processes die, which is actually a problem for the other users. Now the simple solution is to have one PG installation per user (or at least one PGDATA directory), but that does seem a little inefficient. I'd be interested to know the reason for killing the other backends; it does basically mean that each database has to have it's own PG installation if you want reliability. Would there be any potential to avoid these (possibly) unnecessary deaths? Does the WAL and recovery process have any influence here? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> I'd be interested to know the reason for killing the other backends;
Because they all share the same shared-memory pool. After a backend
crash you can't be sure whether shared memory is corrupted or not.
(Even if it's not been actively scribbled on by code gone wild, there
are still going to be proc-table and lock-table entries for the failed
backend, and we have no other mechanism for getting rid of those.)
> Would there be any potential to avoid these (possibly) unnecessary deaths?
No, at least it'll never get my vote.
There is a reason why C-language procedure creation is only allowed to
the superuser ;-) --- both in terms of reliability and in terms of
ability to access data, you are granting the author of a C procedure
100% life-and-death power over your installation. If you find that his
code is not reliable enough for you, either remove it, fix it, or make
a separate playpen installation that he can crash at whim.
regards, tom lane
At 10:43 9/11/00 -0500, Tom Lane wrote: > >> Would there be any potential to avoid these (possibly) unnecessary deaths? > >No, at least it'll never get my vote. > Presumably other than limiting to one 'database' per installation? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 10:43 AM 11/9/00 -0500, Tom Lane wrote: >> Would there be any potential to avoid these (possibly) unnecessary deaths? > >No, at least it'll never get my vote. Besides, it's not that difficult for an application to recover from these prophylactic backend deaths. My PG driver for AOLserver does so transparently, retrying queries that get the "sorry I've been asked to shut down because some other backend screwed up and died - please retry your query" but returning an error for the actual query that caused a backend to hose itself. The code using the driver is unaware that anything has happened (other than the thread issuing the query that hosed the backend that died in execution, of course). - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.