Forum: Building VoltDB Applications

Post: Replicated tables and stored procedures

Replicated tables and stored procedures
Sep 14, 2015
Hello everyone,

I have several replicated tables and I'm going to write SP to get information from them. As far as I understand description of SP for replicated tables does not contain part "PARTITION ON" and I get MULTI-PARTIOTIONED SP - it means I lock all partitions.
But it is weird - I know that all data are in every partition, I can get it from one partition and I don't have to lock all partitions. How can I resolve it ? Thank you.
Sep 14, 2015
Hi Anton,

You can partition the procedure with an arbitrary partitioned table, as long as you have an input parameter that can be used to determine the partitioning.

For example:

1> create table replicated_table (id integer);
Command succeeded.
2> create table partitioned_table (id integer not null);
Command succeeded.
3> partition table partitioned_table on column id;
Command succeeded.
4> create procedure sp_replicated_query partition on table partitioned_table column id parameter 0 as select * from replicated_table where id = ?;
Command succeeded.
5> exec REPLICATED_TABLE.insert 3;
(Returned 1 rows in 0.00s)
6> exec sp_replicated_query 3;

(Returned 1 rows in 0.00s)

Best regards,
Sep 14, 2015
You are correct that a stored procedure definition without a "PARTITION ON" clause is a multi-partition procedure.
However, this does NOT automatically imply that the procedure will need to execute on every partition.
It only implies that the procedure execution does not need to be routed to one particular partition.
A multi-partition procedure needs to execute on all partitions, blocking all other procedures, ONLY IF it reads or writes partitioned data OR writes replicated data.
If the multi-partition procedure ONLY reads replicated data, it will be executed and it will block other procedures only on a single partition at a single convenient (local) site chosen by the VoltDB server. In many cases, this will allow procedures to be run concurrently on other sites.