We ran out of disk space on our main server, and now I've freed up 
space, we cannot start postgres!

Jan 23 12:18:50 canaveral postgres[563]: [2-1] LOG:  checkpoint record 
is at 2/96500B94
Jan 23 12:18:50 canaveral postgres[563]: [3-1] LOG:  redo record is at 
2/964BD23C; undo record is at 0/0; shutdown FALSE
Jan 23 12:18:50 canaveral postgres[563]: [4-1] LOG:  next transaction 
ID: 14216463; next OID: 4732327
Jan 23 12:18:50 canaveral postgres[563]: [5-1] LOG:  database system was 
not properly shut down; automatic recovery in progress
Jan 23 12:18:50 canaveral postgres[563]: [6-1] LOG:  redo starts at 
2/964BD23C
Jan 23 12:18:51 canaveral postgres[563]: [7-1] PANIC:  could not access 
status of transaction 14286850
Jan 23 12:18:51 canaveral postgres[563]: [7-2] DETAIL:  could not read 
from file "/usr/local/pgsql/data/pg_clog/000D" at offset 163840: 
Undefined error: 0
Jan 23 12:18:51 canaveral postgres[567]: [1-1] FATAL:  the database 
system is starting up
Jan 23 12:18:52 canaveral postgres[558]: [1-1] LOG:  startup process 
(PID 563) was terminated by signal 6
Jan 23 12:18:52 canaveral postgres[558]: [2-1] LOG:  aborting startup 
due to startup process failure

What can I do?

Chris

Sat, 24 Jan 2004 04:28:34 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Disaster!
pg_clog information:

# cd pg_clog
# ls -al
total 3602
drwx------  2 pgsql  pgsql     512 Jan 23 03:49 .
drwx------  6 pgsql  pgsql     512 Jan 23 12:30 ..
-rw-------  1 pgsql  pgsql  262144 Jan 18 19:43 0000
-rw-------  1 pgsql  pgsql  262144 Jan 18 19:43 0001
-rw-------  1 pgsql  pgsql  262144 Jan 18 19:43 0002
-rw-------  1 pgsql  pgsql  262144 Jan 18 19:43 0003
-rw-------  1 pgsql  pgsql  262144 Jan 19 08:35 0004
-rw-------  1 pgsql  pgsql  262144 Jan 21 10:35 0005
-rw-------  1 pgsql  pgsql  262144 Jan 20 10:07 0006
-rw-------  1 pgsql  pgsql  262144 Jan 21 19:30 0007
-rw-------  1 pgsql  pgsql  262144 Jan 21 17:24 0008
-rw-------  1 pgsql  pgsql  262144 Jan 22 06:50 0009
-rw-------  1 pgsql  pgsql  262144 Jan 22 13:01 000A
-rw-------  1 pgsql  pgsql  262144 Jan 23 06:45 000B
-rw-------  1 pgsql  pgsql  262144 Jan 23 09:37 000C
-rw-------  1 pgsql  pgsql  163840 Jan 23 12:30 000D

I don't have debug symbols, so backtrace isn't that helpful:

(gdb) bt
#0  0x2840a848 in kill () from /usr/lib/libc.so.4
#1  0x2844ee90 in abort () from /usr/lib/libc.so.4
#2  0x81b33ba in ?? ()
#3  0x8092a0c in ?? ()
#4  0x8092450 in ?? ()
#5  0x808a9d7 in ?? ()
#6  0x808adf4 in ?? ()
#7  0x808ae8c in ?? ()
#8  0x808bffa in ?? ()
#9  0x809082f in ?? ()
#10 0x8095204 in ?? ()
#11 0x8135c6b in ?? ()
#12 0x813309c in ?? ()
#13 0x8108dc3 in ?? ()
#14 0x806d49b in ?? ()

