BUG #17806: PostgreSQL 13.10 returns "CREATE DATABASE cannot be executed within a pipeline"
The following bug has been logged on the website:
Bug reference: 17806
Logged by: Wesley Smith
Email address: wesley.smith@zapproved.com
PostgreSQL version: 13.10
Operating system: Linux
Description:
I am starting up a fresh instance of Postgres inside of Docker using the
"postgres:13.10" tag. I'm then connecting to the database server using the
Ppgsql library for .NET (version 6.0.8) and creating a new database like
this:
var str = "
CREATE ROLE foo WITH PASSWORD 'foo' LOGIN NOINHERIT;
GRANT foo TO postgres;
CREATE DATABASE my_db OWNER foo;
-- More statements here
";
conn = new NpgsqlConnection(connString);
var cmd = new NpgsqlCommand(sql, conn);
await cmd.ExecuteNonQueryAsync();
In all versions of PostgreSQL 13 before 10 (13.1 - 13.9), this works fine.
There is no error and the database is created as expected.
However, with version 13.10, I get this error:
Severity: ERROR
InvariantSeverity: ERROR
SqlState: 25001
MessageText: CREATE DATABASE cannot be executed within a pipeline
File: xact.c
Line: 3412
Routine: PreventInTransactionBlock
As far as I know, I'm not executing these statements in a pipeline or a
transaction.
I don't see anything in the 13.10 release notes that mentions anything about
this. Please let me know whether 13.10 has a bug, or if it is correctly
enforcing something that should have always been enforced.
PG Bug reporting form <noreply@postgresql.org> writes:
I am starting up a fresh instance of Postgres inside of Docker using the
"postgres:13.10" tag. I'm then connecting to the database server using the
Ppgsql library for .NET (version 6.0.8) and creating a new database like
this:
var str = "
CREATE ROLE foo WITH PASSWORD 'foo' LOGIN NOINHERIT;
GRANT foo TO postgres;
CREATE DATABASE my_db OWNER foo;
-- More statements here
";
conn = new NpgsqlConnection(connString);
var cmd = new NpgsqlCommand(sql, conn);
await cmd.ExecuteNonQueryAsync();
You'd have to ask the Ppgsql authors exactly how they transmit that
to the server; but a plausible guess is that it's using what we
refer to as a pipeline, that is a series of commands transmitted
without waiting for the result of earlier commands.
In all versions of PostgreSQL 13 before 10 (13.1 - 13.9), this works fine.
There is no error and the database is created as expected.
However, with version 13.10, I get this error:
Severity: ERROR
InvariantSeverity: ERROR
SqlState: 25001
MessageText: CREATE DATABASE cannot be executed within a pipeline
File: xact.c
Line: 3412
Routine: PreventInTransactionBlock
It is intended that we disallow CREATE DATABASE within a pipeline,
and has been since 13.8 et al; but we fixed some bugs in that
prohibition in 13.10. Looking at the release-note entry about it,
that only mentions ANALYZE, which is probably my own oversight.
The motivation was to prevent semantic anomalies if one command
in the pipeline fails --- previously, you might end up with a
surprising subset of the commands having gotten committed.
regards, tom lane
On Fri, Feb 24, 2023 at 7:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
It is intended that we disallow CREATE DATABASE within a pipeline,
and has been since 13.8 et al; but we fixed some bugs in that
prohibition in 13.10. Looking at the release-note entry about it,
that only mentions ANALYZE, which is probably my own oversight.
Tom,
Thanks for the clarification that this is expected behavior. I'll
update my code to put the "CREATE DATABASE" line in a separate call.
-- Wesley Smith