Re: Maintenance question / DB size anomaly...

Dang it, Tom, don't you ever get tired of being right? I guess I had
been focusing
on the index numbers since they came up first, and its the index
files that are > 10Gb.

Okay, so I did some digging with pg_filedump, and found the following:

.
.
.
.
Block 406 ********************************************************
<Header> -----
Block Offset: 0x0032c000 Offsets: Lower 208 (0x00d0)
Block: Size 8192 Version 2 Upper 332 (0x014c)
LSN: logid 950 recoff 0x9ebcc6e4 Special 8192 (0x2000)
Items: 47 Free Space: 124
Length (including item array): 212

<Data> ------
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)

Item 2 -- Length: 185 Offset: 7532 (0x1d6c) Flags: USED
XMIN: 1489323584 CMIN: 4 XMAX: 0 CMAX|XVAC: 0
Block Id: 406 linp Index: 2 Attributes: 6 Size: 32
infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

Item 3 -- Length: 129 Offset: 7400 (0x1ce8) Flags: USED
XMIN: 1489323590 CMIN: 2 XMAX: 0 CMAX|XVAC: 0
Block Id: 406 linp Index: 3 Attributes: 6 Size: 32
infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

Item 4 -- Length: 77 Offset: 7320 (0x1c98) Flags: USED
XMIN: 1489323592 CMIN: 1 XMAX: 0 CMAX|XVAC: 0
Block Id: 406 linp Index: 4 Attributes: 6 Size: 32
infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

...I then looked in the DB:

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)

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

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

...is this what you were looking for, Tom? The only thing that
stands out to me is
the XMAX_INVALID mask. Thoughts?

Thanks,

/kurt

On Jun 20, 2007, at 11:22 AM, Tom Lane wrote:

> Kurt Overberg <kurt(at)hotdogrecords(dot)com> writes:
>> Okay, so the sl_log_1 TABLE looks okay. Its the indexes that seem to
>> be messed up, specifically sl_log_1_idx1 seems to think that there's
>>>> 300,000 rows in the table its associated with. I just want to fix
>> the index, really.
>
> I'm not sure how you arrive at that conclusion. The VACUUM VERBOSE
> output you provided here:
> http://archives.postgresql.org/pgsql-performance/2007-06/msg00370.php
> shows clearly that there are lots of rows in the table as well as
> the indexes. A REINDEX would certainly cut the size of the indexes
> but it isn't going to do anything about the extraneous rows.
>
> When last heard from, you were working on getting pg_filedump
> output for
> some of the bogus rows --- what was the result?
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings