Re: Maintenance question / DB size anomaly...

Kurt Overberg <kurt@hotdogrecords.com> writes:
> Okay, so I did some digging with pg_filedump, and found the following:

> Block  406 ********************************************************
> Item   1 -- Length:  472  Offset: 7720 (0x1e28)  Flags: USED
>    XMIN: 1489323584  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
>    Block Id: 406  linp Index: 1   Attributes: 6   Size: 32
>    infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

This is pretty much what you'd expect for a never-updated tuple...

> mydb=# select * from _my_cluster.sl_log_1 where ctid = '(406,1)';
> log_origin | log_xid | log_tableid | log_actionseq | log_cmdtype |
> log_cmddata
> ------------+---------+-------------+---------------+-------------
> +-------------
> (0 rows)

so I have to conclude that you've got a wraparound problem.  What is the
current XID counter?  (pg_controldata will give you that, along with a
lot of other junk.)  It might also be interesting to take a look at
"ls -l $PGDATA/pg_clog"; the mod times on the files in there would give
us an idea how fast XIDs are being consumed.

            regards, tom lane