Re: When adding millions of rows at once, getting out of disk space errors

Mike Christensen <imaudi@xxxxxxxxxxx> · Thu, 19 Feb 2009 02:58:02 -0800

I would assume if the account the service is running under has limited
disk space, it won't really matter what OS you're running under. 
Postgres will throw an "out of disk space" error.

The problem for me is I was in the middle of a transaction which
inserted about 50,000 rows into a table, and that transaction was
pending in the transaction log.  For some reason, the service didn't
quit property and when it started back up, it began a repair process. 
When it got to the pending transactions (I think about 16 megs worth of
data) it just hung with no useful errors or anything outputted to the
log or stderr.  I think this needs to be fixed!

First off, when Postgres starts and sees that your database was not
closed properly, it should tell you there's pending transactions and
ask if you want to dump them or try to process them (or maybe save them
for later).  If you process them, there should be clear status and you
should know what's going on.  It's very possible the service would have
/eventually/ started up for me had I waited long enough (I tried 5-6
hrs, with no logs, output, or change in memory consumption; thus I
assumed it was dead)..

Also, if there are errors processing the transaction log, rather than
just error out and exit, it should at least tell you to run
pg_resetxlog.exe.  Another idea is if I just delete everything in the
pg_xlog directory, it should recover and boot up without any errors
rather than complaining about missing checkpoint files.

No big problem, the good news is Postgres never once actually corrupted
my data and I didn't lose a single byte (well, except for the pending
transactions that I didn't care about).  More good news is I learned a
lot and even got familiar with some of the source code and debugging,
and it's made me finally get around to writing an automatic backup
script that runs every midnight.

Mike

Scott Marlowe wrote:

  On Thu, Feb 19, 2009 at 3:11 AM, Mike Christensen <imaudi@xxxxxxxxxxx> wrote:

    Actually I'm writing emails on my Mac <g>

However, the Postgres service is running on my Windows 2003 machine..

The disk space issue turned out to be a disk quota which was easy to solve.
 Unfortunately, the fact it crashed Postgres and with a massive transaction
log left the server in a state where it wouldn't boot anymore.  I was
eventually able to fix it by resetting the transaction log manually.  I'm
hoping future versions of Postgres will handle this scenario a lot better..

They're certainly supposed to.  I've had no such problems running out
of space on linux in the past.  I wonder if it's a windows thing.