When to allocate and deallocate a state

To each invocation of the ITER support function, the database server automatically passes a pointer to the state buffer. When you need to initialize or update the state information, the ITER function can handle the modification in either of two ways, as the following table describes.

Changing the state	State memory duration	Results
Merge the aggregate argument into the existing state in-place and return the existing state.	The existing state has a PER_COMMAND memory duration: For single-valued and opaque-type states, this state is the system-allocated state buffer. For a pointer-valued state, this state is a user-allocated state buffer.	The new state value is at the address that the database server has passed into the ITER function. The ITER function then returns this address as the updated state. Because the state memory has a PER_COMMAND memory duration, the database server can reuse the same state for subsequent invocations of ITER.
Allocate fresh memory for a new state, merge the existing state with the new aggregate argument into this state, and return this new state.	The new state has a PER_ROUTINE memory duration: For a single-valued state, this state can be either a declared local variable or user-allocated PER_ROUTINE memory. For an opaque-type state, the new state must be user-allocated PER_ROUTINE memory. For a pointer-valued state, this state is user-allocated memory with either a PER_ROUTINE or PER_COMMAND memory duration. However, for PER_COMMAND memory, you must also handle deallocation of the old state. For more information, see Using a pointer-valued state for a UDA.	The new state value is at the address of the new state. The ITER function then returns the address of the new state as the updated state. Because this memory has a PER_ROUTINE memory duration, the database server must copy the returned state back into the PER_COMMAND buffer.

The new state method can be slower than the in-place method. Design your ITER support function to use the in-place method whenever possible. When the database server can skip the copy operation, you can improve performance of your UDA.

To determine which of these methods was used in the ITER support function, the database server compares the state value that ITER returns and the state value that was passed into ITER. If these two pointers identify the same memory location, the ITER function has modified the state in-place. Therefore, the database server does not need to perform the copy operation. If these two pointers identify different memory locations, the database server proceeds with the copy operation.

Aggregate support functions have the following restrictions on the deallocation of an aggregate state:

For any state other than a pointer-valued state, no aggregate support function must deallocate the state memory.
No aggregate support function can return a NULL-valued pointer as the state.