Stado

Alter table inherit does not work with round robin partitioned tables

Asked by Ranga Gopalan

Hi,

I'm working with GridSQL 2.0 - haven't been able to move my project to Stado yet -

We are using the "alter table <tablename> inherit <parent table>" syntax to add a child table to a parent. This works fine when the table is "partitioned" (or distributed across nodes) using a specific column.

However, this does not work if I specify the "ROUND ROBIN on ALL" clause to distribute the table across nodes.

Sample SQL code:
{code}
GridSQL -> create table p1 (f1 int, f2 varchar(10)) round robin on all;
OK
GridSQL -> create table p2 (f1 int, f2 varchar(10)) round robin on all;
OK
GridSQL -> alter table p2 inherit p1;
SQLException: ERROR: Can not prepare request: Partitioning of table p2 differs from p1
{code}

I see where this is happening in the source code - in SqlAlterInherit.java:

{code}
                if (!parentTable.getPartitionMap().equals(
                        targetTable.getPartitionMap())) {
                    throw new XDBServerException("Partitioning of table "
                            + parent.getTableName() + " differs from "
                            + tableName);
                }
{code}

The equals() method on RobinPartitionMap returns false unconditionally and so this valdiation fails and the "alter table inherit" does not go through. The two tables in the example above are distributed in round robin fashion on both nodes in my test database.

Is there any specific / inherent reason that GridSQL cannot support the "alter table inherit" when the parent and child tables are "round robin on all"? Or is this just an unnecessary (possibly over-restrictive validation) ?

Thanks in advance for your reply,

Ranga

Question information

Language:
English Edit question
Status:
Solved
For:
Stado Edit question
Assignee:
No assignee Edit question
Solved by:
Ranga Gopalan
Solved:
Last query:
Last reply:
Revision history for this message
Ranga Gopalan (ranga-gopalan) said : 		#1

I updated the code and tried with the following changes in SqlAlterInherit.java:

Original code:

{code}
                if (!parentTable.getPartitionMap().equals(
                        targetTable.getPartitionMap())) {
                    throw new XDBServerException("Partitioning of table "
                            + parent.getTableName() + " differs from "
                            + tableName);
                }

{code}

Modified Code:

{code}
                // Check partitioning
                final PartitionMap parentMap = parentTable.getPartitionMap();
                final PartitionMap targetMap = targetTable.getPartitionMap();

                boolean doPartitionsMatch
                    = Arrays.equals(parentMap.allPartitions().toArray(),
                                     targetMap.allPartitions().toArray());

                if (!doPartitionsMatch) {
                    throw new XDBServerException("Partitioning of table "
                            + parent.getTableName() + " differs from "
                            + tableName);
                }

{code}

This seems to work and lets me do the alter and also retrieve data that is inserted into the child via the parent table.

Revision history for this message
Jim Mlodgenski (jim-cirrusql) said : 		#2

Thanks for the patch Ranga. I will apply it to the the trunk. Do you have a requirement that you use round-robin instead of partitioned? There are not many people using round-robin distribution so you are more likely to run into cases like this where things will work work partition but not round-robin.

Revision history for this message
Ranga Gopalan (ranga-gopalan) said : 		#3

Hi Jim,

The round-robin distribution makes sense for us in case of some "cube" tables that are created to provide aggregates grouped by a set of dimensions - some of the tables contain dimensions that aren't very selective (i.e. not too many values) so there is no obvious candidate for specifying a partitioning key. However, I'm still not able use the round robin because the "order by" behavior seems broken with round robin - (I posted another question about that).

Revision history for this message
Ranga Gopalan (ranga-gopalan) said : 		#4

Thanks Jim -

The round-robin distribution makes sense for us in case of some "cube" tables that are created to provide aggregates grouped by a set of dimensions - some of the tables contain dimensions that aren't very selective (i.e. not too many values) so there is no obvious candidate for specifying a partitioning key. However, I'm still not able use the round robin because the "order by" behavior seems broken with round robin - (I posted another question about that).

Revision history for this message
Jim Mlodgenski (jim-cirrusql) said : 		#5

I created a bug for the order by issue