Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kfoXh-0001K2-F4 for pljava-dev@arkaria.postgresql.org; Thu, 19 Nov 2020 18:20:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kfoXg-0001OR-Eq for pljava-dev@arkaria.postgresql.org; Thu, 19 Nov 2020 18:20:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kfoXg-0001OK-34 for pljava-dev@lists.postgresql.org; Thu, 19 Nov 2020 18:20:24 +0000 Received: from mail-ot1-x342.google.com ([2607:f8b0:4864:20::342]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kfoXd-0006Y2-DI for pljava-dev@lists.postgresql.org; Thu, 19 Nov 2020 18:20:23 +0000 Received: by mail-ot1-x342.google.com with SMTP id 79so6210644otc.7 for ; Thu, 19 Nov 2020 10:20:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=n5zTjs8BP6kUIe5I6tQqe8yO16k6+d3FbHE3QCvBJv8=; b=ntf/iWFVmhJKusSKhZ2357b0+s9NV9ykSXB+BD2SZpS0NMcanvpA+rpEIH1929Ox11 +Hxlbkx5oBreGtMon//i4Lt9Yd7yQnXk76RqBCbnIZBO1cV9oWUCADM23VAdty+s+OnM WiGiQAxilWoniMK/WE5vkkI+oSSo5T+zPm32STJzbzP3ANj7ENe6nVlBp85JCF/CFTpG Tfxqjqm9Pat5FmrG8XQepMU3n4Gx1hyeyOiJvdGt/bYaU4QyQYOvVU+2Qx9FR5w19SGS LFENANMOIZEMkMPN954c82iOM+YB8rENwMSLw+wIMkFwFXHludlyUxiafCEq/H4gQK0a eGNw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=n5zTjs8BP6kUIe5I6tQqe8yO16k6+d3FbHE3QCvBJv8=; b=nvxRo7MAZR4/3jjLsSeDHNLYYNsgf/aBS2ymlVaRAsotMxppzLZT9aFx0rIXpNbPEq xqiYPWLOI9Y+G9bUlqCXdrL/f2hRHuSd3uoubhxfHLkosDBMKbETSnLm3bARTmaYrhTK QLBH/46QGz4InTn4ZANZ60+EpQH7fnL8wgwVwzDVwYjSUmVdKVyxbOW76kSINSFyGLed /F+KwAXTPNDbuPYCvhbBehSwxtAYyb/7cMYYmbClElSp5mT1ZwbIFAZOP6O6K3w2yUic 08LhaeuAE6k9mhL09gYxWiGkvI6kHNZss4IBAuPmMGpc5dstrLj4FqzfliQtiWJOOCjP Fw2w== X-Gm-Message-State: AOAM53283k2O6FhdjRSADlpGpOhFYZd1LBolmIsYmC2wTU3e+OrCFsvl Rfz38YWrh2guVWoqpkA0FJyZD0NNOzdE0pZEA1I= X-Google-Smtp-Source: ABdhPJyvcoN3VZJiOMGjg2lwFQxiTh0GZqxnwyYeWWJEo57uwjR2tOprRn55I+r4CP6haohCdvdZ0zrxgbGFPPXUu6A= X-Received: by 2002:a9d:58c6:: with SMTP id s6mr11262727oth.67.1605810020538; Thu, 19 Nov 2020 10:20:20 -0800 (PST) MIME-Version: 1.0 References: <5FB6AB35.5060101@anastigmatix.net> In-Reply-To: <5FB6AB35.5060101@anastigmatix.net> From: Erik Mata Date: Thu, 19 Nov 2020 19:20:09 +0100 Message-ID: Subject: Re: Fail to create distributed table in Citus To: Chapman Flack Cc: pljava-dev@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d9de7f05b479c7ff" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000d9de7f05b479c7ff Content-Type: text/plain; charset="UTF-8" Hello Chapman! Thanks for your prompt reply. I posted the same question in the official citus forum, and am waiting for a response. I'm pasting the code of my PL/JAVA routine here: @Function( effects = VOLATILE, onNullInput = CALLED, name = "test_create_distributed_table" ) public static void testCreateDistributedTable() throws SQLException { Connection connection = DriverManager.getConnection("jdbc:default:connection"); Statement stmt = connection.createStatement(); stmt.executeQuery("CREATE TABLE test1 (a BIGINT PRIMARY KEY)"); stmt.executeQuery("SELECT create_distributed_table('test1', 'a', colocate_with => 'none')"); } As you mentioned, it is very simple and a slimmed down version of my original production code. I didn't notice the problem immediately, since (as you mentioned) there is no error but silently seems to fail. I was wondering if it could be something with the single quotes surrounding the parameters, but I've tried to use double single quotes and also to escape the single quotes, but then it really fails with an error. Looking through PL/JAVA implementation of SPIDatabaseMetaData I saw that it adds a single quote to every single quote, unless it is escaped. Don't know if that affects somehow. But in any case, it should lead to an error in Citus if the parameters are incorrect. Well, I'll continue looking for the cause of the problem. I'm really grateful for any additional pointers that you could give me. Regards, Erik Den tors 19 nov. 2020 kl 18:28 skrev Chapman Flack : > 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 > --000000000000d9de7f05b479c7ff Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Chapman!

Thanks for your prompt r= eply. I posted the same question in the official citus forum, and am waitin= g for a response.

I'm pasting the code of my P= L/JAVA routine here:

@Function<= span style=3D"color:rgb(8,8,8);font-family:"JetBrains Mono",monos= pace">(
        effects =3D VOLATILE,
        onNullInput=
 =3D CALLED,=

        name =3D "test_create_=
distributed_table"
)
=
public static void testCreateDis=
tributedTable() throws SQLException {
    Connection connection =3D DriverManager.getConnecti=
on("jdbc:default:connection=
");

    Statement stmt =
=3D connection.createStateme=
nt();
    stmt.executeQuery("CREATE TABLE test1 (a BIGINT PRIMARY KEY)"<=
/span>);
    stmt.executeQuery(<=
span style=3D"color:rgb(6,125,23)">"SELECT create_distributed_table(&#=
39;test1', 'a', colocate_with =3D> 'none')");
}

As you mentioned, it is very simp= le and a slimmed down version of my original production code. I didn't = notice the problem immediately, since (as you mentioned) there is no error = but silently seems to fail.

I was wondering if it = could be something with the single quotes surrounding the parameters, but I= 've tried to use double single quotes and also to escape the single quo= tes, but then it really fails with an error. Looking through PL/JAVA implem= entation of SPIDatabaseMetaData I saw that it adds a single quote to every = single quote, unless it is escaped. Don't know if that affects somehow.= But in any case, it should lead to an error in Citus if the parameters are= incorrect.

Well, I'll continue looking for th= e cause of the problem. I'm really grateful for any additional pointers= that you could give me.

Regards,
Erik


Den tors 19 nov. 2020 kl 18:28 skrev Chapman Flack <<= a href=3D"mailto:chap@anastigmatix.net">chap@anastigmatix.net>:
<= /div>
On 11/19/20 10:23, E= rik 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 t= ell
> me that the table is NOT distributed.
>
> If I use the same statements that I have included in the PL/JAVA routi= ne
> (see above), but I execute them from within e.g. DataGrip, connected t= o the
> Citus master node, the table is created and distributed correctly.

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

> The problem, as far as I can tell, should be somewhere in how PL/JAVA<= br> > connects to the Citus master node. The PL/JAVA routine runs in a JVM t= hat
> is spawned by the Postgres server process on the Citus master node. Th= e
> PL/JAVA routine acquires a connection to the local database via a some= what
> 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<= br> 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) {
=C2=A0 =C2=A0 ...
=C2=A0 =C2=A0 SPI_execute("CREATE TABLE test1 (a BIGINT PRIMARY KEY)&q= uot;, false, 0);
=C2=A0 =C2=A0 SPI_execute("SELECT create_distributed_table("
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "'test1', 'a', colocate_wi= th=3D>'none')", false, 0);
=C2=A0 =C2=A0 ...
}

(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/distribute= d/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
--000000000000d9de7f05b479c7ff--