MIME-Version: 1.0
From: Erik Mata <erikmata@gmail.com>
Date: Thu, 19 Nov 2020 16:23:26 +0100
Message-ID: 
 <CAB0C2fn42hT7RQGK+MwTG-rnxj6eKEhnBr3vyjvZsVOu0gv7qQ@mail.gmail.com>
Subject: Fail to create distributed table in Citus
To: pljava-dev@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dfebe005b4774f40"
Precedence: bulk

--000000000000dfebe005b4774f40
Content-Type: text/plain; charset="UTF-8"

I have set up a local docker environment with a total of 4 Postgres server
instances, all including Citus (9.5.0) and PL/JAVA (1.6.1) extensions:
- 1 as Citus master
- 3 as Citus workers

I have set up a simple routine in PL/JAVA which creates a table and
requests that the table be distributed in the cluster, as:

CREATE TABLE test1 (a BIGINT PRIMARY KEY);
SELECT create_distributed_table('test1', 'a', colocate_with=>'none');

I connect to the Citus master server via a JDBC-connection to localhost.
I then call the PL/JAVA routine, as:

SELECT test_create_distributed_table();

This results in a local table being created on the Citus master node, but
no tables are created on the workers and the Citus utility functions tell
me that the table is NOT distributed.

If I use the same statements that I have included in the PL/JAVA routine
(see above), but I execute them from within e.g. DataGrip, connected to the
Citus master node, the table is created and distributed correctly. I can
see that the correct tables are created on the workers and the Citus
utility functions tell me that the table is indeed distributed.

So, it seems that there is nothing wrong with my setup (cluster).

The problem, as far as I can tell, should be somewhere in how PL/JAVA
connects to the Citus master node. The PL/JAVA routine runs in a JVM that
is spawned by the Postgres server process on the Citus master node. The
PL/JAVA routine acquires a connection to the local database via a somewhat
fake JDBC-connection (jdbc:default:connection) as explained in the PL/JAVA
documentation.

I suspect that this fake JDBC-connection is the reason that the Citus
master node is unable to distribute the table to the rest of the cluster
nodes.

As a side-note: From within PL/JAVA routines, I am able to execute other
types of statements that interact with distributed tables created outside
of the PL/JAVA routine. I am able to DROP, TRUNCATE, INSERT, UPDATE, DELETE
etc. Just not SELECT create_distributed_table.

Any help is appreciated!

--000000000000dfebe005b4774f40
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I have set up a local docker environment with a total of 4=
 Postgres server instances, all including Citus (9.5.0) and PL/JAVA (1.6.1)=
 extensions:<div>- 1 as Citus master</div><div>- 3 as Citus workers</div><d=
iv><br></div><div>I have set up a simple routine in PL/JAVA which creates a=
 table and requests that the table be distributed in the cluster, as:<br></=
div><div><br></div><div>CREATE TABLE test1 (a BIGINT PRIMARY KEY);<br></div=
><div>SELECT create_distributed_table(&#39;test1&#39;, &#39;a&#39;, colocat=
e_with=3D&gt;&#39;none&#39;);</div><div><br></div><div>I connect to the Cit=
us master server via a JDBC-connection to localhost.</div><div>I then call =
the PL/JAVA routine, as:<br><br>SELECT test_create_distributed_table();</di=
v><div><br></div><div>This results in a local table being created on the Ci=
tus master node, but no tables are created on the workers and the Citus uti=
lity functions tell me that the table is NOT distributed.</div><div><br></d=
iv><div>If I use the same statements that I have included in the PL/JAVA ro=
utine (see above), but I execute them from within e.g. DataGrip, connected =
to the Citus master node, the table is created and distributed correctly. I=
 can see that the correct tables are created on the workers and the Citus u=
tility functions tell me that the table is indeed distributed.</div><div><b=
r></div><div>So, it seems that there is nothing wrong with my setup (cluste=
r).</div><div><br></div><div>The problem, as far as I can tell, should be s=
omewhere in how PL/JAVA connects to the Citus master node. The PL/JAVA rout=
ine runs in a JVM that is spawned by the Postgres server process on the Cit=
us master node. The PL/JAVA routine acquires a connection to the local data=
base via a somewhat fake JDBC-connection (jdbc:default:connection) as expla=
ined in the PL/JAVA documentation.</div><div><br></div><div>I suspect that =
this fake JDBC-connection is the reason that the Citus master node is unabl=
e to distribute the table to the rest of the cluster nodes.</div><div><br><=
/div><div>As a side-note: From within PL/JAVA routines, I am able to execut=
e other types of statements that interact with distributed tables created o=
utside of the PL/JAVA routine. I am able to DROP, TRUNCATE, INSERT, UPDATE,=
 DELETE etc. Just not SELECT create_distributed_table.</div><div><br></div>=
<div>Any help is appreciated!</div></div>

--000000000000dfebe005b4774f40--