BUG #2419: could not reattach to shared memory

I wish I had more to suggest to you. We are working on a few problems
with semaphore on Win2003 SP1, but nothing related to shared memory.

---------------------------------------------------------------------------

Andy Male wrote:

The following bug has been logged online:

Bug reference: 2419
Logged by: Andy Male
Email address: andy@ubic.co.uk
PostgreSQL version: 8.1
Operating system: Windows 2003 Server (standard) SP1
Description: could not reattach to shared memory
Details:

FULL ERROR IN WINDOWS EVENT LOG -

The description for Event ID ( 0 ) in Source ( PostgreSQL ) cannot be found.
The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: FATAL:
could not reattach to shared memory (key=5432001, addr=01960000): Invalid
argument

There is no correspondng error in pg_log.

Once the error happens the database become unreponsive, current connections
stop responding and you cannot make new connections. Stopping and starting
the database removes the error and normal operation can continue.

The error happens intermittently (three or four times a day) and doesn't
seem to have a specific cause. The database is not heavily used and is
processing around 100 transactions per minute.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi,

Thanks for your response. I have enabled additional logging and have
reduced the connections (max_connections) to 50 to reduce the memory
overhead. There should not be more than 8 connections at once and I wanted
to rule out some issue where the client wasn't releasing the connections.

The error has reduced in frequency but still happens. The following log
shows that there was a problem writing to the log file due to permissions
problem. Obviously this is not the case because the log file was accessible
before and after the error occurs since it contains further logging about
the problem.

The subsequent message in the log suggests something is wrong with the
client application (or I may be misinterpreting this message) -

"This application has requested the Runtime to terminate it in an unusual
way. Please contact the application's support team for more information."

Then there are various other server error/warnings and a report of "possibly
corrupted shared memory" which is the original problem reported.

I am not sure what to do, I do not know how to debug the problem and
currently Postgres is un-useable in a production environment. I am
considering recoding the app to use an alternate database, but that is going
to be a fairly lengthy process just to prove the problem is with the
database rather than the client application. I can't actually see how a
client application could or should be able to cause a memory error in the
database. Any help would be most appreciated.

Thanks
Andy

****** LOG FILE SNIP ******

2006-05-07 23:44:19 10.10.12.100(4018)LOG: 00000: statement: EXECUTE
npgsqlportal1 [PREPARE: select * from
fn_Driver_Session_Updated($1::int8,$2::bool)]
2006-05-07 23:44:19 10.10.12.100(4018)LOCATION: exec_execute_message,
postgres.c:1718
2006-05-07 23:44:20 10.10.12.100(4018)PANIC: 42501: could not write to log
file 0, segment 90 at offset 2998272, length 8192: Permission denied
2006-05-07 23:44:20 10.10.12.100(4018)CONTEXT: writing block 75 of relation
1663/20632/100738
SQL statement "update tbl_query_ui_consumer_session_mapping set
ui_consumer_session_data_action_type_id = 3, client_received = false where
session_id = $1 and client_session_id = $2 "
PL/pgSQL function "fn_driver_session_updated" line 58 at SQL
statement
2006-05-07 23:44:20 10.10.12.100(4018)LOCATION: XLogWrite, xlog.c:1474
2006-05-07 23:44:20 10.10.12.100(4018)STATEMENT: select * from
fn_Driver_Session_Updated($1::int8,$2::bool)

This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's support team for more information.

2006-05-07 23:44:21 LOG: 00000: server process (PID 5924) was terminated by
signal 3

2006-05-07 23:44:21 LOCATION: LogChildExit, postmaster.c:2425

2006-05-07 23:44:21 LOG: 00000: terminating any other active server
processes

2006-05-07 23:44:21 LOCATION: HandleChildCrash, postmaster.c:2306

2006-05-07 23:44:21 10.10.10.100(2467)WARNING: 57P02: terminating
connection because of crash of another server process

2006-05-07 23:44:21 10.10.10.100(2467)DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.

2006-05-07 23:44:21 10.10.10.100(2467)HINT: In a moment you should be able
to reconnect to the database and repeat your command.

2006-05-07 23:44:21 10.10.10.100(2467)LOCATION: quickdie, postgres.c:2103

2006-05-07 23:44:21 10.10.10.100(2466)WARNING: 57P02: terminating
connection because of crash of another server process

2006-05-07 23:44:21 10.10.10.100(2466)DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.

2006-05-07 23:44:21 10.10.10.100(2466)HINT: In a moment you should be able
to reconnect to the database and repeat your command.