I have a backup of the data dir for future analysis, but this is kind of 
an urgent fix right now!  (Especially since it's 4am my time :( )

Thanks,

Chris

Christopher Kings-Lynne wrote:

> We ran out of disk space on our main server, and now I've freed up 
> space, we cannot start postgres!
> 
> Jan 23 12:18:50 canaveral postgres[563]: [2-1] LOG:  checkpoint record 
> is at 2/96500B94
> Jan 23 12:18:50 canaveral postgres[563]: [3-1] LOG:  redo record is at 
> 2/964BD23C; undo record is at 0/0; shutdown FALSE
> Jan 23 12:18:50 canaveral postgres[563]: [4-1] LOG:  next transaction 
> ID: 14216463; next OID: 4732327
> Jan 23 12:18:50 canaveral postgres[563]: [5-1] LOG:  database system was 
> not properly shut down; automatic recovery in progress
> Jan 23 12:18:50 canaveral postgres[563]: [6-1] LOG:  redo starts at 
> 2/964BD23C
> Jan 23 12:18:51 canaveral postgres[563]: [7-1] PANIC:  could not access 
> status of transaction 14286850
> Jan 23 12:18:51 canaveral postgres[563]: [7-2] DETAIL:  could not read 
> from file "/usr/local/pgsql/data/pg_clog/000D" at offset 163840: 
> Undefined error: 0
> Jan 23 12:18:51 canaveral postgres[567]: [1-1] FATAL:  the database 
> system is starting up
> Jan 23 12:18:52 canaveral postgres[558]: [1-1] LOG:  startup process 
> (PID 563) was terminated by signal 6
> Jan 23 12:18:52 canaveral postgres[558]: [2-1] LOG:  aborting startup 
> due to startup process failure
> 
> What can I do?
> 
> Chris
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match

Sat, 24 Jan 2004 04:34:51 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster!
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> We ran out of disk space on our main server, and now I've freed up 
> space, we cannot start postgres!

> Jan 23 12:18:51 canaveral postgres[563]: [7-1] PANIC:  could not access 
> status of transaction 14286850
> Jan 23 12:18:51 canaveral postgres[563]: [7-2] DETAIL:  could not read 
> from file "/usr/local/pgsql/data/pg_clog/000D" at offset 163840: 
> Undefined error: 0

I'd suggest extending that file with 8K of zeroes (might need more than
that, but probably not).

			regards, tom lane

Fri, 23 Jan 2004 15:42:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
Mensaje citado por Christopher Kings-Lynne <chriskl@familyhealth.com.au>:

> > I'd suggest extending that file with 8K of zeroes (might need more than
> > that, but probably not).
> 
> How do I do that?  Sorry - I'm not sure of the quickest way, and I'm 
> reading man pages as we speak!

# dd if=/dev/zeros of=somefile
# cat file1 somefile >> newfile
# mv newfile file1

file1 is "/usr/local/pgsql/data/pg_clog/000D"

-- 
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Martï¿½n Marquï¿½s          |   Programador, DBA
Centro de Telemï¿½tica	|     Administrador
               Universidad Nacional
                    del Litoral
-------------------------------------------------------

Fri, 23 Jan 2004 17:44:45 -0300
=?iso-8859-1?b?TWFydO1uIA==?= =?iso-8859-1?b?TWFycXXpcw==?=
	<martin@bugs.unl.edu.ar>
Re: Disaster!
> I'd suggest extending that file with 8K of zeroes (might need more than
> that, but probably not).

How do I do that?  Sorry - I'm not sure of the quickest way, and I'm 
reading man pages as we speak!

Thanks Tom,

Chris


Sat, 24 Jan 2004 04:46:08 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster!
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> I'd suggest extending that file with 8K of zeroes (might need more than
>> that, but probably not).

> How do I do that?  Sorry - I'm not sure of the quickest way, and I'm 
> reading man pages as we speak!

Something like "dd if=/dev/zero bs=8k count=1 >>clogfile", but check the
dd man page (and make sure you have a /dev/zero).

			regards, tom lane

