Cannot drop and create a table in a single statement using `<libpq-fe.h>`.

[duerwuyi]

Which version of ShardingSphere did you use?

shardingsphere-proxy 5.5.2 postgres 17.2 as backend

Expected behavior

The following C++ code was supposed to execute successfully.

int main(int argc, char *argv[]) {
    auto conn = PQsetdbLogin("host.docker.internal", "5440", NULL, NULL, "testdb", "postgres", "123abc");
    if (PQstatus(conn) != CONNECTION_OK) {
        cerr << "Failed to connect to database" << endl;
        return 1;
    }

    auto res = PQexec(conn, "CREATE BROADCAST TABLE RULE t10;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        cerr << "Failed to create broadcast table rule" << endl;
        return 1;
    }
    
    res = PQexec(conn, "DROP TABLE IF EXISTS t10;"
    "create table t10 (" 
    "vkey int4 ,"
    "pkey int4 ,"
    "c0 numeric ,"
    "c1 timestamp ,"
    "c2 timestamp ,"
    "c3 numeric ,"
    "c4 text ,"
    "c5 text "
    ");"
    );
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        string err = PQerrorMessage(conn);
        cerr << "error: " << err << endl;
        return 1;
    }
    PQfinish(conn);
    return 0;
}

Actual behavior

error: ERROR:  You have an error in your SQL syntax: DROP TABLE IF EXISTS t10;create table t10 (vkey int4 ,pkey int4 ,c0 numeric ,c1 timestamp ,c2 timestamp ,c3 numeric ,c4 text ,c5 text );, null

Reason analyze.

As a comparison, the following code can execute successfully, and the only difference is that “Drop table” statement is separated from "create table" statement.

    auto conn = PQsetdbLogin("host.docker.internal", "5440", NULL, NULL, "testdb", "postgres", "123abc");
    if (PQstatus(conn) != CONNECTION_OK) {
        cerr << "Failed to connect to database" << endl;
        return 1;
    }

    auto res = PQexec(conn, "CREATE BROADCAST TABLE RULE t10;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        cerr << "Failed to create broadcast table rule" << endl;
        return 1;
    }

    res = PQexec(conn, "DROP TABLE IF EXISTS t10;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        cerr << "Failed to drop table" << endl;
        return 1;
    }

    res = PQexec(conn, "create table t10 (" 
    "vkey int4 ,"
    "pkey int4 ,"
    "c0 numeric ,"
    "c1 timestamp ,"
    "c2 timestamp ,"
    "c3 numeric ,"
    "c4 text ,"
    "c5 text "
    ");"
    );
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        string err = PQerrorMessage(conn);
        cerr << "error: " << err << endl;
        return 1;
    }
    PQfinish(conn);
    return 0;

But actually this two statements can be executed together in these situation:

1. using psql

testdb=> CREATE BROADCAST TABLE RULE t0;
SUCCESS
testdb=> DROP TABLE IF EXISTS t0;
create table t0 ( 
vkey int4 ,
pkey int4 ,
c0 numeric ,
c1 timestamp ,
c2 timestamp ,
c3 numeric ,
c4 text ,
c5 text 
);
DROP TABLE
CREATE TABLE

2. The topmost code can also execute successfully when connected to any PostgreSQL.

btw, the error here is very strange. In the end, it appends a ", null," even though this string is not present in the actual input.