Hi all,
We are using PostgreSQL in a mission-critical
application. For the most part it works really well.
However, we are repeatedly running into one problem:
sometimes our client application hangs while in
transaction and that locks up the entire database, so
that nothing else can access it. It
Hi all,
I have been quite satisfied with the level of support
from the PostgreSQL community, but this time I'm
getting nothing. So, is transaction timeout option
planned at all? What's the alternative solution to a
client that's hung in transaction?
thanks,
Eugene
_
uation?
thanks,
Eugene
--- Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 26, 2005 at 07:40:30AM -0700, Dr NoName
> wrote:
>
> > So, is transaction timeout option planned at all?
>
> > What's the alternative solution to a client that
> What's the client doing that takes locks strong
> enough to "lock up
> the entire database"? Why does the client hang?
yeah, good question. I thought postgres uses
better-than-row-level locking? Could the total
deadlock be caused by a combination of an open
transaction and VACUUM FULL that run
> OK, for the third or fourth time, what kind of locks
> is your application
> taking out that can lock the whole database?
I'd like to know that myself. How can a
select/inser/update lock an entire table or even
multiple tables?
> How, exactly, can PostgreSQL (or any other database)
> recognize
> On Tue, Jul 26, 2005 at 08:33:19AM -0700, Dr NoName
> wrote:
>
> > A single client should not be able to bring the
> entire
> > database down. The DB should recognize that the
> client
> > went down and roll back the transaction. That
> would be
> > t
> The common view on this kind of thing is that if
> your client is broken,
> you need to fix it.
The problem is, we can't fix the users, nor can we fix
other software that our client has to interact with.
There will always be occasional situations when a
client gets stuck.
> That said, I have se
> 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.
> Also it seems that some diagnostics are needed in
> the client app to log
> 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
So can anyone suggest a solution that does not involve
killing the client when it hangs?
thanks,
Eugene
--- Dr NoName <[EMAIL PROTECTED]> wrote:
> > You misunderstood his point. In PostgreSQL
> > parlance, a "cluster" is a
> > single postmaster running on
> Sure. Like this:
>
> Client A accesses table T, and "hangs."
> Client B attempts to get an ACCESS EXCLUSIVE lock on
> table T in
> preparation for VACUUM FULL.
> Client C connects to the database and waits for
> client B to get and
> release his lock on table T.
> Client D connects to the datab
Thanks a lot, everyone! That solved my problem. But I
still want to be able to set transaction timeout. Any
chance of that in the next release?
Eugene
--- Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Wed, 2005-07-27 at 10:31, Dr NoName wrote:
> > > Sure. Like this:
>
Hi all,
I got another problem with postgres. This time it
refuses to use the indexes. Check this out:
siam_production=> \d render
Table
"public.render"
Column|Type |
Modifiers
--
> What is the output of these:
>
> set enable_seqscan = off;
> explain SELECT render.* FROM render WHERE person_id
> = 432;
QUERY PLAN
--
ows=27833
width=1493) (actual time=7.11..743.55 rows=5261
loops=1)
Filter: (person_id = 432)
Total runtime: 747.42 msec
(3 rows)
thanks,
Eugene
--- Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Tue, 2005-08-02 at 12:04, Dr NoName wrote:
> > Hi all,
> >
> > I got
> When's the last time you analyzed this table? And
a few hours before I posted this. vacuumdb --analyze
also runs every night.
> have you considered
> running the pg_autovacuum daemon, which will vacuum
> and analyze for you
> in the back ground?
We are using postgresql 7.3.2 which doesn't hav
> the seqscan is cheaper when a large enough
> proportion
> (maybe 5%) of rows are retrieved, and indeed the
> cost
> is estimated at 39014
very good explanation. thank you.
> try to increase statistics for this column:
>
> ALTER TABLE render ALTER COLUMN person_id
> SET STATISTICS 1000;
> AN
Thanks Ragnar. That solved the problem. I still would
like some explanation about this voodoo. Most
importantly, how can I estimage a "good" statistics
number?
thanks,
Eugene
--- Dr NoName <[EMAIL PROTECTED]> wrote:
> > the seqscan is cheaper when a large enough
>
The solution to my problem was to increase statistics
value and do another analyze. You can also change
default_statistics_target parameter in
postgresql.conf. Don't know if that's related to the
problem you're seeing, but it's worth a try.
Eugene
--- Edmund Dengler <[EMAIL PROTECTED]> wrote:
>
Ed
>
>
> On Tue, 2 Aug 2005, Scott Marlowe wrote:
>
> > On Tue, 2005-08-02 at 16:06, Dr NoName wrote:
> > > The solution to my problem was to increase
> statistics
> > > value and do another analyze. You can also
> change
> > > default_statistics_
Hi all,
While investigating the causes of the deadlock I
described previously, we noticed that ps output would
often show some postgresql processes doing COMMIT. The
developer in charge of the application(*) assures me
that they are not using transactions (or at least not
in any of the code he che
yep. looks like it was the library.
thanks,
Eugene
--- Bruce Momjian wrote:
> Tom Lane wrote:
> > Dr NoName <[EMAIL PROTECTED]> writes:
> > > ... My question is, would
> > > postgresql ps string show COMMIT at the end of
> > > INSERT/UPDATE even if
wtf? my messages are not getting posted
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 9: In versions below 8.0, t
> If you cannot make your transactons shorter (and
> please don't tell me
> that you have user interaction going on while
> holding any open
> transactions), then you might be able to increase
> your concurrency by
> deferring the foreign key check until commit.
oh! my! gawd!!!
THANK YOU!
> If you cannot make your transactons shorter (and
> please don't tell me
> that you have user interaction going on while
> holding any open
> transactions), then you might be able to increase
> your concurrency by
> deferring the foreign key check until commit.
oh! my! gawd!!!
THANK YOU! The d
Help!
I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manua
26 matches
Mail list logo