Fri, 23 Jan 2004 15:50:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 

> I'd suggest extending that file with 8K of zeroes (might need more than
> that, but probably not).

OK, I've done

dd if=/dev/zero of=zeros count=16

Then cat zero >> 000D

Now I can start it up!  Thanks!

What should I do now?

Chris


Sat, 24 Jan 2004 04:51:11 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster!
Mensaje citado por Tom Lane <tgl@sss.pgh.pa.us>:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > Now I can start it up!  Thanks!
> 
> > What should I do now?
> 
> Go home and get some sleep ;-).  If the WAL replay succeeded, you're up
> and running, nothing else to do.

Tom, could you give a small insight on what occurred here, why those 8k of zeros
fixed it, and what is a "WAL replay"?

I am very curious about it.

-- 
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Martï¿½n Marquï¿½s          |   Programador, DBA
Centro de Telemï¿½tica	|     Administrador
               Universidad Nacional
                    del Litoral
-------------------------------------------------------

Fri, 23 Jan 2004 17:58:33 -0300
=?iso-8859-1?b?TWFydO1uIA==?= =?iso-8859-1?b?TWFycXXpcw==?=
	<martin@bugs.unl.edu.ar>
Re: Disaster! 
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Now I can start it up!  Thanks!

> What should I do now?

Go home and get some sleep ;-).  If the WAL replay succeeded, you're up
and running, nothing else to do.

			regards, tom lane

Fri, 23 Jan 2004 16:00:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
>>What should I do now?
> 
> 
> Go home and get some sleep ;-).  If the WAL replay succeeded, you're up
> and running, nothing else to do.

Cool, thanks heaps Tom.

Are you interested in real backtraces, any of the old data directory, 
etc. to debug the problem?

Obviously it ran out of disk space, but surely postgres should be able 
to start up somehow?

Chris


Sat, 24 Jan 2004 05:06:28 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster!
On Fri, 2004-01-23 at 16:00, Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > Now I can start it up!  Thanks!
> 
> > What should I do now?
> 
> Go home and get some sleep ;-).  If the WAL replay succeeded, you're up
> and running, nothing else to do.

Granted, running out of diskspace is a bad idea, but can (has?)
something be put into place to prevent manual intervention from being
required in restarting the database?


Fri, 23 Jan 2004 16:12:32 -0500
Rod Taylor <pg@rbt.ca>
Re: Disaster!
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Are you interested in real backtraces, any of the old data directory, 
> etc. to debug the problem?

If you could recompile with debug support and get a backtrace from the
panic, it would be helpful.  I suspect what we need to do is make the
clog code more willing to interpret a zero-length read as 8K of zeroes
instead of an error, at least during recovery.  But I kinda thought
there was such an escape hatch already, so I want to see exactly how
it got to the point of the failure.

Also, which PG version are you running exactly?

> Obviously it ran out of disk space, but surely postgres should be able 
> to start up somehow?

See the response I'm about to write to Mart�n.

			regards, tom lane

Fri, 23 Jan 2004 16:13:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
=?iso-8859-1?b?TWFydO1uIA==?= =?iso-8859-1?b?TWFycXXpcw==?= <martin@bugs.unl.edu.ar> writes:
> Tom, could you give a small insight on what occurred here, why those
> 8k of zeros fixed it, and what is a "WAL replay"?

I think what happened is that there was insufficient space to write out
a new page of the clog (transaction commit) file.  This would result in
a database panic, which is fine --- you're not gonna get much done
anyway if you are down to zero free disk space.  However, after Chris
freed up space, the system needed to replay the WAL from the last
checkpoint to ensure consistency.  The WAL entries evidently included
references to transactions whose commit bits were in the unwritten page.
Now there would also be WAL entries recording those commits, so once the
replay was complete everything would be cool.  But the clog access code
evidently got confused by being asked to read a page that didn't exist
in the file.  I'm not sure yet how that sequence of events occurred,
which is why I asked Chris for a stack trace.

