Thread

1. PostgreSQL service terminated by query

   adrian.kitchingman@dse.vic.gov.au — 2013-03-26T05:55:25Z

   I'm hoping I can get some info on a query which terminates my PostgreSQL 
   service.
   The query is a relatively simple PostGIS query:
   
           SELECT en.gid, ST_LENGTH(en.geom) total_length, 
   (ST_DUMP(ST_INTERSECTION(en.geom, evc.geom))).geom::geometry(Linestring, 
   3111) geom
           FROM en, evc
           WHERE ST_INTERSECTS(en.geom, evc.geom) AND en.gid =355620;
   
   I've run this query successfully with en.gid equal to other gid values 
   (gid is the table PK). The success seems rather haphazard though so I'd 
   like to find out the underlying issue. The above feature with gid = 355620 
   is valid and appears no different from other features which have worked.
   The log text when the service crashes is:
   
   2013-03-26 15:49:10 EST LOG:  database system was interrupted; last known 
   up at 2013-03-26 15:42:29 EST
   2013-03-26 15:49:10 EST LOG:  database system was not properly shut down; 
   automatic recovery in progress
   2013-03-26 15:49:10 EST LOG:  record with zero length at 9A/E7AAD938
   2013-03-26 15:49:10 EST LOG:  redo is not required
   2013-03-26 15:49:10 EST LOG:  database system is ready to accept 
   connections
   2013-03-26 15:49:10 EST LOG:  autovacuum launcher started
   2013-03-26 15:49:55 EST LOG:  server process (PID 3536) was terminated by 
   exception 0xC0000005
   2013-03-26 15:49:55 EST HINT:  See C include file "ntstatus.h" for a 
   description of the hexadecimal value.
   2013-03-26 15:49:55 EST LOG:  terminating any other active server 
   processes
   2013-03-26 15:49:55 EST WARNING:  terminating connection because of crash 
   of another server process
   2013-03-26 15:49:55 EST 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.
   2013-03-26 15:49:55 EST HINT:  In a moment you should be able to reconnect 
   to the database and repeat your command.
   2013-03-26 15:49:55 EST WARNING:  terminating connection because of crash 
   of another server process
   2013-03-26 15:49:55 EST 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.
   2013-03-26 15:49:55 EST HINT:  In a moment you should be able to reconnect 
   to the database and repeat your command.
   2013-03-26 15:49:55 EST WARNING:  terminating connection because of crash 
   of another server process
   2013-03-26 15:49:55 EST 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.
   2013-03-26 15:49:55 EST HINT:  In a moment you should be able to reconnect 
   to the database and repeat your command.
   2013-03-26 15:49:55 EST WARNING:  terminating connection because of crash 
   of another server process
   2013-03-26 15:49:55 EST 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.
   2013-03-26 15:49:55 EST HINT:  In a moment you should be able to reconnect 
   to the database and repeat your command.
   2013-03-26 15:49:55 EST LOG:  all server processes terminated; 
   reinitializing
   2013-03-26 15:50:05 EST FATAL:  pre-existing shared memory block is still 
   in use
   2013-03-26 15:50:05 EST HINT:  Check if there are any old server processes 
   still running, and terminate them.
   
   I suspect the entry 'server process (PID 3536) was terminated by exception 
   0xC0000005' is the culpret. Does anyone have any suggestion on the meaning 
   and cause?
   This may be a PostGIS issue but I figured if I'd see if the general 
   Postgres community would have more of an insight first.
   
   I'm running PostgreSQL 9.1 with PostGIS 2.0 installed on an WinXP SP3: 4GB 
   RAM machine. Shared_buffers set at 50MB. Let me know if further info 
   needed.
   
   Cheers
   
   Adrian
   
   
   
   Notice:
   This email and any attachments may contain information that is personal, confidential,
   legally privileged and/or copyright. No part of it should be reproduced, adapted or communicated without the prior written consent of the copyright owner. 
   
   It is the responsibility of the recipient to check for and remove viruses.
   
   If you have received this email in error, please notify the sender by return email, delete it from your system and destroy any copies. You are not authorised to use, communicate or rely on the information contained in this email.
   
   Please consider the environment before printing this email.
   

