11. Virtuoso Cluster Programming

11.3. SQL Options

For purposes of debugging or writing stored procedures that are specifically meant to work with local data only, it is useful to disable cluster functionality.

This is done with the NO CLUSTER table option. This can be used in the table option clause of a table in a FROM or in an update or delete.

Specially when writing procedures to be called with DAQ, see below, it us necessary to ensure that the procedures will not access data outside of the host running them.

Examples:

select count (*) from x table option (no cluster);
update x table option (no cluster) set y = y + 1;
update x set y = 1 where current of cr option (no cluster)
insert into xx key xx option (no cluster) (c1, c2) values (1, 2);

Note that for positioned (where current of) deletes and updates the option is at the end. For searched ones it is after the table. Use the explain () function to see the compilation of statements to make sure about the locus of execution. If a table is to be accessed across the cluster, this is indicated in the output.

All other SQL options work as with single server databases.

11.3.1. Parallel INSERT Options

Searched updates and deletes can be parallelized as they are written. However, inserts in loops are not as obviously parallelizable. Therefore insert has an option for queueing multiple inserts into a queue for partitioned parallel execution.

The format of the statement is:

INSERT INTO table [KEY <key>] [OPTION (INTO <daq>) <column list> <values or select>

The DAQ is a distributed async queue object, see section on calling procedures in cluster. Specifying the INTO daq option partitions the insert, determining where each key should go and buffers the operation into the DAQ. When the result of the DAQ is fetched, all the buffered operations are sent in parallel, as a single message per node concerned. This is easily tens or hundreds of times more efficient than inserting row by row. The transactional behavior is controlled by the DAQ, see the section on calling procedures in cluster. Inserts can mix with other types of operations in the DAQ. If the DAQ is transactional, any failed insert, as in the case of uniqueness violation, will make the transaction uncommittable, preserving integrity.

Example

create procedure ct_fill_daq (in n1 int, in n2 int, in st int := 1)
{
 declare daq any;
 declare ct int;
 daq := daq (1);
 for (ct := n1; ct < n2; ct:=ct+st)
 {
 insert into CT option (into daq) (ROW_NO, DT) values (ct, cast (ct as varchar) || ' xxxxxxxxxxxxxxx');
 }
 while (daq_next (daq));
}

11.3.2. INSERT KEY Option

When indices are partitioned on different columns, indices pertaining to a single row can be located on different nodes. In special situations, for example when exploiting co-location of keys from different tables, one may wish to insert a things index by index. This happens with the use of partitioned functions with DAQ's. Inserting some indices and not inserting others will make an inconsistent database, thus even if keys are inserted separately, all keys should be inserted within the same transaction or have some other guarantee of getting all inserted. Also, application code will have to change if the indices change. This is not considered good practice.

Consider a partition account table and a history table both partitioned on the same key.

We could make a procedure updating the balance of the account and recording the event.

create table acct (id int, balance numeric);
alter index acct on acct partition (a_id int);
create table hist (h_id int, ts timestamp, note varchar, primary key (h_id, ts);
alter index hist on hist partition (h_id int);

create procedure evt (in a_id int, in delta numeric, in note varchar)
{
 update acct table option (no cluster) set balance = balance + delta where id = a_id;
 insert into hist key hist option (no cluster) (h_id, note) values (a_id, note);
)

Such a procedure would be called on a particular partition using a DAQ In practice, the RDF store uses single key operations for atomically reserving ID's for IRI's, for example..