Subject: Re: Fail to create distributed table in Citus
To: Erik Mata <erikmata@gmail.com>, pljava-dev@lists.postgresql.org
References: 
 <CAB0C2fn42hT7RQGK+MwTG-rnxj6eKEhnBr3vyjvZsVOu0gv7qQ@mail.gmail.com>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5FB6AB35.5060101@anastigmatix.net>
Date: Thu, 19 Nov 2020 12:28:21 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAB0C2fn42hT7RQGK+MwTG-rnxj6eKEhnBr3vyjvZsVOu0gv7qQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 11/19/20 10:23, Erik Mata wrote:
> 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.

That's kind of fascinating. Have you also raised this in a Citus-specific
list? They might hold some pieces of the puzzle. :)

> 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 ...

The jdbc:default:connection in PL/Java really isn't a new connection of
any kind; the JVM is running inside the PostgreSQL backend process that is
handling your existing connection, and the jdbc:default:connection is
no more than a JDBC-like wrapper over SPI. It is largely the same as if
you wrote your test_create_distributed_table() function in C, and it
contained

Datum test_create_distributed_table(PG_FUNCTION_ARGS) {
    ...
    SPI_execute("CREATE TABLE test1 (a BIGINT PRIMARY KEY)", false, 0);
    SPI_execute("SELECT create_distributed_table("
        "'test1', 'a', colocate_with=>'none')", false, 0);
    ...
}

(It might even be interesting to write a C version like that, and see
what it does.)

There seems to be something about create_distributed_table() that doesn't
work when it is invoked in that context, and even more oddly, seems to be
failing silently, rather than reporting an error of some kind.

Are you able to share your Java code? I'm sure it is straightforward,
but maybe it would help to pin the details down.

I think the Citus create_distributed_table source is here

https://github.com/citusdata/citus/blob/v9.5.0/src/backend/distributed/commands/create_distributed_table.c

but in a quick skim I haven't had anything jump out at me as where it
might be failing. Yet.

Regards,
-Chap