Re: VACUUM produces odd freespace values

Bruce Momjian wrote:
> Can anyone explain why VACUUM after INSERT shows steadily decreasing
> freespace, while DELETE of the same rows does not decrease consistently?
>
> Specifically, after one row is inserted I see:
>
>     SELECT pg_freespace('mvcc_demo');
>      pg_freespace
>     --------------
>      (0,8128)
>     (1 row)
>
> but after inserting two more rows and deleting those two rows, I see:
>
>     SELECT pg_freespace('mvcc_demo');
>      pg_freespace
>     --------------
>      (0,8096)
>     (1 row)
>
> Seems that value should be '(0,8128)'.  Is it the unused line pointers
> that are causing this?
>
> Another odd thing --- if I change the second VACUUM to VACUUM FULL I
> see:
>
>     VACUUM FULL mvcc_demo;
>     VACUUM
>     SELECT pg_freespace('mvcc_demo');
>      pg_freespace
>     --------------
>      (0,0)
>     (1 row)
>
> There is still a row in the table, so why is there no free space
> reported?  I realize after VACUUM FULL that only the last page has
> freespace --- do we assume that will be used as default for the next
> addition and just not bother with the free space map? --- makes sense if
> we do that.  Does this happen because cluster creates a new relfilenode?
>
> I am attaching the init script, the SQL query script, and the results I
> obtained against our CVS HEAD.

Sorry.  Attached is trimmed-down result file that shows just the
problem.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

00-init.sql
------------------
This script is designed to run in a database called test
and requires installation of /contrib/pageinspect and
/contrib/pg_freespacemap.
You are now connected to database "test" as user "postgres".
DROP TABLE IF EXISTS mvcc_demo;
DROP TABLE
CREATE TABLE mvcc_demo (val INTEGER);
CREATE TABLE
DROP VIEW IF EXISTS mvcc_demo_page0;
DROP VIEW
CREATE VIEW mvcc_demo_page0 AS
    SELECT  '(0,' || lp || ')' AS ctid,
            CASE lp_flags
                    WHEN 0 THEN 'Unused'
                    WHEN 1 THEN 'Normal'
                    WHEN 2 THEN 'Redirect to ' || lp_off
                    WHEN 3 THEN 'Dead'
            END,
            t_xmin::text::int8 AS xmin,
            t_xmax::text::int8 AS xmax,
        t_ctid
    FROM heap_page_items(get_raw_page('mvcc_demo', 0))
    ORDER BY lp;
CREATE VIEW

31-vacuum-freesp.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
(0 rows)

INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8128)
(1 row)

INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8064)
(1 row)

DELETE FROM mvcc_demo WHERE val = 3;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

DELETE FROM mvcc_demo WHERE val = 2;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

SELECT * FROM mvcc_demo_page0;
 ctid  |  case  | xmin | xmax | t_ctid
-------+--------+------+------+--------
 (0,1) | Normal | 1339 |    0 | (0,1)
 (0,2) | Unused |      |      |
 (0,3) | Unused |      |      |
(3 rows)

DELETE FROM mvcc_demo WHERE val = 1;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
(0 rows)

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
 pg_relation_size
------------------
                0
(1 row)