Forum: Building VoltDB Applications

Post: Can any of those limitations be solved in the future?

Can any of those limitations be solved in the future?
monster
Sep 26, 2010
After looking at the documentation, I think those limitations will make some use of VoltDB basically impossible. Is there any hope of those limitations being "solved" in the future?

* CHECK and FOREIGN KEY constraints are not supported in CREATE TABLE.

* Joins of two partitioned tables in a multi-partitioned stored procedure are not supported.

* SELECT ... FROM cannot join a table to itself.
(Is this a special case of the previous limitation?)

* Do not use UPDATE to change the value of a partitioning column.
(It shouldn't be that difficult to just do the DELETE and INSERT "under the hood"?)

Is there some bug ID for those, so that one can track the "progress" ?
re: Can any of those limitations be solved in the future?
tcallaghan
Sep 27, 2010
M,

I've responded to your items in-line.

* CHECK and FOREIGN KEY constraints are not supported in CREATE TABLE.

Foreign Key enforcement must be done within stored procedures by the VoltDB application developer. We may eventually add support for database level enforcement of FKs, but it is not currently on the product roadmap.

* Joins of two partitioned tables in a multi-partitioned stored procedure are not supported.

No workaround currently exists, this request is in Jira at http://issues.voltdb.com/browse/ENG-496

* SELECT ... FROM cannot join a table to itself.
(Is this a special case of the previous limitation?)

No workaround currently exists, this request is in Jira at http://issues.voltdb.com/browse/ENG-451

* Do not use UPDATE to change the value of a partitioning column.
(It shouldn't be that difficult to just do the DELETE and INSERT "under the hood"?)

As you said you currently need to do a discrete delete and insert, this request is in Jira at http://issues.voltdb.com/browse/ENG-112

There are certainly use cases where VoltDB is not a fit. While some of your concerns currently have work-arounds, others do not. Can you email me your contact information so we can discuss further?

-Tim
support@voltdb.com
My questions were just
monster
Sep 27, 2010
M,

I've responded to your items in-line.

* CHECK and FOREIGN KEY constraints are not supported in CREATE TABLE.

Foreign Key enforcement must be done within stored procedures by the VoltDB application developer. We may eventually add support for database level enforcement of FKs, but it is not currently on the product roadmap...

-Tim
support@voltdb.com


My questions were just general. I'm about to start a small company doing simple web apps, and I had long decided to go for the "traditional" Java+Hibernate+Postgress+Terracotta/Ehecache for clustering, but your product got me really excited, in particular the no-single-point-of-failure (with K>0), which is only available in the commercial version of Terracotta.

The "CHECK and FOREIGN KEY constraints" issue can be implemented in user code, so it is only an issue if you have many stored procedure that duplicate the check code.

The UPDATE is currently the least problem, but only because CHECK and FOREIGN KEY are also not supported. If they were, and were used, then the work-around might not work anymore (or my understanding of those SQL features is incorrect, as I'm rather rusty in SQL DDL).

My use-case for the UPDATE is simple: Imagine a "market" game, where you own buildings in a town. You want the building list of someone to pop up quickly, so you partition on the owner column of the building table, so that the select is single-partitioned. But when someone buys the building of someone else (which would be the point of the game), you would have to update the owner column, along with price, ...


Now the real problem is "Joins of two partitioned tables in a multi-partitioned stored procedure are not supported." I can intuitively understand why; this might be the biggest "compromise" you had to make to achieve scalability in your design, as I don't really see a general work-around, but allowing this would be prohibitively costly in term of performance and memory use. Anyone considering using VoltDB has to be sure that they don't need this, which might be difficult to judge, if you haven't build your application yet and haven't got a clear database design. If you don't know yet which joins you might want to make, or which tables are going to be too big to replicate, then you can't say for sure that you don't need that. That is precisely the position I am in.