Re: Postgresql, sql client and I/O exceptions

On Thu, Jul 18, 2013 at 9:20 AM, Paul Maddock
<paul(dot)maddock(at)spidasoftware(dot)com> wrote:
> I am having trouble with postgresql connection.
>
> I am using squirrel sql client to write sql code for data qc'ing. On many
> occasions this has happened. It closes my connection on my client and stops
> my sql transaction.
>
> There are roughly 3 million records in the database I am editing.
>
> Version of Postgresql:
> psql (PostgreSQL) 9.2.4
>
> JDBC for squirrel sql client:
> postgresql-9.2-1003.jdbc4.jar
>
> --------------------------------------
> syslog
> 276 Jul 18 09:10:08 test-minapps kernel: [173502.656976] postgres[15212]:
> segfault at 7f4c34e8a44e ip 00007f4c02d13605 sp 00007fff92ba69f0 error 4 in
> postgres[7f4c02c86000+54e000]
> 277 Jul 18 09:10:12 test-minapps lsassd[1136]: 0x7f1607fff700:Error code
> 40286 occurred during attempt 0 of a ldap search. Retrying.
>
> postgresql log
> """
> 2013-07-18 09:10:24 EDT LOG: terminating any other active server processes
> 2013-07-18 09:10:24 EDT WARNING: terminating connection because of crash of
> another server process
> 2013-07-18 09:10:24 EDT 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.
> """
> * ~20
>
> 2013-07-18 09:10:26 EDT LOG: all server processes terminated;
> reinitializing
> 2013-07-18 09:10:28 EDT LOG: database system was interrupted; last known up
> at 2013-07-18 09:03:54 EDT
> 2013-07-18 09:10:28 EDT LOG: database system was not properly shut down;
> automatic recovery in progress
> 2013-07-18 09:10:28 EDT LOG: redo starts at 39/BBAE7D60
> 2013-07-18 09:10:29 EDT LOG: record with zero length at 39/BD17A7D8
> 2013-07-18 09:10:29 EDT LOG: redo done at 39/BD17A7A8
> 2013-07-18 09:10:29 EDT LOG: last completed transaction was at log time
> 2013-07-18 09:10:10.968978-04
> 2013-07-18 09:11:08 EDT LOG: database system is ready to accept connections
>
>
> squirrel sql log
> Error: An I/O error occured while sending to the backend. SQLState: 08006
> ErrorCode: 0
> -------------------------------------
>
> I'm researching the issue myself, but if you might know the cause it would
> help me tremendously.

database is crashing. typical culprits are 3rd party extensions,
userland C routines, bad hardware, non-up-to-date postgres, etc.
assuming none of those apply to you, next steps are to use various
debugging tools (gdb especially and strace), and logging. Ideally you
could grab a core for postgres and post a backtrace. Even more
ideally you could provide a reproducible crash case.

merlin