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

Поиск

Список

Период

Сортировка

ERROR: CREATE DATABASE cannot be executed within a pipeline

От

"396934406"

Дата:

20 мая 2023 г., 02:06:43

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

Дата:

20 мая 2023 г., 02:17:14

"=?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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

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

ERROR: CREATE DATABASE cannot be executed within a pipeline

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