Which data node the table is stored, how to query in postgres-xl?

Question

when creation of table, the distribute sub statement is not used.

If there is 2 node, how could I query where the table is actual stored?

If there are two tables and they are belong to the same schema, will they be stored different data node?

Answer 1

From the documentation:

If DISTRIBUTE BY is not specified, columns with UNIQUE constraint
will be chosen as the distribution key. If no such column is
specified, distribution column is the first eligible column 
in the definition. If no such column is found, then the table 
will be distributed by ROUNDROBIN.

In your use-case scenario, when creating a table without using the TO NODE nodename instruction or specifying a distribution method, that table is created in all datanodes, and the rows are distributed by either hash or roundrobin between the datanodes.

You can see what rows are in what datanode by using EXECUTE DIRECT (which is a SQL command specific to Postgres-XL):

test_db=# CREATE TABLE test (id integer UNIQUE, name varchar(30) NULL);
CREATE TABLE
test_db=# insert into test (id, name) values (0,'0test');
INSERT 0 1
test_db=# insert into test (id, name) values (1,'1test');
INSERT 0 1
test_db=# insert into test (id, name) values (2,'2test');
INSERT 0 1
test_db=# insert into test (id, name) values (3,'3test');
INSERT 0 1
test_db=# EXECUTE DIRECT ON (datanode1) 'select * from test';
 id | name
----+-------
  1 | 1test
  2 | 2test
(2 rows)

test_db=# EXECUTE DIRECT ON (datanode2) 'select * from test';
 id | name
----+-------
  0 | 0test
  3 | 3test
(2 rows)

As I just mentioned, you can have a table stored on a certain datanode if you use TO NODE nodename or TO GROUP groupname.