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)-----------------------------------------------------------------------------------
The same java code is normal in PostgreSQL server 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。