Обсуждение: ERROR: CREATE DATABASE cannot be executed within a pipeline

Поиск
Список
Период
Сортировка

ERROR: CREATE DATABASE cannot be executed within a pipeline

От
"396934406"
Дата:
hello,pg team

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

This behavior was  tested in PostgreSQL server version 11.19、12.14、13.10、14.7、15.2 with 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)-----------------------------------------------------------------------------------

 
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。

Re: ERROR: CREATE DATABASE cannot be executed within a pipeline

От
Tom Lane
Дата:
"=?gb18030?B?Mzk2OTM0NDA2?=" <396934406@qq.com> writes:
> When executing DROP DATABASE or CREATE DATABASE statements in JDBC,
> other statements cannot be included

This restriction is intentional.  Previous versions failed to enforce it
fully, but you were at serious hazard of problems, up to and including
database corruption, if you exploited the missing check.

            regards, tom lane