How to Check Database Connection is still valid?

[swhite]

Hi

I had a situation where a Postgres Server was restarted and my application crashed even though I used the IsDatabase() function to check that #Database was valid. IsDatabase() continue to return a value despite the fact that the connection had been reset. Is there a way to detect when the connection to the database has been rest because using IsDatabase() does not detect when the server is restarted? I should also mention I am using pgBouncer and it was also reset.

Thanks,
Simon

[JHPJHP]

Hi swhite,

pg_backend_pid(): Returns the process ID of the server process attached to the current session.
• https://pgpedia.info/p/pg_backend_pid.html
• https://docs.aws.amazon.com/redshift/la ... D_PID.html

Code: Select all

DatabaseQuery(#Database, "SELECT pg_backend_pid()")

[swan]

As a matter of interest "SELECT @@SPID;" is SQL Servers equivalent, a system function returns the server process ID (SPID) of the current user process.
Also "SELECT SESSION_ID();" a function returning the ID of the current session. For those using SQL Server.

[spikey]

By definition anything requiring a 'SELECT' isn't going to work in this particular situation.

I haven't tried it to be certain but I think you probably want PQstatus. There's an import and example in this post: Postgres client functions possible ???

[swhite]

Hi

I will look into PQStatus as I know that anything using a select statement will fail because that is the problem I am experiencing when the server is reset.

Thanks,
Simon

[JHPJHP]

Hi swhite,

I completely misread your first post, thanks spikey for the clarity.

1. Installed PostgreSQL (v17.6.1) for Windows:
• PostgreSQL Downloads
• Database Connection Control Functions
2. Included the following from Stack Builder:
• pgAgent :: Add-ons, tools and utilities
• pgBouncer :: Add-ons, tools and utilities
• psqlODBC (64 bit) :: Database Drivers
3. Copied the following binaries to my project folder:
• C:\Program Files\PostgreSQL\17\bin\libintl-9.dll
• C:\Program Files\PostgreSQL\17\bin\libpq.dll
• C:\Program Files\PostgreSQL\17\bin\libssl-3-x64.dll
➤ Alternatively, create the following under Environment Variables:
• Path: C:\Program Files\PostgreSQL\17\bin
4. Execute the following script:
• First, update Password$.

Code: Select all

Enumeration
  #CONNECTION_OK
  #CONNECTION_BAD
EndEnumeration

ImportC "C:\Program Files\PostgreSQL\17\lib\libpq.lib"
  PQconnectdb(conninfo.p-utf8)
  PQerrorMessage(conn)
  PQfinish(conn)
  PQstatus(conn)
EndImport

Procedure TestPostgresConnection(Host.s, Port, dbName.s, User.s, Password.s)
  ConnectionInfo$ = "host=" + Host + " port=" + Str(Port) + " dbname=" + dbName + " user=" + User + " password=" + Password
  Connection = PQconnectdb(ConnectionInfo$)

  If PQstatus(Connection) = #CONNECTION_OK
    MessageRequester("PostgreSQL", "Connection Successful.", #PB_MessageRequester_Info)
  Else
    ErrorMessage$ = PeekS(PQerrorMessage(Connection), -1, #PB_UTF8)
    MessageRequester("PostgreSQL", "Connection failed: " + ErrorMessage$, #PB_MessageRequester_Error)
  EndIf
  PQfinish(Connection)
EndProcedure

Host$     = "localhost"
Port      = 5432
dbName$   = "postgres"
User$     = "postgres"
Password$ = "[YOUR-PASSWORD]"

TestPostgresConnection(Host$, Port, dbName$, User$, Password$)

[swhite]

Hi

I tried the code but it does not do what I need. I stepped through the code and just before executing the IF PQStatus() I restarted the Postgres server and it still reported the connection was successful.

Simon

Hi swhite,

I completely misread your first post, thanks spikey for the clarity.

1. Installed PostgreSQL (v17.6.1) for Windows:
• PostgreSQL Downloads
• Database Connection Control Functions
2. Included the following from Stack Builder:
• pgAgent :: Add-ons, tools and utilities
• pgBouncer :: Add-ons, tools and utilities
• psqlODBC (64 bit) :: Database Drivers
3. Copied the following binaries to my project folder:
• C:\Program Files\PostgreSQL\17\bin\libintl-9.dll
• C:\Program Files\PostgreSQL\17\bin\libpq.dll
• C:\Program Files\PostgreSQL\17\bin\libssl-3-x64.dll
➤ Alternatively, create the following under Environment Variables:
• Path: C:\Program Files\PostgreSQL\17\bin
4. Execute the following script:
• First, update Password$.

Code: Select all

Enumeration
  #CONNECTION_OK
  #CONNECTION_BAD
EndEnumeration

ImportC "C:\Program Files\PostgreSQL\17\lib\libpq.lib"
  PQconnectdb(conninfo.p-utf8)
  PQerrorMessage(conn)
  PQfinish(conn)
  PQstatus(conn)
EndImport

Procedure TestPostgresConnection(Host.s, Port, dbName.s, User.s, Password.s)
  ConnectionInfo$ = "host=" + Host + " port=" + Str(Port) + " dbname=" + dbName + " user=" + User + " password=" + Password
  Connection = PQconnectdb(ConnectionInfo$)

  If PQstatus(Connection) = #CONNECTION_OK
    MessageRequester("PostgreSQL", "Connection Successful.", #PB_MessageRequester_Info)
  Else
    ErrorMessage$ = PeekS(PQerrorMessage(Connection), -1, #PB_UTF8)
    MessageRequester("PostgreSQL", "Connection failed: " + ErrorMessage$, #PB_MessageRequester_Error)
  EndIf
  PQfinish(Connection)
EndProcedure

Host$     = "localhost"
Port      = 5432
dbName$   = "postgres"
User$     = "postgres"
Password$ = "[YOUR-PASSWORD]"

TestPostgresConnection(Host$, Port, dbName$, User$, Password$)

[JHPJHP]

Hi swhite,

Extended my previous example:
• Back to using pg_backend_pid. Even after a reset if a query is successful the PIDs can be compared.
• I'm hoping SELECT isn't an issue going through the Library as it was with the built-in PureBasic commands.

Code: Select all

Enumeration
  #CONNECTION_OK
  #CONNECTION_BAD
EndEnumeration

Enumeration
  #PGRES_EMPTY_QUERY
  #PGRES_COMMAND_OK
  #PGRES_TUPLES_OK
  #PGRES_COPY_OUT
  #PGRES_COPY_IN
  #PGRES_BAD_RESPONSE
  #PGRES_NONFATAL_ERROR
  #PGRES_FATAL_ERROR
  #PGRES_COPY_BOTH
  #PGRES_SINGLE_TUPLE
  #PGRES_TUPLES_CHUNK
  #PGRES_PIPELINE_SYNC
  #PGRES_PIPELINE_ABORTED
EndEnumeration

ImportC "C:\Program Files\PostgreSQL\17\lib\libpq.lib"
  PQclear(res)
  PQconnectdb(conninfo.p-utf8)
  PQerrorMessage(conn)
  PQexec(conn, command.p-utf8)
  PQfinish(conn)
  PQgetvalue(res, tup_num, field_num)
  PQresultStatus(res)
  PQstatus(conn)
EndImport

Procedure.s GetSingleValue(Result)
  Value$ = PeekS(PQgetvalue(Result, 0, 0), -1, #PB_UTF8)
  ProcedureReturn Value$
EndProcedure

Procedure TestPostgresConnection(Host.s, Port, dbName.s, User.s, Password.s)
  ConnectionInfo$ = "host=" + Host + " port=" + Str(Port) + " dbname=" + dbName + " user=" + User + " password=" + Password
  Connection = PQconnectdb(ConnectionInfo$)

  If PQstatus(Connection) = #CONNECTION_OK
    Result = PQexec(Connection, "SELECT pg_backend_pid();")
    Status = PQresultStatus(Result)

    If Status <> #PGRES_EMPTY_QUERY And Status = #PGRES_TUPLES_OK
      Value$ = GetSingleValue(Result)
      MessageRequester("PostgreSQL", "Connection & Query Successful: " + Value$, #PB_MessageRequester_Info)
    Else
      WarningMessage$ = PeekS(PQerrorMessage(Connection), -1, #PB_UTF8)
      MessageRequester("PostgreSQL", "Query Failed: " + WarningMessage$, #PB_MessageRequester_Warning)
    EndIf
    PQclear(Result)
  Else
    ErrorMessage$ = PeekS(PQerrorMessage(Connection), -1, #PB_UTF8)
    MessageRequester("PostgreSQL", "Connection Error: " + ErrorMessage$, #PB_MessageRequester_Error)
  EndIf
  PQfinish(Connection)
EndProcedure

Host$     = "localhost"
Port      = 5432
dbName$   = "postgres"
User$     = "postgres"
Password$ = "[YOUR-PASSWORD]"

TestPostgresConnection(Host$, Port, dbName$, User$, Password$)

[JHPJHP]

Hi swhite,

Finally tested PostgreSQL using the built-in PureBasic commands.
• For additional error handling OnErrorCall was added.
• From my tests OnErrorCall wasn't needed.

1. Added a break point after the connection was made.
2. At the break point both the PostgreSQL and pgBouncer services were restarted.
3. Continued the application.
4. No crash just the expected MessageRequester error.

Code: Select all

Procedure PostgreSQL_OnError()
  pqMessage$ = "Database Error Detected:" + #LF$ + #LF$ + UCase(ErrorMessage()) + #LF$ + #LF$

  If ErrorLine() = -1
    pqMessage$ + "Additional Information: Compiler > Compiler Options" + #LF$ + "• Enable OnError lines support"
  Else
    pqMessage$ + "ADDRESS: " + ErrorAddress() + #LF$ + "LINE: " + Str(ErrorLine())
  EndIf
  MessageRequester("PostgreSQL", pqMessage$, #PB_MessageRequester_Error)
EndProcedure

OnErrorCall(@PostgreSQL_OnError())
UsePostgreSQLDatabase()

Procedure TestPostgresConnection(Host.s, Port, dbName.s, User.s, Password.s)
  ConnectionInfo$ = "host=" + Host + " port=" + Str(Port) + " dbname=" + dbName

  If OpenDatabase(0, ConnectionInfo$, User, Password)
    If DatabaseQuery(0, "SELECT pg_backend_pid();")
      FirstDatabaseRow(0)
      Value$ = GetDatabaseString(0, 0)
      MessageRequester("PostgreSQL", "Connection & Query Successful: " + Value$, #PB_MessageRequester_Info)
      FinishDatabaseQuery(0)
    Else
      MessageRequester("PostgreSQL", "Query Failed: " + DatabaseError(), #PB_MessageRequester_Warning)
    EndIf
  Else
    MessageRequester("PostgreSQL", "Connection Error: " + DatabaseError(), #PB_MessageRequester_Error)
  EndIf
EndProcedure

Host$     = "localhost"
Port      = 5432
dbName$   = "postgres"
User$     = "postgres"
Password$ = "[YOUR-PASSWORD]"

TestPostgresConnection(Host$, Port, dbName$, User$, Password$)

[swhite]

Hi

I generally check the DatabaseQuery and report any errors. So I will check my code because I may have missed a spot and that may explain why my code is not handling the disconnect properly.

Thanks JHPJHP for all your input.

Simon