ERROR: CREATE DATABASE cannot be executed within a pipeline

From: 396934406 <396934406(at)qq(dot)com>
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: ERROR: CREATE DATABASE cannot be executed within a pipeline
Date: 2023-05-20 02:06:43
Message-ID: tencent_BD6B866E25069560051D16A1EEA07A4F2908@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

hello,pg team

When executing DROP DATABASE or CREATE DATABASE statements in JDBC, other statements cannot be included。

This behavior was&nbsp;&nbsp;tested in&nbsp;PostgreSQL server&nbsp;version 11.19、12.14、13.10、14.7、15.2 with&nbsp;jre1.8 and PostgreSQL-42.3.7.jar。

the java code is below:
-----------------------------------------------------------------------------------
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
Properties props = new Properties();
PGProperty.USER.set(props, "postgres");
PGProperty.PASSWORD.set(props, "admin");
Connection connection = DriverManager.getConnection("jdbc:postgresql://ip:port/postgres", props);

//ERROR: DROP DATABASE cannot be executed within a pipeline
String sql_recreate1 = "SELECT 1;DROP DATABASE if exists mydb;";

//ERROR: CREATE DATABASE cannot be executed within a pipeline
String sql_recreate2 = "SELECT 1;CREATE DATABASE mydb;";

PreparedStatement statement = connection.prepareStatement(sql_recreate2);
statement.execute();

// Statement statement = connection.createStatement();
// statement.execute(sql_recreate2);

}
-----------------------------------------------------------------------------------

the whole exception print below:
-----------------------------------------------------------------------------------

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: CREATE DATABASE cannot be executed within a pipeline

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167)

at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156)

at test.jdbc.pg.TestPipeline.main(TestPipeline.java:39)
-----------------------------------------------------------------------------------
&nbsp;
The same java code&nbsp;is normal in&nbsp;PostgreSQL server&nbsp;version 11.18、12.13、13.9、14.6、15.1 。

What may be related to this differential behavior,I notice the release note of 11.19、12.14、13.10、14.7、15.2:
-----------------------------------------------------------------------------------
In extended query protocol, avoid an immediate commit after ANALYZE if we’re running a pipeline (Tom Lane)
If there’s not been an explicit BEGIN TRANSACTION, ANALYZE would take it on itself to commit, which should not happen within a pipelined series of commands.
-----------------------------------------------------------------------------------

hope that helps。

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-05-20 02:17:14 Re: ERROR: CREATE DATABASE cannot be executed within a pipeline
Previous Message David G. Johnston 2023-05-19 20:14:07 Re: Aggregation results with json(b)_agg and array_agg in a SELECT with OUTER JOIN