SQL replication implementation rules

There are rules related to the implementation of SQL replication. In a replication environment, SQL statements that cannot be replicated by using statement replay are either replicated by value or rejected with a replication error.

The following rules apply to the implementation of SQL replication:
  • Update transactions against replicated data or global data must be done on the primary node.
  • Update transactions against global data such as users, groups, security definitions, and global privileges are permitted on the primary only when there is a connection to a replicated database or to the SYSTEM database.
  • The SYSTEM database is not replicated. This applies to the local objects, such as tables and views. Because global objects are not part of any replicated database, they are replicated.
  • Temporary tables themselves are not replicated. However, using temporary tables in an update transaction (a transaction that accesses a temporary table and then writes into a replicated, non-temporary table) triggers by-value replication. That is, temporary tables are like any other non-replicated resource that is allowed and supported by using by-value replication.
  • Update transactions against replicated objects that select from non-replicated databases, system tables, and virtual tables trigger by-value replication.
  • If the primary node is suspended, any update SQL transaction against a replicated database results in an error. Transactions must begin and end in a time span during which replication was continuously active.
  • If you want to use a WHERE clause in an SQL expression that depends on the optimizer choosing a specific execution order, you must rewrite the clause to use an IF/THEN statement. Otherwise, the primary and replica might evaluate the clause in a different sequence, resulting in a division by zero error. For example, the statement that follows, if evaluated in the opposite order, returns a division by zero error:
    ( ID <> 0 and ( XYZ / ID > 10) )
    To correct the problem, rewrite the statement with a nested IF/THEN:
    IF ID <> 0 THEN IF ( XYZ / ID > 10 )
  • Use of SQL statements with aggregate functions, such as SUM or AVG, when modifying double precision or other floating-point types might result in a data mismatch between the primary and replica when using by-SQL replication. This can also occur with two queries on a single host, due to the inherent limitations of floating-point data types. Processing finite precision IEEE-754 values requires special considerations, such as using less than and greater than instead of equal operators. To ensure bit-for-bit identical results, set the REPLICATE_ALWAYS_BY_VALUE session variable to ON for the session that is executing update SQL transactions.
  • A table becomes a versioned table when users add columns to or drop columns from the table. In a replication environment, updating rows in a versioned table can cause the replica to suspend with the following error: Versioned tables do not support DELETE operations that join again to the versioned table. To prevent this error, issue the GROOM TABLE command with the VERSIONS option on the primary node after adding or dropping columns, within the same transaction.
For a list of SQL commands in the replication environment that differ from standard Netezza® SQL commands, see SQL command differences in the replication environment.