Adding a page of zeroes fixed it by eliminating the read error
condition.  It was okay to do so because zeroes is the correct initial
state for a clog page (all transactions in it "still in progress").
After WAL replay, any completed transactions would be updated in the page.

			regards, tom lane

Fri, 23 Jan 2004 16:21:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
On Fri, Jan 23, 2004 at 05:58:33PM -0300, Martï¿½n Marquï¿½s wrote:

> Tom, could you give a small insight on what occurred here, why those 8k of zeros
> fixed it, and what is a "WAL replay"?

If I may ...

- the disk filled up
- Postgres registered something in WAL that required some commit status
  (WAL log space is preallocated on disk, so this didn't fail)
- the clog code tried to store information about the commit bits, but
  noticed that it needed to extend the clog file.
- the extension failed because the disk was full
- the server went down and a WAL replay was in order, but ...
- the WAL replay could not be done because the code tried to read a
  commit status in pg_clog that wasn't there

(some time later)

- Chris emptied up some space and extended the clog
- WAL replay completed, reading an "uncommitted" status from the clog.

Here, "clog" is the "commit log", an area which indicates for each
transaction whether it committed or aborted.  A WAL replay is the
operation of bringing the data files (tables, indexes, etc) up to date
by reading the "Write-ahead log" (WAL).

Tom's answer will be undoubtly better ...

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"ï¿½Que diferencia tiene para los muertos, los huï¿½rfanos, y aquellos que han
perdido su hogar, si la loca destrucciï¿½n ha sido realizada bajo el nombre
del totalitarismo o del santo nombre de la libertad y la democracia?" (Gandhi)

Fri, 23 Jan 2004 18:22:49 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Disaster!
Rod Taylor <pg@rbt.ca> writes:
> Granted, running out of diskspace is a bad idea, but can (has?)
> something be put into place to prevent manual intervention from being
> required in restarting the database?

See subsequent discussion.  I do want to modify the code to avoid this
problem in future, but we do not need to make Chris work on it at 5 AM
his time.  It can wait till tomorrow.

			regards, tom lane

Fri, 23 Jan 2004 16:23:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
On Fri, Jan 23, 2004 at 04:21:04PM -0500, Tom Lane wrote:

> But the clog access code evidently got confused by being asked to read
> a page that didn't exist in the file.  I'm not sure yet how that
> sequence of events occurred, which is why I asked Chris for a stack
> trace.

There was a very similar episode some time ago, and Elein summed it up
nicely in General Bits:

http://www.varlena.com/GeneralBits/45.php

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernï¿½ndez-Novich)

Fri, 23 Jan 2004 18:26:39 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Disaster!
Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > Are you interested in real backtraces, any of the old data directory, 
> > etc. to debug the problem?
> 
> If you could recompile with debug support and get a backtrace from the
> panic, it would be helpful.  I suspect what we need to do is make the
> clog code more willing to interpret a zero-length read as 8K of zeroes
> instead of an error, at least during recovery.  But I kinda thought
> there was such an escape hatch already, so I want to see exactly how
> it got to the point of the failure.
> 
> Also, which PG version are you running exactly?

I asked him, 7.4.1.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 23 Jan 2004 17:44:35 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Disaster!
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Tom's answer will be undoubtly better ...

Nope, I think you got all the relevant points.

The only thing I'd add after having had more time to think about it is
that this seems very much like the problem we noticed recently with
recovery-from-WAL being broken by the new code in bufmgr.c that tries to
validate the header fields of any page it reads in.  We had to add an
escape hatch to disable that check while InRecovery, and I expect what
we will end up with here is a few lines added to slru.c to make it treat
read-past-EOF as a non-error condition when InRecovery.  Now the clog
code has always had all that paranoid error checking, but because it
deals in such tiny volumes of data (only 2 bits per transaction), it's
unlikely to suffer an out-of-disk-space condition.  That's why we hadn't
seen this failure mode before.

			regards, tom lane

Fri, 23 Jan 2004 20:21:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
On Fri, 23 Jan 2004, Tom Lane wrote:

> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > Tom's answer will be undoubtly better ...
>
> Nope, I think you got all the relevant points.
>
> The only thing I'd add after having had more time to think about it is
> that this seems very much like the problem we noticed recently with
> recovery-from-WAL being broken by the new code in bufmgr.c that tries to
> validate the header fields of any page it reads in.  We had to add an
> escape hatch to disable that check while InRecovery, and I expect what
> we will end up with here is a few lines added to slru.c to make it treat
> read-past-EOF as a non-error condition when InRecovery.  Now the clog
> code has always had all that paranoid error checking, but because it
> deals in such tiny volumes of data (only 2 bits per transaction), it's
> unlikely to suffer an out-of-disk-space condition.  That's why we hadn't
> seen this failure mode before.

It seems that by adding the following to SlruPhysicalReadPage() we can
recover in a reasonable way here. Instead of:

    if (lseek(fd, (off_t) offset, SEEK_SET) < 0)
    {
        slru_errcause = SLRU_SEEK_FAILED;
        slru_errno = errno;
        return false;
    }

We have:


    if (lseek(fd, (off_t) offset, SEEK_SET) < 0)
    {
	if(!InRecovery)
	{
	        slru_errcause = SLRU_SEEK_FAILED;
        	slru_errno = errno;
	        return false;
	}
        ereport(LOG,
                (errmsg("Short read from file \"%s\", reading as zeroes",
                        path)));
        MemSet(shared->page_buffer[slotno], 0, BLCKSZ);
        return true;
    }

Which is exactly how we recover from a missing pg_clog file.

>
> 			regards, tom lane

Gavin

Sat, 24 Jan 2004 17:31:36 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Disaster! 
Gavin Sherry <swm@linuxworld.com.au> writes:
> It seems that by adding the following to SlruPhysicalReadPage() we can
> recover in a reasonable way here. Instead of:
> [ add non-error check to lseek() ]

But it's not the lseek() that's gonna fail.  What we'll actually see,
and did see in Chris' report, is a read() that returns zero bytes, or
possibly an incomplete page.  So actually this change is needed in the
next step, not the lseek.

BUT: after looking at the code more, I'm confused again about exactly
how Chris' failure happened.  The backtrace he sent this morning shows
that the panic occurs while replaying a transaction-commit WAL entry ---
it's trying to set the commit status bit for that transaction number,
and finding that the clog page containing that bit doesn't exist.  But
there should have been a previous WAL entry recording the ZeroCLOGPage()
action for that clog page.  The only way that wouldn't have got replayed
too is if there was a checkpoint in between ... but a checkpoint should
not have been able to complete without flushing the clog buffer to disk.
If there wasn't disk space enough to hold the clog page, the checkpoint
attempt should have failed.  So it may be that allowing a short read in
slru.c would be patching the symptom of a bug that is really elsewhere.
We need to understand the sequence of events in more detail.

Chris, you said you'd saved a copy of the data directory at the time of
the failure, right?  Could you send me the pg_control file and the
active segments of pg_xlog?  (It should be sufficient to send the ones
with file mod times within five minutes of the crash.)

			regards, tom lane

Sat, 24 Jan 2004 12:40:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
I said:
> If there wasn't disk space enough to hold the clog page, the checkpoint
> attempt should have failed.  So it may be that allowing a short read in
> slru.c would be patching the symptom of a bug that is really elsewhere.

