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@xxxxxxxxxxxxxxxxx> 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
