Обсуждение: Can't run CREATE PROCEDURE with new Postgres' 14 new SQL-standard body
Hello, Postgres 14 supports SQL standard conforming bodies for functions and procedures which are no longer string constants, e.g. create or replace procedure do_stuff() language sql begin atomic select 1; select 2; end However, it seems that the JDBC driver will parse the string and split it into multiple statements based on the semicolons thus making it an invalid create procedure statement. The above CREATE statement runs fine in psql, but the following Java code: Connection con = DriverManager.getConnection(...); String sql = "create or replace procedure do_stuff()\n" + " language sql\n" + " begin atomic\n" + " select 1;\n" + " select 2;\n" + " end"; Statement stmt = con.createStatement(); stmt.execute(sql); fails with: org.postgresql.util.PSQLException: ERROR: syntax error at end of input Position: 79 Position 79 is the semicolon after "select 1" Is there a way to disable the parsing/splitting in the driver so that it will be possible to run such statements? This is important for JDBC based SQL client tools. Regards Thomas
On Sun, Oct 3, 2021 at 8:08 AM Thomas Kellerer <shammat@gmx.net> wrote:
Postgres 14 supports SQL standard conforming bodies for functions and procedures
which are no longer string constants, e.g.
create or replace procedure do_stuff()
language sql
begin atomic
select 1;
select 2;
end
However, it seems that the JDBC driver will parse the string and split it into
multiple statements based on the semicolons thus making it an invalid create procedure
statement.
The above CREATE statement runs fine in psql, but the following Java code:
Connection con = DriverManager.getConnection(...);
String sql =
"create or replace procedure do_stuff()\n" +
" language sql\n" +
" begin atomic\n" +
" select 1;\n" +
" select 2;\n" +
" end";
Statement stmt = con.createStatement();
stmt.execute(sql);
fails with:
org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 79
Position 79 is the semicolon after "select 1"
Is there a way to disable the parsing/splitting in the driver so that it will be possible
to run such statements?
If you set the connection property "preferQueryMode" to "simple" or "extendedForPrepared", then the driver will not attempt to split the statement into multiple commands. It will get sent as-is and executed via the simple protocol rather than the parse / bind / execute extended protocol. I tried this out locally and confirmed that I'm able to create a SQL standard body procedure on PG 14.
The connection property applies to the entire life of that connection. I'm not aware of a way to do it selectively for a single command on an already established connection.
I'm also not sure why we do the statement splitting at all. The server supports multiple commands in a single simple protocol statement and rejects multiple commands in the extended protocol. That's something we should look into as well adding more testing to the driver for stored procs and PG 14.
Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
Sehrope Sarkuni schrieb am 04.10.2021 um 14:49: > On Sun, Oct 3, 2021 at 8:08 AM Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote: > > Postgres 14 supports SQL standard conforming bodies for functions and procedures > which are no longer string constants, e.g. > > create or replace procedure do_stuff() > language sql > begin atomic > select 1; > select 2; > end > > However, it seems that the JDBC driver will parse the string and split it into > multiple statements based on the semicolons thus making it an invalid create procedure > statement. > > > If you set the connection property "preferQueryMode" to "simple" or > "extendedForPrepared", then the driver will not attempt to split the > statement into multiple commands. Thanks, that works fine. Changing the connection URL is a good enough solution for me. Thomas
On Mon, 4 Oct 2021 at 11:27, Thomas Kellerer <shammat@gmx.net> wrote:
Sehrope Sarkuni schrieb am 04.10.2021 um 14:49:
> On Sun, Oct 3, 2021 at 8:08 AM Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote:
>
> Postgres 14 supports SQL standard conforming bodies for functions and procedures
> which are no longer string constants, e.g.
>
> create or replace procedure do_stuff()
> language sql
> begin atomic
> select 1;
> select 2;
> end
>
> However, it seems that the JDBC driver will parse the string and split it into
> multiple statements based on the semicolons thus making it an invalid create procedure
> statement.
>
>
> If you set the connection property "preferQueryMode" to "simple" or
> "extendedForPrepared", then the driver will not attempt to split the
> statement into multiple commands.
Thanks, that works fine.
Changing the connection URL is a good enough solution for me.
Thomas
Serhope,
We should probably document this..
Dave Cramer
www.postgres.rocks