Idle in transaction ????

On Wed, Nov 14, 2001 at 03:21:36PM -0000, Steve Brett wrote:

we've just started using zeos postgresql db components in delphi and i've
noticed that vacuum is hanging in postgres.

if i do ps auxf i get the following

postgres 332 0.0 0.5 13832 1344 ? S 14:24 0:00
/usr/local/pgsql/bin/./postmaster -i -o -F -B 1024 -N 128
postgres 722 0.0 1.3 14704 3496 ? S 14:55 0:00 \_ postgres:
postgres ohm 10.139.13.112 idle in transact
postgres 752 0.1 1.8 14580 4740 ? S 15:04 0:01 \_ postgres:
iain ohm 10.139.13.187 idle
postgres 1086 0.5 1.3 14620 3360 ? S 15:23 0:00 \_ postgres:
postgres ohm [local] VACUUM waiting

it's the 'idle in transact' that i'm puzzled about and i'm assuming it's
idel within a transaction block ...

You've got something with an active transaction somewhere and VACUUM is
waiting for it to finish. Find whichever process is guilty and fix it to
commit the transaction.

I think the new vacuum in 7.2 is less picky about locks and will simply skip
tables it can't get a lock on.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

Meant to send this to the list......

---------------------- Forwarded by Wesley Sheldahl/Lex/Lexmark on 11/15/2001
02:37 PM ---------------------------

Wesley Sheldahl
11/15/2001 11:04 AM

To: Martijn van Oosterhout <kleptog%svana.org@interlock.lexmark.com>
cc:

Subject: Re: [GENERAL] Idle in transaction ???? (Document link: Wesley
Sheldahl)

If vacuum in 7.2 skips tables it can't lock, I would hope there would at least
be something logged to that effect so problems like this can be found and
resolved. Otherwise, it seems that a table might go for weeks without being
successfully vacuumed if some process has it perpetually locked.

Wes Sheldahl

Martijn van Oosterhout <kleptog%svana.org@interlock.lexmark.com> on 11/15/2001
09:45:21 AM

Please respond to Martijn van Oosterhout
<kleptog%svana.org@interlock.lexmark.com>

To: Steve Brett <steve.brett%e-mis.com@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Idle in transaction ????

On Wed, Nov 14, 2001 at 03:21:36PM -0000, Steve Brett wrote:
[snip]

it's the 'idle in transact' that i'm puzzled about and i'm assuming it's
idel within a transaction block ...

You've got something with an active transaction somewhere and VACUUM is
waiting for it to finish. Find whichever process is guilty and fix it to
commit the transaction.

I think the new vacuum in 7.2 is less picky about locks and will simply skip
tables it can't get a lock on.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

wsheldah@lexmark.com writes:

If vacuum in 7.2 skips tables it can't lock,

It does not.

The real change in 7.2 is that vacuum requires only an ordinary writer's
lock on the table, not exclusive lock. Since a write lock doesn't
conflict with read or write locks (basically it only conflicts with
schema-changing operations) we expect that vacuum will run concurrently
with most ordinary database operations.

regards, tom lane

That sounds great. Thanks for clearing that up.

Wes Sheldahl

Tom Lane <tgl%sss.pgh.pa.us@interlock.lexmark.com> on 11/15/2001 05:52:00 PM

To: "Wesley_Sheldahl/Lex/Lexmark.LEXMARK"@sweeper.lex.lexmark.com
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Idle in transaction ????

wsheldah@lexmark.com writes:

If vacuum in 7.2 skips tables it can't lock,

It does not.

The real change in 7.2 is that vacuum requires only an ordinary writer's
lock on the table, not exclusive lock. Since a write lock doesn't
conflict with read or write locks (basically it only conflicts with
schema-changing operations) we expect that vacuum will run concurrently
with most ordinary database operations.

regards, tom lane