2. Re: PostgreSQL service terminated by query

   Richard Huxton <dev@archonet.com> — 2013-03-26T11:28:59Z

   On 26/03/13 05:55, adrian.kitchingman@dse.vic.gov.au wrote:
   > I'm hoping I can get some info on a query which terminates my PostgreSQL
   > service.
   > The query is a relatively simple PostGIS query:
   
   > The log text when the service crashes is:
   
   > 2013-03-26 15:49:55 EST LOG: server process (PID 3536) was terminated by
   > exception 0xC0000005
   > 2013-03-26 15:49:55 EST HINT: See C include file "ntstatus.h" for a
   > description of the hexadecimal value.
   
   > I'm running PostgreSQL 9.1 with PostGIS 2.0 installed on an WinXP SP3:
   > 4GB RAM machine. Shared_buffers set at 50MB. Let me know if further info
   > needed.
   
   This is a Windows memory-related error. It might be due to a library 
   problem, bad RAM or a corrupted pointer in the database table itself.
   
   1. Can you reliably produce the error with this specific gid?
   2. Can you dump the database (or perhaps just the tables in question)?
   
   If we can't find any problems in the database itself and you can spare 
   the downtime, it may be worth running a RAM checker overnight.
   
   > Notice:
   > This email and any attachments may contain information that is personal,
   > confidential,
   > legally privileged and/or copyright. No part of it should be reproduced,
   > adapted or communicated without the prior written consent of the
   > copyright owner.
   
   Oh no, too late!
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   
   

3. Re: PostgreSQL service terminated by query

   adrian.kitchingman@dse.vic.gov.au — 2013-03-27T00:54:20Z

   Thanks for the suggestion Richard.
   
   I dumped the two tables in question and restored them which got the query 
   working for a while until it eventually crashed the service again at 
   another gid. I'll do a RAM check tonight.
   I transferred the whole database to another computer and so far the query 
   is working without issues.
   
   Government men in black will be knocking on your door soon. ;-)
   
   Cheers
   
   Adrian
   
   
   
   
   From:   Richard Huxton <dev@archonet.com>
   To:     adrian.kitchingman@dse.vic.gov.au, 
   Cc:     pgsql-general@postgresql.org
   Date:   26/03/2013 10:32 PM
   Subject:        Re: [GENERAL] PostgreSQL service terminated by query
   Sent by:        pgsql-general-owner@postgresql.org
   
   
   
   On 26/03/13 05:55, adrian.kitchingman@dse.vic.gov.au wrote:
   > I'm hoping I can get some info on a query which terminates my PostgreSQL
   > service.
   > The query is a relatively simple PostGIS query:
   
   > The log text when the service crashes is:
   
   > 2013-03-26 15:49:55 EST LOG: server process (PID 3536) was terminated by
   > exception 0xC0000005
   > 2013-03-26 15:49:55 EST HINT: See C include file "ntstatus.h" for a
   > description of the hexadecimal value.
   
   > I'm running PostgreSQL 9.1 with PostGIS 2.0 installed on an WinXP SP3:
   > 4GB RAM machine. Shared_buffers set at 50MB. Let me know if further info
   > needed.
   
   This is a Windows memory-related error. It might be due to a library 
   problem, bad RAM or a corrupted pointer in the database table itself.
   
   1. Can you reliably produce the error with this specific gid?
   2. Can you dump the database (or perhaps just the tables in question)?
   
   If we can't find any problems in the database itself and you can spare 
   the downtime, it may be worth running a RAM checker overnight.
   
   > Notice:
   > This email and any attachments may contain information that is personal,
   > confidential,
   > legally privileged and/or copyright. No part of it should be reproduced,
   > adapted or communicated without the prior written consent of the
   > copyright owner.
   
   Oh no, too late!
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   
   -- 
   Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
   To make changes to your subscription:
   http://www.postgresql.org/mailpref/pgsql-general
   
   
   
   
   Notice:
   This email and any attachments may contain information that is personal, confidential,
   legally privileged and/or copyright. No part of it should be reproduced, adapted or communicated without the prior written consent of the copyright owner. 
   
   It is the responsibility of the recipient to check for and remove viruses.
   
   If you have received this email in error, please notify the sender by return email, delete it from your system and destroy any copies. You are not authorised to use, communicate or rely on the information contained in this email.
   
   Please consider the environment before printing this email.