Losing records when server hang

[lec]

Hi,

I'm observing the following:
If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physically writes the records?

Thanks,
thomas.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Scott Marlowe]

On Sun, 2004-08-08 at 19:43, lec wrote:

Hi,

I'm observing the following:
If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physically writes the records?

Assuming a properly function storage subsystem and a kernel that does
not lie about fsync, this is not possible.

Are you running on top of IDE drives with the write cache enabled?
Most, if not all, IDE drives lie about FSYNC and can lose data if power
is disconnected abrubtly.

Or if you are running with fsync=false in postgresql.conf the same thing
can happen. Otherwise, not, it should not happen. More info perhaps?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera Munoz]

On Sun, Aug 08, 2004 at 08:36:36PM -0600, Scott Marlowe wrote:

On Sun, 2004-08-08 at 19:43, lec wrote:

If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physically writes the records?

Assuming a properly function storage subsystem and a kernel that does
not lie about fsync, this is not possible.

It is actually possible if he uses several backends to do the job, and
transaction inserting record 10 commits before the hang, and 5,6,7,8,9
don't.

If this is only one backend, then I'd love to see how did he do that.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"The ability to monopolize a planet is insignificant
next to the power of the source"

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Scott Marlowe]

On Sun, 2004-08-08 at 21:26, Alvaro Herrera Munoz wrote:

On Sun, Aug 08, 2004 at 08:36:36PM -0600, Scott Marlowe wrote:

On Sun, 2004-08-08 at 19:43, lec wrote:

If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physically writes the records?

Assuming a properly function storage subsystem and a kernel that does
not lie about fsync, this is not possible.

It is actually possible if he uses several backends to do the job, and
transaction inserting record 10 commits before the hang, and 5,6,7,8,9
don't.

Yeah, but he explicitly said he'd committed 1 through 10. Unless he
didn't understand what is meant by commit. I.e. committing AND
receiving the ack for that commit. Until the database says it
committed, nothing's been committed, so he might have thought just
firing the insert query was committing. I hadn't really thought of that
angle.

Is that the case, lec?
If this is only one backend, then I'd love to see how did he do that.

Me too :-)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

"Scott Marlowe" <sm******@qwest .net> writes:

On Sun, 2004-08-08 at 21:26, Alvaro Herrera Munoz wrote:

If this is only one backend, then I'd love to see how did he do that.

Me too :-)

Given a disk drive that lies about write complete (which is to to say,
most consumer-grade IDE gear), it's at least theoretically possible.
But it doesn't sound real likely, especially if the machine didn't
actually lose power.

I was wondering what he meant by "hang", as well as what he meant by
"commit". The overall lack of detail in the report is notable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[lec]

Scott Marlowe wrote:

On Sun, 2004-08-08 at 21:26, Alvaro Herrera Munoz wrote:

On Sun, Aug 08, 2004 at 08:36:36PM -0600, Scott Marlowe wrote:

On Sun, 2004-08-08 at 19:43, lec wrote:
If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physicall y writes the records?
Assuming a properly function storage subsystem and a kernel that does
not lie about fsync, this is not possible.

I'm using Redhat 7.3, kernel 2.4.18
It is actually possible if he uses several backends to do the job, and
transaction inserting record 10 commits before the hang, and 5,6,7,8,9
don't.

Just 1 backend.

Yeah, but he explicitly said he'd committed 1 through 10. Unless he
didn't understand what is meant by commit. I.e. committing AND
receiving the ack for that commit. Until the database says it
committed, nothing's been committed, so he might have thought just
firing the insert query was committing. I hadn't really thought of that
angle.

Is that the case, lec?

I explicitly 'COMMIT'

If this is only one backend, then I'd love to see how did he do that.

Me too :-)

That's exactly leaving me puzzled. I don't know if it has anything to
do with the SCSI controller or hardware related stuff. The controller
is a RAID, configured are RAID-5.

--lec

[lec]

Tom Lane wrote:

"Scott Marlowe" <sm******@qwest .net> writes:

On Sun, 2004-08-08 at 21:26, Alvaro Herrera Munoz wrote:

If this is only one backend, then I'd love to see how did he do that.

Me too :-)

Given a disk drive that lies about write complete (which is to to say,
most consumer-grade IDE gear), it's at least theoretically possible.
But it doesn't sound real likely, especially if the machine didn't
actually lose power.

It's a SCSI, RAID-5 on a Dell server.
I was wondering what he meant by "hang", as well as what he meant by
"commit". The overall lack of detail in the report is notable.

The hardware actually "hang". The Dell engineers came and replaced the
motherboard but couldn't tell what the actual fault was.

Commit as in 'COMMIT'. 'Records' 1,2,3,4,5,6,7,8 ,9,10 are actually
transactions. I'm as puzzled as to why I lost the transactions in the
middle but got the last transaction.
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[lec]

Scott Marlowe wrote:

On Sun, 2004-08-08 at 19:43, lec wrote:

Hi,

I'm observing the following:
If I commit the following records 1,2,3,4,5,6,7,8 ,9,10 to the database
and the server hangs, I could lose records 5,6,7,8,9 but record 10 is
there. How is this possible and do anyone know how Postgresql
physically writes the records?
Assuming a properly function storage subsystem and a kernel that does
not lie about fsync, this is not possible.

Are you running on top of IDE drives with the write cache enabled?
Most, if not all, IDE drives lie about FSYNC and can lose data if power
is disconnected abrubtly.

SCSI
Or if you are running with fsync=false in postgresql.conf the same thing
can happen. Otherwise, not, it should not happen. More info perhaps?

I didn't change the default fsync, which I believe is fsync=true.

[Tom Lane]

lec <li***@streamyx .com> writes:

It's a SCSI, RAID-5 on a Dell server. The hardware actually "hang". The Dell engineers came and replaced the
motherboard but couldn't tell what the actual fault was. Commit as in 'COMMIT'. 'Records' 1,2,3,4,5,6,7,8 ,9,10 are actually
transactions. I'm as puzzled as to why I lost the transactions in the
middle but got the last transaction.

I'm puzzled too. I don't suppose you have the postmaster log from when
it was trying to recover from the crash? Or even better, copies of the
WAL files?

A possible theory has to do with corruption of the WAL log. For
instance, transactions 1-10 are all down to disk in WAL (or at least the
kernel told postgres the writes were done) and for one reason or another
the buffer manager chances to flush the page containing record 10 out
to its data file before the other records' pages. Now the system hangs.
After reboot, if the WAL log is unreadable beyond transaction 1 then the
database would come up with transaction 1 replayed, 2-10 not replayed,
but 10's data is out there anyway.

However this would seem to imply disk drive misfeasance above and beyond
your motherboard problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[lec]

Tom Lane wrote:

I'm puzzled too. I don't suppose you have the postmaster log from when
it was trying to recover from the crash? Or even better, copies of the
WAL files?

I never knew where the log files are :(

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly