Re: transaction timeout

On Tue, 2005-07-26 at 12:51, Dr NoName wrote:
> > If you have second database in the cluster is it
> > still operational when
> > the main database locks up?
>
> we don't have a DB cluster. It would be pretty useless
> since postgresql doesn't support distributed
> transactions.

You misunderstood his point. In PostgreSQL parlance, a "cluster" is a
single postmaster running on a single machine, with 1 or more
databases. So, what he wanted to know was, if your application is
hitting a database called fred, and you have a spare database named
wilma, would "psql wilma" work when the database is "locked up?"

Can you elaborate on what you mean by a database that is "locked up?"

> > Also it seems that some diagnostics are needed in
> > the client app to log
> > the crash event so you can determine which SQL
> > commands are causing the
> > lock.
>
> I'll try to get that next time it happens. But
> regardless of sql commands are running, I know what
> the root cause is: a client hangs while in
> transaction.

Actually, unless the client is holding a table level exclusive lock,
most transactions should continue.

> > Despite many years of writing buggy code I have not
> > yet locked a whole
> > DB in the fashion described. I can not see how a
> > simple select / insert
> > / update command sequence can achieve it unless
> > there is a particular
> > relation between the tables involved.
>
> As I have already said, I suspect this might be caused
> by a combination of an open transaction and a weekly
> VACUUM FULL. Does that sound right?

No, VAcuum full shouldn't cause this kind of issue. Now, if the
database is just running real slow, instead of actually locking up,
that's possible with vacuum full.