pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: CREATE DATABASE cannot be executed within a pipeline
2+ messages / 2 participants
[nested] [flat]
* ERROR: CREATE DATABASE cannot be executed within a pipeline
@ 2023-05-20 02:06 396934406 <396934406@qq.com>
0 siblings, 1 reply; 2+ messages in thread
From: 396934406 @ 2023-05-20 02:06 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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。
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ERROR: CREATE DATABASE cannot be executed within a pipeline
@ 2023-05-20 02:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: 396934406 <396934406@qq.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-05-20 02:17 UTC (permalink / raw)
To: 396934406 <396934406@qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
"=?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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-05-20 02:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-20 02:06 ERROR: CREATE DATABASE cannot be executed within a pipeline 396934406 <396934406@qq.com>
2023-05-20 02:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox