Using the HOTSTANDBY_STATE function

To implement application polling of the primary and secondary servers, you can use the HOTSTANDBY_STATE function. This function is equivalent to using the following command:

It allows the application to request the current HotStandby (HSB) state when it is connected to the server.

Note This function has no arguments. For a description of each possible state that this function might return, see HotStandby server states.

An application, whether or not it is HSB-enabled, should have error handling that allows the application to replay a failed or aborted transaction.

In a non-HSB environment, a transaction might be aborted because of a concurrency conflict (optimistic tables) or deadlock (pessimistic tables). The application must catch these error situations and either automatically try the transaction again or ask the user to re-execute the transaction.

If your application already has code to handle failed or aborted transactions, then it is relatively easy to extend this code to make use of HSB.

In a simplified example, the application pseudo-code with proper error handling for an application (that is not designed for high availability) might be similar to the following example:

BEGIN TRANSACTION EXECUTE
APPLICATION LOGIC
PREPARE & EXECUTE STATEMENTS
COMMIT TRANSACTION
IF ERROR OCCURRED
          IF ERROR == concurrency conflict or deadlock GO TO                  BEGIN TRANSACTION
          END IF
          other error handling
END IF ;

Improving the above application to make it work in a high availability environment is very simple. You must add code so that the application can perform the following tasks:

▪ in the case of an error, find the server that is currently in one of the following states: PRIMARY ACTIVE, PRIMARY ALONE, or STANDALONE.

BEGIN TRANSACTION
EXECUTE APPLICATION LOGIC
PREPARE & EXECUTE STATEMENTS
COMMIT TRANSACTION
IF ERROR OCCURRED
      IF ERROR == server unavailable for write transactions
             FIND CURRENT PRIMARY SERVER
             GO TO BEGIN TRANSACTION
      END IF
      IF ERROR == concurrency conflict or deadlock
             GO TO BEGIN TRANSACTION
      END IF
      IF ERROR == something else other error handling
      END IF
END IF

To find the current primary server, check the current state of both servers (try to reconnect if necessary) and if either of them is PRIMARY ACTIVE, PRIMARY ALONE or STANDALONE, set that server as the current primary. If neither server has an appropriate state, wait a while and check the current server states again.