After more staring at the code, I have a theory.  SlruPhysicalWritePage
and SlruPhysicalReadPage are coded on the assumption that close() can
never return any interesting failure.  However, it now occurs to me that
there are some filesystem implementations wherein ENOSPC could be
returned at close() rather than the preceding write().  (For instance,
the HPUX man page for close() states that this never happens on local
filesystems but can happen on NFS.)  So it'd be possible for
SlruPhysicalWritePage to think it had successfully written a page when
it hadn't.  This would allow a checkpoint to complete :-(

Chris, what's your platform exactly, and what kind of filesystem are
you storing pg_clog on?

			regards, tom lane

Sat, 24 Jan 2004 12:52:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
> After more staring at the code, I have a theory.  SlruPhysicalWritePage
> and SlruPhysicalReadPage are coded on the assumption that close() can
> never return any interesting failure.  However, it now occurs to me that
> there are some filesystem implementations wherein ENOSPC could be
> returned at close() rather than the preceding write().  (For instance,
> the HPUX man page for close() states that this never happens on local
> filesystems but can happen on NFS.)  So it'd be possible for
> SlruPhysicalWritePage to think it had successfully written a page when
> it hadn't.  This would allow a checkpoint to complete :-(

FreeBSD 4.7/4.9 and the UFS filesystem

RETURN VALUES
      The close() function returns the value 0 if successful; otherwise the
      value -1 is returned and the global variable errno is set to 
indicate the
      error.

ERRORS
      Close() will fail if:

      [EBADF]            D is not an active descriptor.

      [EINTR]            An interrupt was received.


Chris

Sun, 25 Jan 2004 08:21:45 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster!
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> FreeBSD 4.7/4.9 and the UFS filesystem

Hm, okay, I'm pretty sure that that combination wouldn't report ENOSPC
at close().  We need to fix the code to check close's return value,
probably, but it seems we still lack a clear explanation of what
happened to your database.

That request to look at your WAL files is still open ...

			regards, tom lane

Sat, 24 Jan 2004 21:42:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > FreeBSD 4.7/4.9 and the UFS filesystem
> 
> Hm, okay, I'm pretty sure that that combination wouldn't report ENOSPC
> at close().  We need to fix the code to check close's return value,
> probably, but it seems we still lack a clear explanation of what
> happened to your database.

The traditional Unix filesystems certainly don't return errors at close. Even
NFS doesn't traditionally do so. I think NFSv3 can if the server disappears
after the client obtains a lease on a piece of the file, but I'm not sure if
ENOSPC is a possible failure mode.

I do know that AFS returns quota failures on close. This was unusual enough
that when AFS was deployed at school unix tools failed left and right over
precisely this issue. Though it mostly just meant they returned the wrong exit
status.

-- 
greg


25 Jan 2004 00:46:55 -0500
Greg Stark <gsstark@mit.edu>
Re: Disaster!
> That request to look at your WAL files is still open ...

I've sent you it privately - let me know how it goes.

Chris



Sun, 25 Jan 2004 14:47:35 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Disaster! 
"gsstark@mit.edu (Greg Stark)" stated in
comp.databases.postgresql.hackers: 

> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
>> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> > FreeBSD 4.7/4.9 and the UFS filesystem
>> 
>> Hm, okay, I'm pretty sure that that combination wouldn't report ENOSPC
>> at close().  We need to fix the code to check close's return value,
>> probably, but it seems we still lack a clear explanation of what
>> happened to your database.
> 
> The traditional Unix filesystems certainly don't return errors at close.
> Even NFS doesn't traditionally do so. I think NFSv3 can if the server
> disappears after the client obtains a lease on a piece of the file, but
> I'm not sure if ENOSPC is a possible failure mode.
[sNip]

    	Why shouldn't the close() function return an error?  If an invalid 
file handle was passed to it, it most certainly should indicate this since 
it's always possible for a separate thread to close it first (or other 
reasons as well).

-- 
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

"We are anti-spammers.  You will confirm
subscriptions.  Resistance is futile."

Please do not eMail me directly when responding
to my postings in the newsgroups.

Sun, 25 Jan 2004 07:36:59 +0000 (UTC)
Randolf Richardson <rr@8x.ca>
Re: Disaster!
Greg Stark wrote:

>I do know that AFS returns quota failures on close. This was unusual enough
>that when AFS was deployed at school unix tools failed left and right over
>precisely this issue. Though it mostly just meant they returned the wrong exit
>status.
>
That means
    open();
    write();
    sync();

could succeed, but the data is not stored on disk, correct?

--
    Manfred



Sun, 25 Jan 2004 11:30:19 +0100
Manfred Spraul <manfred@colorfullife.com>
Re: Disaster!

Manfred Spraul <manfred@colorfullife.com> writes:

> That means
>     open();
>     write();
>     sync();
> 
> could succeed, but the data is not stored on disk, correct?

That would be true on any filesystem. Unless you throw an fsync() call in.

With sync replaced by fsync then any filesystem ought to guarantee the data
has reached disk by the time fsync returns. I think this is even true of NFS
or AFS, though I wouldn't depend on it for my own data.

-- 
greg


25 Jan 2004 09:14:27 -0500
Greg Stark <gsstark@mit.edu>
Re: Disaster!
Greg Stark wrote:

>Manfred Spraul <manfred@colorfullife.com> writes:
>
>  
>
>>That means
>>    open();
>>    write();
>>    sync();
>>
>>could succeed, but the data is not stored on disk, correct?
>>    
>>
>
>That would be true on any filesystem. Unless you throw an fsync() call in.
>  
>
The checkpoint code uses sync() right now. Actually sync();sleep(2);sync().
Win32 has no sync() call, therefore it will use fsyncs. Perhaps 
platforms with deferred errors on close must use fsync, too. Hopefully 
parallel fsyncs - sequential fsyncs could be slow due to more seeking.

--
    Manfred


Sun, 25 Jan 2004 15:32:33 +0100
Manfred Spraul <manfred@colorfullife.com>
Re: Disaster!

Manfred Spraul <manfred@colorfullife.com> writes:

> The checkpoint code uses sync() right now. Actually sync();sleep(2);sync().
> Win32 has no sync() call, therefore it will use fsyncs. Perhaps platforms with
> deferred errors on close must use fsync, too. Hopefully parallel fsyncs -
> sequential fsyncs could be slow due to more seeking.

That code is known to be totally bogus in theory. However in practice it seems
to be the best of the possible bad choices.

Even on filesystems where errors won't be deferred after the write() the data
is still not guaranteed to be on disk. Even after the sync() call. There's no
guarantee of any particular sleep time being enough.

This was brought up a few months ago. The only safe implementation would be to
fsync every file descriptor that had received writes. The problem is keeping
track of which file descriptors those are. Also people were uncertain whether
a backend opening a file and calling fsync would guarantee that writes written
to the same file by other processes through other file descriptors would be
flushed. I'm fairly convinced they would be on all sane vfs implementations
but others were less convinced.

-- 
greg


25 Jan 2004 18:26:05 -0500
Greg Stark <gsstark@mit.edu>
Re: Disaster!
Tom Lane wrote:
> I said:
> > If there wasn't disk space enough to hold the clog page, the checkpoint
> > attempt should have failed.  So it may be that allowing a short read in
> > slru.c would be patching the symptom of a bug that is really elsewhere.
> 
> After more staring at the code, I have a theory.  SlruPhysicalWritePage
> and SlruPhysicalReadPage are coded on the assumption that close() can
> never return any interesting failure.  However, it now occurs to me that
> there are some filesystem implementations wherein ENOSPC could be
> returned at close() rather than the preceding write().  (For instance,
> the HPUX man page for close() states that this never happens on local
> filesystems but can happen on NFS.)  So it'd be possible for
> SlruPhysicalWritePage to think it had successfully written a page when
> it hadn't.  This would allow a checkpoint to complete :-(
> 
> Chris, what's your platform exactly, and what kind of filesystem are
> you storing pg_clog on?

We already have a TODO on fclose():

	* Add checks for fclose() failure

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 26 Jan 2004 13:04:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Disaster!
> 
> Tom Lane wrote:
> > I said:
> > > If there wasn't disk space enough to hold the clog page, the checkpoint
> > > attempt should have failed.  So it may be that allowing a short read in
> > > slru.c would be patching the symptom of a bug that is really elsewhere.
> > 
> > After more staring at the code, I have a theory.  SlruPhysicalWritePage
> > and SlruPhysicalReadPage are coded on the assumption that close() can
> > never return any interesting failure.  However, it now occurs to me that
> > there are some filesystem implementations wherein ENOSPC could be
> > returned at close() rather than the preceding write().  (For instance,
> > the HPUX man page for close() states that this never happens on local
> > filesystems but can happen on NFS.)  So it'd be possible for
> > SlruPhysicalWritePage to think it had successfully written a page when
> > it hadn't.  This would allow a checkpoint to complete :-(
> > 
> > Chris, what's your platform exactly, and what kind of filesystem are
> > you storing pg_clog on?
> 
> We already have a TODO on fclose():
> 
> 	* Add checks for fclose() failure
> 
Tom was referring to close(), not fclose(). 
I once had an awful time searching for a memory leak caused 
by a typo using close instead of fclose. 
So adding checks for both is probably a good idea. 

Regards, Christoph 


Thu, 29 Jan 2004 16:13:08 MET
Christoph Haller <ch@rodos.fzk.de>
Re: Disaster!
Christoph Haller <ch@rodos.fzk.de> writes:
> Tom was referring to close(), not fclose(). 
> I once had an awful time searching for a memory leak caused 
> by a typo using close instead of fclose. 
> So adding checks for both is probably a good idea. 

Already done.

			regards, tom lane

Thu, 29 Jan 2004 11:19:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 
-On [20040125 03:52], Tom Lane (tgl@sss.pgh.pa.us) wrote:
>Hm, okay, I'm pretty sure that that combination wouldn't report ENOSPC
>at close().

 From Tru64's write(2):

[ENOSPC]
      [XSH4.2]  No free space is left on the file system containing the
      file.
      [Tru64 UNIX]  An attempt was made to write past the "early
      warning" EOT while this indicator was enabled.
      [Tru64 UNIX]  An attempt was made to write at or beyond the end of
      a partition.

 From close(2):

[Tru64 UNIX]   A close() function on an NFS file system waits for all
outstanding I/O to complete. If any operation completes with an error,
the error will be returned by close(). The possible errors depend on the
NFS server implementation, but the most likely errors are:

[snip...]

	[ENOSPC] Attempted to write on a full file system.

>We need to fix the code to check close's return value, probably, but it
>seems we still lack a clear explanation of what happened to your
>database.

You always need to check the return codes of calls like that, what if
you received EBADF or EINTR for whatever reason?

-- 
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / kita no mono
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7  9D88 97E6 839B 2EAC 625B
http://www.tendra.org/   | http://diary.in-nomine.org/
 From the pine tree, learn of the pine tree.  And from the bamboo, of the
bamboo...

Fri, 30 Jan 2004 06:59:32 +0100
Jeroen Ruigrok/asmodai <asmodai@wxs.nl>
Re: Disaster!
Randolf Richardson <rr@8x.ca> writes:
> "gsstark@mit.edu (Greg Stark)" stated in
> comp.databases.postgresql.hackers: 
>> The traditional Unix filesystems certainly don't return errors at close.

>     	Why shouldn't the close() function return an error?  If an invalid 
> file handle was passed to it, it most certainly should indicate this 

Of course.  We're discussing the situation where no errors were reported
in prior syscalls --- in particular, open() succeeded.

			regards, tom lane

Fri, 30 Jan 2004 23:54:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disaster! 