Delivery-Date: Fri, 17 Sep 93 08:31:33 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9309171528.AA02949@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Fri, 17 Sep 93 10:28:06 CDT
Message-Id: <9309171528.AA19224@aristotle.ils.nwu.edu>
To: postgres@postgres.Berkeley.EDU
From: neves@ils.nwu.edu
Subject: more survey summary
Resent-To: postgres-dist
Resent-Date: Fri, 17 Sep 93 08:28:48 PDT

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.