Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction problem?

Transaction problem?

From
Kurt Roeckx
Date:
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

Re: Transaction problem?

From
Tom Lane
Date:
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

Re: Transaction problem?

From
Kurt Roeckx
Date:
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

Re: Transaction problem?

From
Doug McNaught
Date:
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

Re: Transaction problem?

From
Hannu Krosing
Date:
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

Re: Transaction problem?

From
Kurt Roeckx
Date:
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

Re: Transaction problem?

From
Tom Lane
Date:
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