2006-05-07 23:44:21 10.10.10.100(2466)LOCATION: quickdie, postgres.c:2103

2006-05-07 23:44:21 10.10.12.100(4021)WARNING: 57P02: terminating
connection because of crash of another server process

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 07 May 2006 00:16
To: Andy Male
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #2419: could not reattach to shared memory

I wish I had more to suggest to you. We are working on a few problems
with semaphore on Win2003 SP1, but nothing related to shared memory.

Andy Male wrote:

Hi Andy,

This is your problem:

2006-05-07 23:44:20 10.10.12.100(4018)PANIC: 42501: could not write to log
file 0, segment 90 at offset 2998272, length 8192: Permission denied
2006-05-07 23:44:20 10.10.12.100(4018)CONTEXT: writing block 75 of relation
1663/20632/100738
SQL statement "update tbl_query_ui_consumer_session_mapping set
ui_consumer_session_data_action_type_id = 3, client_received = false where
session_id = $1 and client_session_id = $2 "
PL/pgSQL function "fn_driver_session_updated" line 58 at SQL
statement
2006-05-07 23:44:20 10.10.12.100(4018)LOCATION: XLogWrite, xlog.c:1474
2006-05-07 23:44:20 10.10.12.100(4018)STATEMENT: select * from
fn_Driver_Session_Updated($1::int8,$2::bool)

The "Permission denied" message is a report Postgres is getting from the
operating system. Notice it is marked as PANIC -- an unrecoverable
error. What you should be investigating is why does the operating
system reject the writing of that file. It clearly is a database file;
try looking at the files named $PGDATA/base/20632/100738 or possibly
$PGDATA/pg_tblspc/1663/20632/100738. What permissions do those files
have? Who owns them? If it's not the user who runs the Postgres
processes, or they are not accesible to it, then something else in the
system changed that, which is what you need to figure out and disable.

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

On Mon, 2006-05-08 at 08:31 -0400, Alvaro Herrera wrote:

2006-05-07 23:44:20 10.10.12.100(4018)PANIC: 42501: could not write to log
file 0, segment 90 at offset 2998272, length 8192: Permission denied

This is a pg_xlog error, so it looks like you have a whole-system issue,
not just isolated tables.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Hi,

Thank you for your replies.

I accept that the "Permission denied" problem does suggest that the DB error
may be caused by the OS somehow.

There is no problem with the permissions/ownership of the files because the
Postgres account created and owns and those files; this rules out any sort
of security problem. It is possible that the file is inaccessible through
some other reason and that Postgres is merely reporting that it can't access
the file and, that it 'could' be caused by a permissions problem rather than
it 'is' a permissions problem. The error log information in this case isn't
really very useful since it doesn't accurately report the real cause of the
error. The OS doesn't report any other errors and there are no other
systems problem or file access problems; the only problem lies within the
database.

To try and reproduce the problem on another machine, I did a new install of
the same version of Postgres (8.1.3) and dump/restored the database onto
this new server. So far it has been running with the same load and activity
for almost 30 hours and the problem has not surfaced. In theory, Postgres
and the database are identical and therefore, the fact that it doesn't error
in the same way does confirm this is an OS problem (assuming the problem
doesn't occur at some point in the future). The two servers are identical
hardware and have the same version of OS, Windows Server 2003 SP1.

None of this helps me because I still have a production server on which I
can't run the database since I can't debug the error. Any suggestions?

Thank you for your assistance.

Andy

"Andy" <andy@otelex.co.uk> writes:

To try and reproduce the problem on another machine, I did a new install of
the same version of Postgres (8.1.3) and dump/restored the database onto
this new server. So far it has been running with the same load and activity
for almost 30 hours and the problem has not surfaced. In theory, Postgres
and the database are identical and therefore, the fact that it doesn't error
in the same way does confirm this is an OS problem (assuming the problem
doesn't occur at some point in the future). The two servers are identical
hardware and have the same version of OS, Windows Server 2003 SP1.

We've seen reports of intermittent permission failures on Windows being
caused by broken antivirus software. What security software have you
got on those machines, and does the failure go away if you remove it?

regards, tom lane

I have removed the AV software and the problem doesn't go away, it does seem
to happen less frequently but is still there. It is the same error -

"FATAL: could not reattach to shared memory (key=5432001, addr=01960000):
Invalid argument"

The referenced key and address are always the same, is there some way we can
view the data being stored in the memory, or look at what data was stored in
the memory, and where, to see if the reference is valid?

Regards

Andy

"Tom Lane <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us> writes: