Re: Backends "idle in transaction"

Jeff Boes <jboes(at)nexcerpt(dot)com> writes:
> Could I glean some info from the postmaster log? For example, I think I
> know that
> ReceiveSharedInvalidMessages: cache state reset
> has something to do with this "idle in transaction" state. Could the
> incidence of these be used as a measurement?

Yeah, it could. That particular symptom is relatively harmless though.
The real reason staying "idle in transaction" for long periods is evil
is that it prevents VACUUM from reclaiming deleted tuples (because they
are still live as far as the oldest open transaction is concerned, and
VACUUM has no way to know whether that transaction will choose to look
at any particular table). So what you should really be looking at is
VACUUM VERBOSE output.

Here is a simple example (using CVS tip, but I believe 7.3 and 7.2
act the same):

regression=# create table foo (f1 serial, f2 int);
NOTICE: CREATE TABLE will create implicit sequence 'foo_f1_seq' for SERIAL column 'foo.f1'
CREATE TABLE
regression=# insert into foo default values;
INSERT 154419 1
regression=# insert into foo default values;
INSERT 154420 1
regression=# insert into foo default values;
INSERT 154421 1
regression=# vacuum verbose foo;
INFO: --Relation public.foo--
INFO: Pages 1: Changed 1, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

-- That says we have three tuples. Duh.

regression=# update foo set f2 = 11;
UPDATE 3
regression=# vacuum verbose foo;
INFO: --Relation public.foo--
INFO: Removed 3 tuples in 1 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 1: Changed 1, Empty 0; Tup 3: Vac 3, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

-- That says we have three live tuples left, and we reclaimed (vacuumed)
-- three dead ones, namely the old versions of the updated rows.

-- Now in another psql session I do
-- begin;
-- so that that guy is in "idle in transaction" state.

-- Now repeat the experiment:

regression=# update foo set f2 = 12;
UPDATE 3
regression=# vacuum verbose foo;
INFO: --Relation public.foo--
INFO: Pages 1: Changed 1, Empty 0; Tup 6: Vac 0, Keep 3, UnUsed 0.
Total CPU 0.01s/0.00u sec elapsed 0.00 sec.
VACUUM
regression=#

-- See the "keep 3"? VACUUM knows those tuples are committed dead,
-- but it has to keep them around because my other session can still
-- possibly see them. So we've failed to reclaim any space.

So the short answer is you should see fewer "kept" tuples and more
effective re-use of space in vacuuming, now that you're not holding
transactions open.

regards, tom lane