Thread: [GENERAL] PostGreSQL Timeout, auto shutdown and Pkey errors

On 06/28/2017 11:32 AM, Niel Smith wrote:
> Hi,
>
> So I have a few problems with my PostGreSQL and was hoping you could
> help me out. one is I'm getting random timeouts and the errors I'm
> seeing in the logs are

What version of Postgres and where did you get it?
OS and version?
Which logs?

>
> On the 6/22/17 at 10:51am we had  ERROR [08001] timeout expired at
> System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode
> retcode)

A timeout expired, probably on client side.
Where are the clients relative to the server, local network or Internet?

>
>   2:23pm we had Error during indexfile unfixed fields readCurIndex:
> 6598CurFileNum: 551
>
> 4;25 pm Error during indexfile unfixed fields readCurIndex:
> 7054CurFileNum: 551
>
> 6/23/17 @ 5:57PM Error during indexfile unfixed fields readCurIndex:
> 4449CurFileNum: 552

Where are the above coming from as I can not find a reference to
readCurIndex in either the Postgres or psqodbc code?

>
> 6/24/17 9:43 am ERROR [08001] timeout expired
>     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)

Another timeout.

>
> 6/26/17 7:21am  Exception during IsDuplicateSelect ID from
> di_central.di_web_records where starttime = '2017-06-26 01:07:34' AND
> channelnum = 95 AND ulseqlow = 5095480The connection has been disabled.
>    at System.Data.Odbc.OdbcConnection.ConnectionIsAlive(Exception
> innerException) >
> x2 6/26/17 7:21am ERROR [08001] FATAL:  the database system is shutting down
>     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)

Looks like bad code in IsDuplicateSelect ID is triggering an exception
that shuts down the database.

>
>   Having an Issue of timing out or just randomly shutting down?  other
> errors I'm getting are duplicate key value violates unique constraint
> "di_web_users_pkey"

I am not seeing that in what you show. In any case that is what a unique
constraint is supposed to do, throw an error when you try to create a
duplicate entry. The client/application code will need to be able to
deal with that.

> 2017-06-21 06:53:09 MDT ERROR:  relation
> "di_central.di_web_user_hmaxfield" does not exist at character 37
> 2017-06-21 06:53:09 MDT STATEMENT:  select column_name, is_checked from
> di_central.di_web_user_hmaxfield
> 2017-06-21 09:43:35 MDT LOG:  could not receive data from client: An
> established connection was aborted by the software in your host machine.
>
> 2017-06-26 07:21:35 MDT FATAL:  terminating connection due to
> administrator command
> 2017-06-26 07:21:47 MDT FATAL:  the database system is shutting down

Looks like some external process is shutting down the server.

>
> I also have MySQL running on this PC and have an error in event viewer
> we have a spam of errors Task category is WebHost and the source is
> system.serviceModel 4.0.0.0 WebHost failed to process a request.
>   Sender Information:
> System.ServiceModel.ServiceHostingEnvironment+HostingManager/45653674
>   Exception: System.ServiceModel.ServiceActivationException: The service
> '/MySQLService.svc' cannot be activated due to an exception during
> compilation.  The exception message is: Memory gates checking failed
> because the free memory (89677824 bytes) is less than 5% of total
> memory. Process Name: w3wp Process ID: 10672

The only thing I understand in the above is:

"...the free memory (89677824 bytes) is less than 5% of total memory."

Whatever it is wants more memory.

>
> if anyone can help me out with these errors that would be very much
> appreciated.
>
> Thank you
>
>
> Niel Smith - Support Technician
> Equature
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/28/2017 11:32 AM, Niel Smith wrote:
> Hi,
>
> So I have a few problems with my PostGreSQL and was hoping you could
> help me out. one is I'm getting random timeouts and the errors I'm
> seeing in the logs are

What version of Postgres and where did you get it?
OS and version?
What is the source for the logs below?

>
> On the 6/22/17 at 10:51am we had  ERROR [08001] timeout expired at
> System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode
> retcode)

A timeout expired, probably on client side.
Where are the clients relative to the server, local network or Internet?

>
>   2:23pm we had Error during indexfile unfixed fields readCurIndex:
> 6598CurFileNum: 551
>
> 4;25 pm Error during indexfile unfixed fields readCurIndex:
> 7054CurFileNum: 551
>
> 6/23/17 @ 5:57PM Error during indexfile unfixed fields readCurIndex:
> 4449CurFileNum: 552

Where are the above coming from as I can not find a reference to
readCurIndex in either the Postgres or psqodbc code?

>
> 6/24/17 9:43 am ERROR [08001] timeout expired
>     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)

Another timeout.

>
> 6/26/17 7:21am  Exception during IsDuplicateSelect ID from
> di_central.di_web_records where starttime = '2017-06-26 01:07:34' AND
> channelnum = 95 AND ulseqlow = 5095480The connection has been disabled.
>    at System.Data.Odbc.OdbcConnection.ConnectionIsAlive(Exception
> innerException) >
> x2 6/26/17 7:21am ERROR [08001] FATAL:  the database system is shutting down
>     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)

Looks like bad code in IsDuplicateSelect ID is triggering an exception
that shuts down the database.

>
>   Having an Issue of timing out or just randomly shutting down?  other
> errors I'm getting are duplicate key value violates unique constraint
> "di_web_users_pkey"

I am not seeing that in what you show. In any case that is what a unique
constraint is supposed to do, throw an error when you try to create a
duplicate entry. The client/application code will need to be able to
deal with that.

> 2017-06-21 06:53:09 MDT ERROR:  relation
> "di_central.di_web_user_hmaxfield" does not exist at character 37
> 2017-06-21 06:53:09 MDT STATEMENT:  select column_name, is_checked from
> di_central.di_web_user_hmaxfield
> 2017-06-21 09:43:35 MDT LOG:  could not receive data from client: An
> established connection was aborted by the software in your host machine.
>
> 2017-06-26 07:21:35 MDT FATAL:  terminating connection due to
> administrator command
> 2017-06-26 07:21:47 MDT FATAL:  the database system is shutting down

Looks like some external process is shutting down the server.

>
> I also have MySQL running on this PC and have an error in event viewer
> we have a spam of errors Task category is WebHost and the source is
> system.serviceModel 4.0.0.0 WebHost failed to process a request.
>   Sender Information:
> System.ServiceModel.ServiceHostingEnvironment+HostingManager/45653674
>   Exception: System.ServiceModel.ServiceActivationException: The service
> '/MySQLService.svc' cannot be activated due to an exception during
> compilation.  The exception message is: Memory gates checking failed
> because the free memory (89677824 bytes) is less than 5% of total
> memory. Process Name: w3wp Process ID: 10672

The only thing I understand in the above is:

"...the free memory (89677824 bytes) is less than 5% of total memory."

Whatever it is wants more memory.

>
> if anyone can help me out with these errors that would be very much
> appreciated.
>
> Thank you
>
>
> Niel Smith - Support Technician
> Equature
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/30/2017 11:35 AM, Niel Smith wrote:

Please reply to list also.
Ccing list.

Also to help with replies in the future could you change your posting
style to something like:

https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

Thanks.

> What version of Postgres and where did you get it? This Was from
> postgresSQL 9.5

  > I'm not sure where this was downloaded from this was
  > installed on the server before I started with the company I work
for. my
  > guess is from the postgreSQL website

> OS and version?
   >this is running a windows 7 embedded OS.

Do you have anti-virus software running on this machine?

> Which logs?

  >From the PostgreSQL logs.

>
> A timeout expired, probably on client side.
> Where are the clients relative to the server, local network or Internet?
   > Local network
>
> Where are the above coming from as I can not find a reference to
> readCurIndex in either the Postgres or psqodbc code?


   >This is from a Log
   > called Trace and it comes from my Central Storage interface

What is Central Storage?

>
> Timestamp: 6/20/2017 3:09:15 PM
> Message: Error during indexfile unfixed fields readCurIndex:
> 3292CurFileNum: 550
> Severity: Error, Machine: DIRELIANT2,  Thread Name: SQLThreadPM
> Process Name: C:\Program Files\Dynamic Instruments, Inc\Digital
> Recorder\DICSInterface\DICSInterface.exe
> ----------------------------------------
> Timestamp: 6/21/2017 4:51:05 AM
> Message: Error during indexfile unfixed fields readCurIndex:
> 6249CurFileNum: 550
> Severity: Error, Machine: DIRELIANT2,  Thread Name: SQLThreadPM
> Process Name: C:\Program Files\Dynamic Instruments, Inc\Digital
> Recorder\DICSInterface\DICSInterface.exe
> ----------------------------------------

So the above is this third party instrument, correct?:

http://www.officer.com/company/10028921/dynamic-instruments

If so you might want to get in touch with them, as this looks to be
coming from their software.

> Timestamp: 6/21/2017 9:43:28 AM
> Message: Exception during making connection to
> databaseDriver={PostgreSQL
> UNICODE};Server=127.0.0.1;Port=5432;UID=postgres;PWD=root;Database=postgres;ERROR
> [08001] timeout expired

That looks fairly straight forward the connection attempt timed out.

The question is why?

Do you see anything in the Postgres logs from this time period that
might help?

Do you have network issues that might prevent the device from connecting
in a timely fashion?

Is there any consistency to when these errors occur?

>     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
> RetCode retcode)
>     at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection
> connection, OdbcConnectionString constr, OdbcEnvironmentHandle
> environmentHandle)
>     at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection
> outerConnection, OdbcConnectionString connectionOptions)
>     at
> System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool,
> DbConnection owningObject)
>     at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
> owningConnection, DbConnectionPoolGroup poolGroup)
>     at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
>     at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
>     at System.Data.Odbc.OdbcConnection.Open()
>     at DICSInterface.DISQL.ConnectToSQL(OdbcConnection& conn, String
> DatabaseType, String IPAddress)
> Severity: Critical, Machine: DIRELIANT2,  Thread Name:
> Process Name: C:\Program Files\Dynamic Instruments, Inc\Digital
> Recorder\DICSInterface\DICSInterface.exe
>
> Niel Smith - Support Technician



--
Adrian Klaver
adrian.klaver@aklaver.com