Forum: Building VoltDB Applications

Post: Help defining table partitioning

Help defining table partitioning
tsouza
Oct 30, 2014
Hello,

From the Flight example the Reservation table's primary key is ReservationID but it's partitioned on FlightID. I've tried a similar schema:


CREATE TABLE document
(
  id           varchar(32)  UNIQUE     NOT NULL
, key          varchar(32)                   NOT NULL
, datasourceId varchar(32)             NOT NULL
, datatypeId   varchar(32)               NOT NULL
, createdAt    varchar(25)               NOT NULL
, content      varbinary(1048576)    NOT NULL
, PRIMARY KEY (id)
);

PARTITION TABLE document ON COLUMN key;



That is document table's primary key is id but is partitioned on key
But after trying to compile I get the following error:


ERROR: [null]: Invalid use of PRIMARY KEY. The PRIMARY KEY on the partitioned table DOCUMENT does not include the partitioning column KEY. See the documentation for the 'CREATE TABLE' and 'CREATE INDEX' commands and the 'ASSUMEUNIQUE' keyword.


What am I missing?

Regards,
Thiago Souza
xin
Oct 30, 2014
The problem is that your Primary Key does not contain Partition column, so we can not guarantee the Primary Key is globally unique when VoltDB rebalances the tuples across the partitions.

A possible work around for your problem is make the primary key on (id, key) or use VoltDB's special AssumeUnique.

More information can be found at: http://docs.voltdb.com/UsingVoltDB/ddlref_createtable.php

Please let us know if you have more questions.


Hello,

From the Flight example the Reservation table's primary key is ReservationID but it's partitioned on FlightID. I've tried a similar schema:


CREATE TABLE document
(
  id           varchar(32)  UNIQUE     NOT NULL
, key          varchar(32)                   NOT NULL
, datasourceId varchar(32)             NOT NULL
, datatypeId   varchar(32)               NOT NULL
, createdAt    varchar(25)               NOT NULL
, content      varbinary(1048576)    NOT NULL
, PRIMARY KEY (id)
);

PARTITION TABLE document ON COLUMN key;



That is document table's primary key is id but is partitioned on key
But after trying to compile I get the following error:


ERROR: [null]: Invalid use of PRIMARY KEY. The PRIMARY KEY on the partitioned table DOCUMENT does not include the partitioning column KEY. See the documentation for the 'CREATE TABLE' and 'CREATE INDEX' commands and the 'ASSUMEUNIQUE' keyword.


What am I missing?

Regards,
Thiago Souza
tsouza
Oct 31, 2014
Hi there,

I would not like to include 'key' in primary key as it would be wrong. Could please give me an example of how the 'assumeunique' index would be to solve my case?

Thanks!
Thiago Souza
pzhao
Dec 15, 2014
Thiago,

Sorry for the late reply to your question.

Here is a quick example of assumeunique index:
CREATE TABLE T38
(
C INTEGER
C6 DECIMAL ASSUMEUNIQUE NOT NULL
, CONSTRAINT con38A ASSUMEUNIQUE
(
C
)
);

Peter