Crash of Postgresql on Windows

On Wed, Feb 4, 2009 at 6:18 AM, Emilie Laffray <emilie.laffray@gmail.com>wrote:

Hello,

I am sending this email since I am hitting a snag with postgresql. I
am currently running the following version of postgresql:
Postgresql 8.3.5 on Windows XP Pro 32bits

I am getting a crash whenever I try to perform an update on some rows.
More precisely, I have recently imported a dataset and managed to work
through it easily. However, when performing one update I am getting
the following error:

GMT PANIC: could not write to log file 40, segment 44 at offset
8929280, length 1744896: Invalid argument
This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's support team for more information.
2009-02-04 12:02:34 GMT LOG: WAL writer process (PID 2916) exited
with exit code 3
2009-02-04 12:02:34 GMT LOG: terminating any other active server processes
2009-02-04 12:02:34 GMT WARNING: terminating connection because of
crash of another server process
2009-02-04 12:02:34 GMT 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.
2009-02-04 12:02:34 GMT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2009-02-04 12:02:34 GMT WARNING: terminating connection because of
crash of another server process
2009-02-04 12:02:34 GMT 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.
2009-02-04 12:02:34 GMT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2009-02-04 12:02:34 GMT WARNING: terminating connection because of
crash of another server process
2009-02-04 12:02:34 GMT 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.
2009-02-04 12:02:34 GMT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2009-02-04 12:02:34 GMT LOG: all server processes terminated;
reinitializing
2009-02-04 12:02:35 GMT FATAL: pre-existing shared memory block is still
in use
2009-02-04 12:02:35 GMT HINT: Check if there are any old server
processes still running, and terminate them.

I am a bit lost on what to do next. I have tried running the query
several times even changing the parameters to make sure if it wasn't
something in the sql.
UPDATE gtable AS g
SET code = '00' -- 04
WHERE g.code = '04'
AND g.cc = 'TW';

UPDATE gtable AS g
SET code = '00' -- 04
WHERE g.d IN (6724652, 1673813);

Those two queries do exactly the same thing but they both crash with
the same information, except for the length between the two queries.
What should I do?

Emilie Laffray

What does '--' do?

Thanks,

Andrew

Andrew Gould wrote:

What does '--' do?

-- Is an SQL comment

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

Andrew Gould wrote:

On Wed, Feb 4, 2009 at 6:18 AM, Emilie Laffray <emilie.laffray@gmail.com>wrote:

UPDATE gtable AS g
SET code = '00' -- 04
WHERE g.code = '04'
AND g.cc = 'TW';

What does '--' do?

It's a comment delimeter

--
Richard Huxton
Archonet Ltd

Emilie Laffray wrote:

Hello,

I am sending this email since I am hitting a snag with postgresql. I
am currently running the following version of postgresql:
Postgresql 8.3.5 on Windows XP Pro 32bits

I am getting a crash whenever I try to perform an update on some rows.
More precisely, I have recently imported a dataset and managed to work
through it easily. However, when performing one update I am getting
the following error:

GMT PANIC: could not write to log file 40, segment 44 at offset
8929280, length 1744896: Invalid argument

1. Can you run any other queries
a. A SELECT
b. A different UPDATE

2. Do you have any reason to think the filesystem may have become corrupted?

3. Do you have an antivirus scanner that might be interfering with
PostgreSQL? That frequently causes strange problems.

--
Richard Huxton
Archonet Ltd

what about free disc space ?
aren't you running out of ?

Hello,

1) I can perform a query on the rows that I am trying to update. I have
also successfully updated rows before in the same table and also after.

2) I have no reason to believe that the filesystem got corrupted. I have
started a scan before you asked the question wondering if it could be
the problem. So far, no problem has been found.

3) I have an antivirus running, but again this antivirus has been
running for months and I have worked on postgresql also for months.
There has been an update yesterday to that antivirus, but I don't think
it had an impact.

4) The disk space is not a problem with more than 160GB free.

I tried also in the meantime to run a vacuum, and I got some messages
that the indexes had a problem and it was working to fix it. It crashed.
I restarted my pc and could perform the vacuum just fine afterwards.

Richard Huxton wrote:

Emilie Laffray wrote:

3) I have an antivirus running, but again this antivirus has been
running for months and I have worked on postgresql also for months.
There has been an update yesterday to that antivirus, but I don't think
it had an impact.

I tried also in the meantime to run a vacuum, and I got some messages
that the indexes had a problem and it was working to fix it. It crashed.
I restarted my pc and could perform the vacuum just fine afterwards.

I don't run PG on Windows other than to test, but this definitely sounds
like antivirus problems to me. They're quite sophisticated nowadays and
it might be that it takes a certain pattern of activity to trigger it.

Try (1) turning your antivirus off and running the update and if that
works, (2) excluding all the data directories and PostgreSQL from it's
checking.

--
Richard Huxton
Archonet Ltd

Hello,

well Ｉcouldn't turn off my antivirus since it is controlled by our IT
management team. However, since I rebuilt the indexes, the query has
been running fine. I don't understand what happpened. Before asking for
help, I made sure I could repeat the problem over several reboots.

Emilie Laffray

Richard Huxton wrote:

Emilie Laffray wrote:

Hello,

well Ｉcouldn't turn off my antivirus since it is controlled by our IT
management team. However, since I rebuilt the indexes, the query has
been running fine. I don't understand what happpened. Before asking for
help, I made sure I could repeat the problem over several reboots.

It's not impossible you have found a bug in PostgreSQL's code. However,
I can't think of a situation where rebuilding an index would fail once
then work fine after a reboot. Not if your hardware is working OK.

I would contact your IT team and ask them to add an exclusion for your
PostgreSQL directories though. Otherwise you'll never be sure what is
causing any crashes.

--
Richard Huxton
Archonet Ltd