[C] libpq driver improvements

[lidavidm]

• Handle null values, more types
• Work out some of the error handling details that were left as TODOs (and probably do some refactoring)
• Handle bulk ingestion
• Use COPY FROM STDIN for bulk ingestion instead of a prepared statement (see https://www.postgresql.org/docs/current/populate.html)
• Handle prepared statements
• Add tests for queries that don't return result sets
• c/driver/postgresql: add basic benchmarks #568
• Make sure concurrent statements don't interfere with each other/internal locking
• Set a notice handler
• RuntimeError: Cannot close AdbcConnection with open AdbcStatement #538 integration test with Polars

[WillAyd]

Not sure if this is the right issue to post this in but was doing a little research into libpq today and how we could potentially populate AdbcConnectionGetInfo. Not complete but wanted to share this in case helpful - mostly modified from the first libpq example program but subbing in queries that get us closer to what we need from the catalog:

https://www.postgresql.org/docs/current/libpq-example.html

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <libpq-fe.h>

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

static void
print_table(PGconn *conn, PGresult *res, const char* stmt) {
    /*
     * Fetch rows from pg_database, the system catalog of databases
     */

    const char *decl = "DECLARE mycursor CURSOR FOR ";
    char *curs_stmt = malloc(strlen(decl) + strlen(stmt) + 1);
    sprintf(curs_stmt, "%s%s", decl, stmt);
    res = PQexec(conn, curs_stmt);

    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        free(curs_stmt);        
        exit_nicely(conn);
    }
    free(curs_stmt);    
    PQclear(res);

    res = PQexec(conn, "FETCH ALL IN mycursor");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /* first, print out the attribute names */
    int nFields = PQnfields(res);
    for (int i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (int i = 0; i < PQntuples(res); i++)
    {
        for (int j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE mycursor");
    PQclear(res);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* Set always-secure search path, so malicious users can't take control. */
    res = PQexec(conn,
                 "SELECT pg_catalog.set_config('search_path', '', false)");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * Should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);


    printf("Database information:\n");
    print_table(conn, res, "SELECT oid, datname, datdba "
                "FROM pg_catalog.pg_database d ");

    printf("\n");

    printf("Schema information:\n");
    print_table(conn, res, "SELECT oid, nspname FROM pg_catalog.pg_namespace");

    printf("\n");

    printf("Table, Views, Sequences information:\n");
    print_table(conn, res, "SELECT oid, relname, relnamespace, "
                "CASE relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' "
                "WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' "
                "WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' "
                "WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' "
                "WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' "
                "END as \"Type\" "
                "FROM pg_catalog.pg_class "
                "WHERE relkind IN ('r','p','v','m','S','f','') "
                "ORDER BY 1,2 ");
    printf("\n");

    printf("Columns:\n");
    print_table(conn, res, "SELECT attrelid, "
                "attname, "
                "pg_catalog.format_type(atttypid, atttypmod), "
                "attnotnull, "
                "attidentity, "
                "attgenerated "
                "FROM pg_catalog.pg_attribute ");
    printf("\n");    

    
    /* close the connection to the database and cleanup */
    PQfinish(conn);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

    return 0;
}

[judahrand]

Should there be a separate issue for handling prepared statements? This feels like significant missing functionality for the Postgres driver.

Maybe this issue sort of covers it? #855

Is this something which should be added to the 0.10.0 milestone?

[lidavidm]

Closing this in favor of #855.