Subject: more survey summary

From: [email-masked]
Date: Fri, 17 Sep 93 10:28:06 CDT

Thanks to all who added their thoughts on using Postgres in a multi-user
environment.  Based on what I read it is problematic to use it with more
than one person writing to a database.
-David

Here are the messages I received:

-------------------------------------
I have been using postgres for just over six months now in a multi user
situation.  Our usual "high load" situation is to have two people
writing the database and any number of people viewing the database.
All the transactions are based on the same table.  We have in general
had no problems with the follwoing summary:

- retrieves are pretty reliable

- appends and deletes work fine as long as they are not in begin/end
transaction blocks.

- We get occasional deadlocks on replaces if the timing is bad.  I
would assume though that if we had more than two people writing to the
database the frequency of this would increase.

We are running postgres 4.1 under SunOS 4.1.3
-------------------------------------

David

I tried using Postgres (on Sun sparcs) in a lab with 15 students each with
their own database (which isn't quite what you want). In the end only 2 or 3
students at any one time could use the system without the postmaster
self destructing. I was impressed by the tenacity of the students.

I have heard similar tales from Wolverhampton, Coventry and Bradford in the UK

-------------------------------------

What I have learnt about Postgres and multi-user:

I group all related table updates into transactions as you should do
between 'begin' and 'end' or 'abort'. In order to avoid deadlock you
need get Postgres to acquire exclusive lock(s) to allow one transaction
through and the others to wait. From previous mail discussions the only
way to do this is by an 'append' to a table which locks it (the whole
table). In my case I have one dummy table which I append to to force
all other transactions to wait. Clearly if you have transactions which
access totally distinct tables you could have more than one dumy table
... but this is now in the realms of general DB design so I wont bore
you.

There is still a problem with Postgres though, my transactions take so
long that a waiting transaction times itself out thinking it's detected
deadlock.

The application that uses this code has not really been pushed hard in
multi-user use, I haven't had any corrupt DBs yet but my gut feeling is
that when the transactions start to fail Postgres is `fragile'. I can't
substantiate this and I'm still only on v3.1 so treat this as hearsay
or rumour.

My plug for new features for Postgres is to have control of locking and
transaction timeouts.

-------------------------------------

I've had simular experiences to the others who responded.
Any time I try to access a data base in parallel, eventually
one of the backend processes will hang.  It will eat CPU and
never terminate and all other backends just wait.

We've never had multiple people use the same database and
we probably never will.

---

From: [email-masked] (Paul M. Aoki)
Date: Fri, 17 Sep 93 11:16:27 -0700

in general, i would agree that multiuser reliability isn't very good.
there are some gotchas in protecting shared state [kristin wright
is looking at some of those] and the way fatal exceptions are handled
can make life really inconvenient (see below).

> database the frequency of this would increase.

there was a discussion of this a few months ago.  the moral equivalent
of update locks (and SELECT... FOR UPDATE) would help but i don't think 
there are any current plans to add those.

> their own database (which isn't quite what you want).

if you want to give people a taste of database use, why not use a real
SQL engine.  or, if you can't afford it, ingres89 (which doesn't do any
neat stuff but is smaller and more reliable and also runs on sparcs).

on the other hand, if people are *writing user extensions*, you want 
each of them under their own *postmaster* (meaning a separate PGDATA
and sufficient shared memory/semaphores for each).  why?  during the 
debugging phase, user-defined functions tend to cause the system to 
core (more so than usual :-).  when one backend cores, the postmaster 
takes down ALL of its other children (since the dead one may have 
corrupted shared state, i.e., the lock table and buffer pool).  even
ignoring other reliability issues, nobody can make *any* progress in 
this scenario.  (mike olson and jeff meredith tell some pretty good 
horror stories about trying to support the grad dbms class at UCB when 
people were trying to write ADTs and running under a single postmaster..)

you can use untrusted functions but only if the user-defined functions 
don't have to call into the backend server.

> In the end only 2 or 3

hmm.  i haven't been able to actually crash [core] the postmaster 
since it was rewritten for 4.1 (not that it's hard to crash the 
backend).  the major failure mode for the postmaster happens when 
it tries to reinitialize shared state and one of the sysV ipc calls 
fails for some reason.  something else i never had a chance to look
into .. :-P

> never terminate and all other backends just wait.

you know, i've never seen a 4.1 backend infinite-loop.  dump core, yes,
spin during deadlock, yes (though the deadlock period is relatively short),
but spin *forever*, never.  i'm sure someone here would be interested in 
knowing how to reproduce this.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki