SQL Guide : solidDB® SQL statements : REFRESH
  
REFRESH
REFRESH publication [parameters] [FULL]
[OPTIMISTIC|PESSIMISTIC]
[COMMITBLOCK number_of_rows]
[TIMEOUT {DEFAULT | FOREVER | timeout_ms}]
Usage
The REFRESH statement is a storageless refresh command. It conserves memory by streaming the associated data. It also saves I/O bandwidth because no messages are written to disk. Each command blocks until it is successfully executed.
The optional property OPTIMISTIC|PESSIMISTIC define the way the replica table is being locked.
▪The OPTIMISTIC mode (the default value) defines that the concurrency control method depends on the table type and the isolation level.
– With disk-based tables (D-tables) in the OPTIMISTIC mode, the REFRESH always succeeds.
– With in-memory tables (M-tables), row-level locking is used.
– With D-tables in the PESSIMISTIC mode, if a lock cannot be obtained, PESSIMISTIC fails and returns an error.
▪PESSIMISTIC defines that the table is exclusively locked, regardless of the table type and isolation level chosen, for the time of refresh. If the lock cannot be obtained, the refresh request fails and returns an error.
The COMMITBLOCK property defines the size of the REFRESH commit block, that is, the number of rows that are committed in one transaction. The COMMITBLOCK keyword is useful if the reply to the REFRESH request contains REFRESHes of large publications. Defining the commit block size has a positive impact on the performance of the replica database. It is recommended that there are no online users accessing the database when the COMMITBLOCK property is being used.
▪If COMMITBLOCK is not used, the execution of REFRESH is a part of the current transaction.
The effect of REFRESH can be revoked by issuing the ROLLBACK command. To make the effect of REFRESH durable, COMMIT WORK has to be issued.
REFRESH is idempotent in the sense that it can be issued repeatedly, over the rollbacks and commits, and the effects are (in the quiescent state of the database) always the same.
▪If the COMMITBLOCK clause is used, each transfer part (of the specified size) is committed in Replica implicitly. The ROLLBACK statement removes the effect of the latest transfer part only. COMMIT WORK commits the last transfer part.
The TIMEOUT property defines how long the replica server waits for the reply message. If TIMEOUT is not defined, FOREVER is used.
Return values
Error code
Description
13133
Not a valid license for this product
25004
Dynamic parameters are not supported
25015
Syntax error: error_message, line line_number
25024
Master not defined
25025
Node name not defined
25026
Not a valid master user
25044
SYNC_CONFIG system publication takes only character arguments
25056
Autocommit not allowed
25071
Not registered to publication publication_name
25072
Already registered to publication publication_name
13XXX
Table level error
21XXX
Communication error
10006
Concurrency conflict (simultaneous other operation)
13047
No privilege for operation
13056
Insert not allowed for pseudo column
25005
Message message_name is already active
25018
Illegal message state
In the replica, the message can be executed only using the MESSAGE FORWARD statement if the message is ended and the ending transaction is committed.
25024
Master not defined
This message is produced if double quotation marks, rather than single quotation marks, are used around the connect_string in a MESSAGE FORWARD statement.
For example, if the master node is given the node name “master” (which is a reserved word and therefore should be delimited by double quotation marks), and if connect string for the is tcp localhost 1315, the following MESSAGE statements shown are correct:
--On the replica
...
--double quotation marks
MESSAGE msg1 BEGIN TO "master";
...
--single quotation marks
MESSAGE msg2 FORWARD TO 'tcp localhost 1315';
The MESSAGE BEGIN statement defines (within the replica server) what the node name of the master is. The MESSAGE FORWARD statement may contain the connect string to the server.
25026
Not a valid master user
25031
Transaction is active, operation failed
25035
Message message_name is in use.
A user is currently creating or forwarding this message.
25040
User ID user_id is not found.
While executing a message reply an attempt to map a master user to a local replica id failed.
25041
Subscription to publication publication_name not found
25048
Publication publication_name request info not found
25052
Failed to set node name to node_name.
25054
Table table_name is not set for synchronization history
25055
Connect information is allowed only when not registered
The connect info in MESSAGE message_name FORWARD TO connect_info options is allowed only if the replica has not yet been registered to the master database.
25056
Autocommit not allowed
25057
The replica database is registered to a master database
25060
Column column_name does not exist on publication publication_name result set in table table_name
13XXX
Table level error
13124
User ID num not found
This message is generated, for example, if the user has been dropped.
25056
Autocommit not allowed
Examples
The following example shows a synchronous, messageless refresh:
REFRESH publ_states; PESSIMISTIC; COMMITBLOCK 1000; COMMIT WORK;
See also
solidDB® SQL statements