
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   |/

Fri, 10 Nov 2000 00:08:22 +1100
Philip Warner <pjw@rhyme.com.au>
Question about reliability?
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

Thu, 09 Nov 2000 10:43:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about reliability? 
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   |/

Fri, 10 Nov 2000 03:00:37 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Question about reliability? 
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 Service and other goodies at
  http://donb.photo.net.

Thu, 09 Nov 2000 08:47:43 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Question about reliability? 