Re: transaction timeout

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 <smarlowe(at)g2switchworks(dot)com> wrote:

> On Wed, 2005-07-27 at 10:31, Dr NoName wrote:
> > > 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 database and waits for
> > > client B to get and
> > > release his lock on table T.
> > > Client E connects to the database and waits for
> > > client B to get and
> > > release his lock on table T.
> > > etc...
> >
> > oh! my! gawd!
> > Finally a clear explanation that makes perfect
> sense.
> > Now why did it take so long?
>
> Because your initial definition of the problem kinda
> led us all in the
> wrong direction for 24 hours? :) Remember, it took
> like three times of
> folks asking "what's happening that locks your
> database" before the
> vacuum full issue came up. From there, 24 more
> hours. Actually not
> bad.
>
> And don't forget, the docs on vacuum pretty clearly
> state:
>
> "The second form is the VACUUM FULL command. This
> uses a more aggressive
> algorithm for reclaiming the space consumed by
> expired row versions. Any
> space that is freed by VACUUM FULL is immediately
> returned to the
> operating system. Unfortunately, this variant of the
> VACUUM command
> acquires an exclusive lock on each table while
> VACUUM FULL is processing
> it. Therefore, frequently using VACUUM FULL can have
> an extremely
> negative effect on the performance of concurrent
> database queries."
>
> And then later on:
>
> "VACUUM FULL is recommended for cases where you know
> you have deleted
> the majority of rows in a table, so that the
> steady-state size of the
> table can be shrunk substantially with VACUUM FULL's
> more aggressive
> approach. Use plain VACUUM, not VACUUM FULL, for
> routine vacuuming for
> space recovery."
>
> So, daily vacuum fulls are not recommended.
>
> > So all I need to do is take out the FULL? Is
> regular
> > VACUUM sufficient? How often do we need FULL? (I
> know
> > it's a stupid question without providing some more
> > context, but how can I estimate it?)
>
> Please read up on vacuuming in the docs, at:
>
>
http://www.postgresql.org/docs/8.0/static/maintenance.html#ROUTINE-VACUUMING
>
> It's quite enlightening about this. Basically,
> assuming your fsm
> settings are high enough for your update/delete
> load, yes, plain vacuums
> should be enough.
>
> >
> > I suppose the ultimate solution would be a wrapper
> > script that works as follows:
> >
> > check if there are any waiting/idle in transaction
> > processes
> > if such processes exist, do a regular VACUUM and
> send
> > out a warning email
> > otherwise, do VACUUM FULL.
>
> Nah, that's probably overkill. I'd rather just run
> plain vacuum
> verboses and check them by hand once a week or so to
> make sure I'm
> reclaiming all the space.
>
> > I like this solution a lot more than getting
> support
> > calls on weekends.
>
> Amen brother, amen...
>
> > Out of curiousity, how is lock acquisition
> implemented
> > in postgresql? All the processes have to go
> through
> > some sort of queue, so that locks are granted in
> FIFO
> > order, as you described. Just trying to understand
> it
> > better.
>
> See here:
>
> http://www.postgresql.org/docs/8.0/static/mvcc.html
>
> PostgreSQL's locking system is quite impression. I
> kinda giggle when
> someone says "Well, not MySQL has feature Y, so why
> bother with
> PostgreSQL?" It's pretty obvious they haven't really
> read up on pgsql
> when they say things like that.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
> choose an index scan if your joining column's
> datatypes do not
> match
>

__________________________________
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail