Checkpoint request failed on version 8.2.1.

"Patrick Earl" <patearl@patearl.net> writes:

2007-01-11 09:56:17 ERROR: could not open relation 1663/16403/16426:
Permission denied
2007-01-11 09:56:17 ERROR: checkpoint request failed
2007-01-11 09:56:17 HINT: Consult recent messages in the server log
for details.
2007-01-11 09:56:17 STATEMENT: CREATE DATABASE "TestDatabase"

Well, like the HINT says, consult recent messages for details. In this
case the reason the checkpoint failed was evidently a file permissions
problem. Is this repeatable? What platform is it on?

regards, tom lane

We're getting the error as part of an automated test suite and it is
seems to occur every time the suite is run. The platform is Win XP 64
bit.

When running the same unit test suite from a remote machine, the error
does not occur. The error also does not occur when manually running
the create database command using pgadmin. We are logging into
PostgreSQL as the same user.

Patrick

"Patrick Earl" <patearl@patearl.net> writes:

We're getting the error as part of an automated test suite and it is
seems to occur every time the suite is run. The platform is Win XP 64
bit.

Hm. We've seen problems of this ilk caused by bogus antivirus software,
but if that were the explanation it's not clear why running the test
locally vs remotely would change anything.

Another theory is that it's got something to do with Windows' problems
with unlinking open files --- we've found that "permission denied" can
be the error code when attempting to open a file that's been unlinked
but isn't yet gone because someone is holding it open. Can you
determine whether the complained-of relation is one that's recently been
deleted?

regards, tom lane

There is no antivirus software running on the machine.

I'm not entirely sure how to determine which relation it is
complaining about. I see a folder that corresponds to the middle
number in the log, and I see numbers in the same range as the right
number from the log.

In any case, the unit tests remove all contents and schema within the
database before starting, and they remove the tables they create as
they proceed. Certainly there are many things have been recently
deleted.

Patrick

"Patrick Earl" <patearl@patearl.net> writes:

In any case, the unit tests remove all contents and schema within the
database before starting, and they remove the tables they create as
they proceed. Certainly there are many things have been recently
deleted.

Yeah, I think then there's no question that the bgwriter is trying to
fsync something that's been deleted but isn't yet closed by every
process. We have things set up so that that's not a really serious
problem anymore --- eventually it will be closed and then the next
checkpoint will succeed. But CREATE DATABASE insists on checkpointing
and so it's vulnerable to even a transient failure.

I've been resisting changing the checkpoint code to treat EACCES as a
non-error situation on Windows, but maybe we have no choice. How do
people feel about this idea: #ifdef WIN32 and the open or fsync fails
with EACCES, then

1. Emit a LOG (or maybe DEBUG) message noting the problem.
2. Leave the fsync request entry in the hashtable for next time.
3. Allow the current checkpoint to complete normally anyway.

If the file has actually been deleted, then eventually it will be closed
and the next checkpoint will be able to remove the hash entry. If
there's something else wrong, we'll keep bleating and maybe the DBA will
notice eventually.

The downside of this is that a real EACCES problem wouldn't get noted at
any level higher than LOG, and so you could theoretically lose data
without much warning. But I'm not seeing anything else we could do
about it --- AFAIK we have not heard of a way we can distinguish this
case from a real permissions problem. And anyway there should never
*be* a real permissions problem; if there is then the user's been poking
under the hood sufficient to void the warranty anyway ;-)

Comments?

regards, tom lane

Tom Lane wrote:

"Patrick Earl" <patearl@patearl.net> writes:

In any case, the unit tests remove all contents and schema within the
database before starting, and they remove the tables they create as
they proceed. Certainly there are many things have been recently
deleted.

Yeah, I think then there's no question that the bgwriter is trying to
fsync something that's been deleted but isn't yet closed by every
process. We have things set up so that that's not a really serious
problem anymore --- eventually it will be closed and then the next
checkpoint will succeed. But CREATE DATABASE insists on checkpointing
and so it's vulnerable to even a transient failure.

I've been resisting changing the checkpoint code to treat EACCES as a
non-error situation on Windows, but maybe we have no choice. How do
people feel about this idea: #ifdef WIN32 and the open or fsync fails
with EACCES, then

1. Emit a LOG (or maybe DEBUG) message noting the problem.
2. Leave the fsync request entry in the hashtable for next time.
3. Allow the current checkpoint to complete normally anyway.

If the file has actually been deleted, then eventually it will be closed
and the next checkpoint will be able to remove the hash entry. If
there's something else wrong, we'll keep bleating and maybe the DBA will
notice eventually.

The downside of this is that a real EACCES problem wouldn't get noted at
any level higher than LOG, and so you could theoretically lose data
without much warning. But I'm not seeing anything else we could do
about it --- AFAIK we have not heard of a way we can distinguish this
case from a real permissions problem. And anyway there should never
*be* a real permissions problem; if there is then the user's been poking
under the hood sufficient to void the warranty anyway ;-)

Comments?

I find it very unlikely that you would "during normal operations" end up
in a situation where you would first have permissions to create files in
a directory, and then lose them.
What could be is that you have a directory where you never had
permissions to create the file in the first place.

Any chance to differentiate between these? In the first case, someone
did something to change the permissions, and can be expected to actually
check that things continued to work after that. In the second case, it
would be nice if it was possible to catch it faster.

//Magnus

Magnus Hagander <magnus@hagander.net> writes:

I find it very unlikely that you would "during normal operations" end up
in a situation where you would first have permissions to create files in
a directory, and then lose them.
What could be is that you have a directory where you never had
permissions to create the file in the first place.

Any chance to differentiate between these?

The cases we're concerned about involve access to an existing file, not
attempts to create a new one, so I'm not clear what your point is.

I would certainly *love* to differentiate between these failures and
ordinary permissions failures, but so far as I've heard we can't.

regards, tom lane

On Thu, Jan 11, 2007 at 03:14:37PM -0500, Tom Lane wrote:

The downside of this is that a real EACCES problem wouldn't get noted at
any level higher than LOG, and so you could theoretically lose data
without much warning. But I'm not seeing anything else we could do
about it --- AFAIK we have not heard of a way we can distinguish this
case from a real permissions problem. And anyway there should never
*be* a real permissions problem; if there is then the user's been poking
under the hood sufficient to void the warranty anyway ;-)

Or some other "helpful" process such as a virus scanner has been poking
under the hood for you... :(

Given that this could result in data loss, if this was to be done I'd
very much want to see a way to disable it in a production environment.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

"Jim C. Nasby" <jim@nasby.net> writes:

Given that this could result in data loss, if this was to be done I'd
very much want to see a way to disable it in a production environment.

Production environments are the same ones that won't be happy with
random checkpoint failures, either.

If we can't find a way to positively identify the deleted-file failures
then I think we've got to do something like this.

(You know, of course, that my opinion is that no sane person would run a
production database on Windows in the first place. So the data-loss
risk to me seems less of a problem than the unexpected-failures problem.
It's not like there aren't a ton of other data-loss scenarios in that OS
that we can't do anything about...)

regards, tom lane

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

I find it very unlikely that you would "during normal operations" end up
in a situation where you would first have permissions to create files in
a directory, and then lose them.
What could be is that you have a directory where you never had
permissions to create the file in the first place.

Any chance to differentiate between these?

The cases we're concerned about involve access to an existing file, not
attempts to create a new one, so I'm not clear what your point is.

Well, then I don't see it as being a big problem, which was the
question, I think. If pgsql had permissions to create the file, it would
never lose it unless the dba changed something - and if the dba changed
something, then he should check his logs afterwards to make sure he
didn't break anything.

My point is that if we know that *we* could create the file, than the
probability of it being an *actual* permissions problem is very low
during normal operations. So it's most likely "the delete issue", and
thus doing what you propose does seem like a fairly safe bet.

I would certainly *love* to differentiate between these failures and
ordinary permissions failures, but so far as I've heard we can't.

Right, that's the base problem.

//Magnus

On Thu, Jan 11, 2007 at 04:32:42PM -0500, Tom Lane wrote:

"Jim C. Nasby" <jim@nasby.net> writes:

Given that this could result in data loss, if this was to be done I'd
very much want to see a way to disable it in a production environment.

Production environments are the same ones that won't be happy with
random checkpoint failures, either.

Maybe I'm not understanding what happens in a checkpoint failure, but
I'd rather have my pg_xlog fill up (hopefully with a lot af WARNINGS
thrown before-hand) and face a long recovery than lose data...

If we can't find a way to positively identify the deleted-file failures
then I think we've got to do something like this.

(You know, of course, that my opinion is that no sane person would run a
production database on Windows in the first place. So the data-loss
risk to me seems less of a problem than the unexpected-failures problem.
It's not like there aren't a ton of other data-loss scenarios in that OS
that we can't do anything about...)

Yeah, and I share your opinion. Unfortunately, a lot of others do not.
:(

It would be useful if we had a page somewhere that explained in detail
what these data-loss issues were and why they're out of our control. At
least then people would (or could...) understand why production +
Windows == BadIdea.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, 11 Jan 2007, Tom Lane wrote:

...snip...

(You know, of course, that my opinion is that no sane person would run a
production database on Windows in the first place. So the data-loss
risk to me seems less of a problem than the unexpected-failures problem.
It's not like there aren't a ton of other data-loss scenarios in that OS
that we can't do anything about...)

regards, tom lane

PLEASE OH PLEASE document every f-ing one of them! (And I don't mean
document Windows issues as comments in the source code. Best would be in
the official documentation/on a web page.) On occasion, I could *really*
use such a list! (If such already exists, please point me at it!)

Thing is, Tom, not everybody has the same level of information you have on
the subject...

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

Richard Troy wrote:

On Thu, 11 Jan 2007, Tom Lane wrote:

...snip...

(You know, of course, that my opinion is that no sane person would run a
production database on Windows in the first place. So the data-loss
risk to me seems less of a problem than the unexpected-failures problem.
It's not like there aren't a ton of other data-loss scenarios in that OS
that we can't do anything about...)

PLEASE OH PLEASE document every f-ing one of them! (And I don't mean
document Windows issues as comments in the source code. Best would be in
the official documentation/on a web page.) On occasion, I could *really*
use such a list! (If such already exists, please point me at it!)

Thing is, Tom, not everybody has the same level of information you have on
the subject...

Please don't. At least not on the PostgreSQL web site nor in the docs.
And no, I don't run my production servers on Windows either.

For good or ill, we made a decision years ago to do a proper Windows
port. I think that it's actually worked out reasonably well. All
operating systems have warts. Not long ago I tended to advise people not
to run mission critical Postgresql on Linux unless they were *very*
careful, due to the over-commit issue.

In fact, I don't trust any OS. I use dumps and backups and replication
to protect myself from them all.

In the present instance, the data loss risk is largely theoretical, as I
understand it, as we don't expect a genuine EACCESS error.

cheers

andrew

Please don't. At least not on the PostgreSQL web site nor in the docs.
And no, I don't run my production servers on Windows either.

It does seem like it might be a good idea to have FAQs based on each OS,
yes? There are various things that effect each OS differently. The most
obvious to me being shared memory and wal_sync_method.

If could be a good idea to have.

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Thu, Jan 11, 2007 at 03:12:07PM -0800, Joshua D. Drake wrote:

It does seem like it might be a good idea to have FAQs based on each OS,
yes? There are various things that effect each OS differently. The most
obvious to me being shared memory and wal_sync_method.

If could be a good idea to have.

Joshua D. Drake

+1

regards,
J

Joshua D. Drake wrote:

Please don't. At least not on the PostgreSQL web site nor in the docs.
And no, I don't run my production servers on Windows either.

It does seem like it might be a good idea to have FAQs based on each OS,
yes? There are various things that effect each OS differently. The most
obvious to me being shared memory and wal_sync_method.

But we have per-platform FAQs. If there is information missing, the
reason is that nobody has submitted an appropriate patch, nothing more.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Jan 11, 2007 at 09:42:38PM -0300, Alvaro Herrera wrote:

But we have per-platform FAQs. If there is information missing, the
reason is that nobody has submitted an appropriate patch, nothing more.

where are these FAQs, and why were they not easily found when the original
poster sent his email? is there some "SEO" we need to do on the websites to
make things more obvious?

regards,
J

On Thu, 2007-01-11 at 21:42 -0300, Alvaro Herrera wrote:

Joshua D. Drake wrote:

Please don't. At least not on the PostgreSQL web site nor in the docs.
And no, I don't run my production servers on Windows either.

It does seem like it might be a good idea to have FAQs based on each OS,
yes? There are various things that effect each OS differently. The most
obvious to me being shared memory and wal_sync_method.

But we have per-platform FAQs. If there is information missing, the
reason is that nobody has submitted an appropriate patch, nothing more.

Yes you are correct, now that I look. It is not obviously apparent
though and they do appear to be quite out of date.

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

"Jim C. Nasby" <jim@nasby.net> writes:

On Thu, Jan 11, 2007 at 03:14:37PM -0500, Tom Lane wrote:

... And anyway there should never
*be* a real permissions problem; if there is then the user's been poking
under the hood sufficient to void the warranty anyway ;-)

Or some other "helpful" process such as a virus scanner has been poking
under the hood for you... :(

One point worth making is that I'm not really convinced anymore that
we have proof that antivirus code has been creating any such problems.
We have several anecdotal cases where someone reported erratic
"permission denied" problems on Windows, and we suggested getting rid
of any AV code, and it seemed to fix their problem --- but how long did
they test? This problem is inherently very timing-sensitive, and so the
fact that you don't see it for a little while is hardly proof that it's
gone. See the report that started this thread for examples of apparent
correlations that are really quite spurious, like whether the test case
is being driven locally or not. It could easy be that every report
we've heard really traces to the not-yet-deleted-file problem.

So basically what we'd have is that if you manually remove permissions
on a database file or directory you'd be risking data loss; but heck,
if you manually move, rename, delete such a file you're risking
(guaranteeing) data loss. Any sane user is going to figure "keep your
fingers away from the moving parts"; or if he can't figure that out,
he's got no one but himself to blame.

It's not ideal, granted, but we're dealing with a much-less-than-ideal
OS, so we gotta make some compromises.

regards, tom lane