I'm not exactly sure what the problem is, or how it happened.

I have a database where I deleted all records from all tables.  I
run a vacuum full and am left with about 1 GB of data.

What I think is happening is that it somehow thinks that data is
still in use by an other transanction, but that is not the case.
I even restated to make sure.

Anybody got any idea how this could have happened?

I once saw this in the log, and think it might be related:

2003-04-12 21:30:55 LOG:  database system was interrupted at
2003-04-12 21:30:35 CEST
2003-04-12 21:30:55 LOG:  checkpoint record is at 1/1DC89028
2003-04-12 21:30:55 LOG:  redo record is at 1/1C128B54; undo
record is at 0/0; shutdown FALSE
2003-04-12 21:30:55 LOG:  next transaction id: 741; next oid:
9753304
2003-04-12 21:30:55 LOG:  database system was not properly shut
down; automatic
recovery in progress
2003-04-12 21:30:55 LOG:  redo starts at 1/1C128B54
2003-04-12 21:32:23 LOG:  ReadRecord: unexpected pageaddr
1/15AE2000 in log file 1, segment 36, offset 11411456
2003-04-12 21:32:23 LOG:  redo done at 1/24AE0B60

Should I worry about that "unexpected pageaddr"?  It ussually
says "ReadRecord: record with zero length" after an unclean shut
down.


Kurt


Wed, 16 Apr 2003 00:36:20 +0200
Kurt Roeckx <Q@ping.be>
Transaction problem?
Kurt Roeckx <Q@ping.be> writes:
> I have a database where I deleted all records from all tables.  I
> run a vacuum full and am left with about 1 GB of data.

Indexes maybe?  If you don't identify *where* the space is, we can't
say much.

> 2003-04-12 21:30:55 LOG:  redo starts at 1/1C128B54
> 2003-04-12 21:32:23 LOG:  ReadRecord: unexpected pageaddr
> 1/15AE2000 in log file 1, segment 36, offset 11411456
> 2003-04-12 21:32:23 LOG:  redo done at 1/24AE0B60

I believe this is harmless; it's just a possible side-effect from
recycling WAL files.  You'd see it when the old WAL happens to end
exactly on a page boundary.

			regards, tom lane


Tue, 15 Apr 2003 19:23:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction problem? 
On Tue, Apr 15, 2003 at 07:23:32PM -0400, Tom Lane wrote:
> Kurt Roeckx <Q@ping.be> writes:
> > I have a database where I deleted all records from all tables.  I
> > run a vacuum full and am left with about 1 GB of data.
> 
> Indexes maybe?  If you don't identify *where* the space is, we can't
> say much.

I'm exactly sure what's store in that file, or how I have to find
that out.

The biggest file is base/25168/4887238 which is about 750 MB.
I believe that is the biggest table.

How can I find out what is in it?


Kurt


Wed, 16 Apr 2003 01:39:44 +0200
Kurt Roeckx <Q@ping.be>
Re: Transaction problem?
Kurt Roeckx <Q@ping.be> writes:

> The biggest file is base/25168/4887238 which is about 750 MB.
> I believe that is the biggest table.
> 
> How can I find out what is in it?

contrib/oidname

-Doug


15 Apr 2003 21:38:23 -0400
Doug McNaught <doug@mcnaught.org>
Re: Transaction problem?
Kurt Roeckx kirjutas K, 16.04.2003 kell 01:36:
> I'm not exactly sure what the problem is, or how it happened.
> 
> I have a database where I deleted all records from all tables.  I
> run a vacuum full and am left with about 1 GB of data.

Probably indexes.

Delete of all data is slow. If you don't need referetial integrity
maintained then TRUNCATE is much faster. Doing REINDEX after that will
clear out indexes if TRUNCATE does not do it ?

------------------
Hannu


16 Apr 2003 12:13:00 +0300
Hannu Krosing <hannu@tm.ee>
Re: Transaction problem?
On Tue, Apr 15, 2003 at 09:38:23PM -0400, Doug McNaught wrote:
> Kurt Roeckx <Q@ping.be> writes:
> 
> > The biggest file is base/25168/4887238 which is about 750 MB.
> > I believe that is the biggest table.
> > 
> > How can I find out what is in it?
> 
> contrib/oidname

So it seems to be the old indexes.


Kurt


Wed, 16 Apr 2003 20:51:34 +0200
Kurt Roeckx <Q@ping.be>
Re: Transaction problem?
Kurt Roeckx <Q@ping.be> writes:
> So it seems to be the old indexes.

No surprise.  VACUUM will not make them smaller (in 7.3), but REINDEX
will...

			regards, tom lane


Wed, 16 Apr 2003 15:42:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction problem? 