Re: A long-running transaction - Mailing list pgsql-sql

On Fri, Apr 13, 2007 at 07:49:19AM -0400, Andrew Sullivan wrote:
> On Fri, Apr 13, 2007 at 09:02:58AM +0800, John Summerfield wrote:

> > 1. For the first day or so, my observation was that the disk was not
> > particularly busy.
> 
> That's completely consistent with the theory I have.  As the number
> of dead tuples goes up, your disk activity will slowly get worse.

This simple demonstration occurred to me on the subway on the way
here, to show that the dead tuples really will mount.

testing=# \d testtab                        Table "public.testtab"Column |  Type   |                      Modifiers
                 
 
--------+---------+------------------------------------------------------id     | integer | not null default
nextval('testtab_id_seq'::regclass)col1  | text    | col2   | text    | 
 
Indexes:   "testtab_pkey" PRIMARY KEY, btree (id)

testing=# SELECT * from testtab;id | col1 | col2 
----+------+------ 1 | one  | 
(1 row)

Now, we check how many dead tuples we have:

testing=# VACUUM VERBOSE testtab ;
INFO:  vacuuming "public.testtab"
INFO:  index "testtab_pkey" now contains 1 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "testtab": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 3 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_90325"
INFO:  index "pg_toast_90325_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_90325": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Notice the lines:

INFO:  "testtab": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.


So, let's do an update

BEGIN
testing=# UPDATE testtab set col2='1';
UPDATE 1
testing=# commit;
COMMIT

This time, when we run vacuum, we get this (snipped out for brevity):

INFO:  "testtab": found 1 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.

So, several updates in one transaction:

testing=# begin;
BEGIN
testing=# UPDATE testtab set col2='2';
UPDATE 1
testing=# UPDATE testtab set col2='3';
UPDATE 1
testing=# UPDATE testtab set col2='4';
UPDATE 1
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# commit;
COMMIT

This time, VACUUM VERBOSE tells us that all of those were dead:

INFO:  "testtab": found 4 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.

Ok, what if we just update without actually changing anything?

testing=# begin;
BEGIN
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# UPDATE testtab set col2='5';
UPDATE 1
testing=# commit;
COMMIT

We get the same result:

INFO:  "testtab": found 5 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.

A





> 
> > At present I'm trying to clarify in my mind the nature of the problem.
> > What I'm trying to do seems to me reasonable. I have some data, and I
> > want it all in or none of it, so it fits the idea of a single transaction.
> > 
> > It might be that my demands exceed Postgresql's current capabilities,
> > but by itself it doesn't make what I'm trying to do unreasonable.
> 
> No, it's not unreasonable, but it happens to be a pessimal case under
> Postgres.
> 
> A
> 
> -- 
> Andrew Sullivan  | ajs@crankycanuck.ca
> "The year's penultimate month" is not in truth a good way of saying
> November.
>         --H.W. Fowler
> 
> ---------------------------(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

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."    --Damien Katz