Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYEWy-00175D-NB for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 23:41:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qYEWw-00Bzvg-Do for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 23:41:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYEWv-00BzvY-Tt for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 23:41:53 +0000 Received: from mail-qv1-xf29.google.com ([2607:f8b0:4864:20::f29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYEWo-000Eit-FZ for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 23:41:52 +0000 Received: by mail-qv1-xf29.google.com with SMTP id 6a1803df08f44-64b3504144cso23186536d6.2 for ; Mon, 21 Aug 2023 16:41:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692661305; x=1693266105; h=in-reply-to:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:from:to:cc:subject:date :message-id:reply-to; bh=vB0gvRhdf2UHDyg4Lvnzko2CR2zUdLrf/UwJFnvbvto=; b=O//vyyWZTB7mNoQdZwHdpDVZfqt4Gjf8NxXEM2dgxMxB3Ie40jhGRpNlw7aQfioAwq vDMn/0MRebI9AhBtUX8OJFBxJ8+bGJ76qFD03cOoQRBSN2y3tqvKklw2ALOn6dCkFzz/ AXvZUoGnCjmONH7IA/L9Z192MlIargahSEc+AhPjgFbC7a3onetxlCIC3Bi8NBmq72Yj qBGGAMLpt1SiCiyJFgv20iAS+befOeKKy0/1QmLFf2HEb3iJvcWCV5LMi+cH/Sps2Z7d YClr/n/7vGCT9Hu5bz5b21Zp8Lora0098B1TcJhU1EnN2zqy2ZTV1x/tcKtqgMQTs13u 7loQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692661305; x=1693266105; h=in-reply-to:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=vB0gvRhdf2UHDyg4Lvnzko2CR2zUdLrf/UwJFnvbvto=; b=a3bH9oA1Qw9JFhbHe6UX+cuKm0XCeQ09WjPzHGZk1mxgbuVUjzZkie9XmP2GmtwSWI 4nDgeWJiI4FQkS/dJlpkdJpTes16WpQPIQQfN9zQ4gCJLYlANorcv8+elZqgZikYz+v6 OQdGiw0Egsxa1VYkKNfPXwxYSFvxrLg62BabA4cmyjFvthSRf3okBP7eRFL2QK1AW125 wY0nf7Yg7nLcRBMCHyQx16cuED0ZmEIuDaAEYIqexnWSnBDwZFXi+RHO1YKJvpT6nQLE bneZrf06Nhs/e9NgfxctRDqjM2MXgXZCI6IGfAjnUakm4yI9low5TiR4jVziJefvLDQb 3LCw== X-Gm-Message-State: AOJu0Yx86UIJgsMWlYT9ukfvcoOEfRWPJCRgJEsKUUjEU/DDxYjCYBCd CZKR9XxBquwr15lVIL3mMokFAzw9OfU= X-Google-Smtp-Source: AGHT+IG7BpKRwZysN4CZuk/zqOBWW3+4J8MRFsWZLKI4VnsZsroY3eB9iDpLgwYnnHeukplwLDvMcw== X-Received: by 2002:a0c:f2c8:0:b0:63d:1573:c29b with SMTP id c8-20020a0cf2c8000000b0063d1573c29bmr7428470qvm.54.1692661304618; Mon, 21 Aug 2023 16:41:44 -0700 (PDT) Received: from [192.168.2.170] ([67.70.150.114]) by smtp.gmail.com with ESMTPSA id e8-20020a0ce3c8000000b0064805ab33e0sm3235389qvl.67.2023.08.21.16.41.43 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 21 Aug 2023 16:41:44 -0700 (PDT) From: Amn Ojee Uw X-Google-Original-From: Amn Ojee Uw Content-Type: multipart/alternative; boundary="------------RZAaTc02nMdD9rdw0KWo9w67" Message-ID: Date: Mon, 21 Aug 2023 19:41:34 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 Subject: Re: JDBC + PG-15 but not psql Content-Language: en-CA, es-ES To: pgsql-general@lists.postgresql.org References: In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------RZAaTc02nMdD9rdw0KWo9w67 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Thanks Dave for your interest. In  the OP I started by explaining the difficulties when trying to create a tabelspace using JDBC; please note that when using the PG-15 prompt I did not have the problem. Well, it turns out that while developing the request for help (writing the email), I realize that my code had the following line : *this.conn.setAutoCommit(false);*/* */However, in my research, I had read a post mentioning that by setting /auto commit/ to *true* the issue was solved. So, I changed that line of code in my algorithm to : *this.conn.setAutoCommit(true);*/* */Which also resulted in my app working without any errors./**/ Please read the code-snip I submitted; there you can see that line of code I edited; which I commented with "/*//*//*game changer!!".*/ Thanks again Dave, have great week. /* */ On 8/21/23 2:51 p.m., Dave Cramer wrote: > Hi Amn, > > Can you help me understand this issue better? > > I don't see anywhere in the code where you are attempting to create a > tablespace ? > > > Dave Cramer > > > > On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw wrote: > > Hello. > > I have searched the net in an attempt to find if others have had > and resolved this challenge, but most of the sites talk about how, > when using the psql, this error arises. In my case, the error > arises only when access PG-15 using JDBC. > JDBC connects to the database, but when trying to execute a > schema, it be to create a database or to create a tabelspace I get > this error : > > *StackTrace : [Ljava.lang.StackTraceElement;@7a69b07** > **Message : ERROR: CREATE TABLESPACE cannot run inside a > transaction block* > > I have used the same algorithm, but now modify to accommodate > PG-15, when using SQLite. So the JDBC code might not be the > problem, but the requirements needed from PG-15. > > Change of perspective, I resolved the concern! > > Here is what changed the outcome : > --- snip --- > > public void connectToDatabase() throws ClassNotFoundException, > SQLException { >     try { >         Class.forName(this.getDatabaseClass().toString()); >         this.conn = > DriverManager.getConnection(this.getDatabaseUrl().toString(), >                 this.getUserID().toString(), >                 this.getUserPassword().toString()); > > *this.conn.setAutoCommit(true);*/*//*//*game changer!!*/ > >         this.pout("Connected to the PostgreSQL server, success!!!"); > >         this.stmt = this.conn.createStatement(); >     } catch (final SQLException | ClassNotFoundException e) { >         throw e; >     } > } > > -- > > All I had to do was to setAutoCommit to true, PG-15 set this value > to false by default. > > > I hope my experience can help others. > > --------------RZAaTc02nMdD9rdw0KWo9w67 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Thanks Dave for your interest.

In  the OP I started by explaining the difficulties when trying to create a tabelspace using JDBC; please note that when using the PG-15 prompt I did not have the problem. Well, it turns out that while developing the request for help (writing the email), I realize that my code had the following line :
this.conn.setAutoCommit(false);
However, in my research, I had read a post mentioning that by setting auto commit to true the issue was solved. So, I changed that line of code in my algorithm to :
this.conn.setAutoCommit(true);
Which also resulted in my app working without any errors.

Please read the code-snip I submitted; there you can see that line of code I edited; which I commented with "// game changer!!".

Thanks again Dave, have great week.


On 8/21/23 2:51 p.m., Dave Cramer wrote:
Hi Amn,

Can you help me understand this issue better?

I don't see anywhere in the code where you are attempting to create a tablespace ?


Dave Cramer



On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw <amnojeeuw@gmail.com> wrote:

Hello.

I have searched the net in an attempt to find if others have had and resolved this challenge, but most of the sites talk about how, when using the psql, this error arises. In my case, the error arises only when access PG-15 using JDBC.
JDBC connects to the database, but when trying to execute a schema, it be to create a database or to create a tabelspace I get this error :

StackTrace : [Ljava.lang.StackTraceElement;@7a69b07
Message : ERROR: CREATE TABLESPACE cannot run inside a transaction block

I have used the same algorithm, but now modify to accommodate PG-15, when using SQLite. So the JDBC code might not be the problem, but the requirements needed from PG-15.

Change of perspective, I resolved the concern!

Here is what changed the outcome :
--- snip ---

public void connectToDatabase() throws ClassNotFoundException, SQLException {
    try {
        Class.forName(this.getDatabaseClass().toString());
        this.conn = DriverManager.getConnection(this.getDatabaseUrl().toString(),
                this.getUserID().toString(),
                this.getUserPassword().toString());
        
        this.conn.setAutoCommit(true); // game changer!!
       

        this.pout("Connected to the PostgreSQL server, success!!!");

        this.stmt = this.conn.createStatement();
    } catch (final SQLException | ClassNotFoundException e) {
        throw e;
    }
}

--

All I had to do was to setAutoCommit to true, PG-15 set this value to false by default.


I hope my experience can help others.


--------------RZAaTc02nMdD9rdw0KWo9w67--