Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

[Enrico Schenone <eschenone@xxxxxxxxxxxx>]

<<< text/html; charset=UTF-8: Unrecognized >>>

#include <stdio.h>
#include <stdlib.h>
#include </usr/include/postgresql/libpq-fe.h>
#include <sys/time.h>
#include <time.h>

// This function print the timestamp
void print_timestamp(FILE *stream) {
    struct timeval tv;
    gettimeofday(&tv, NULL);

    time_t now = tv.tv_sec;
    struct tm *timeinfo = localtime(&now);

    char buffer[64];
    strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", timeinfo);

    fprintf(stream, "[%s.%03ld] ", buffer, tv.tv_usec / 1000);
}

// Error handler
void handle_error(PGconn *conn, PGresult *res, const char *context) {
    print_timestamp(stderr);
    const char *sqlstate = res ? PQresultErrorField(res, PG_DIAG_SQLSTATE) : NULL;
    fprintf(stderr, "ERROR in %s: %s", context, PQerrorMessage(conn));
    if (sqlstate) {
        fprintf(stderr, " (SQLSTATE: %s)", sqlstate);
    }
    fprintf(stderr, "\n");

    if (res) PQclear(res);
    PQfinish(conn);
    exit(EXIT_FAILURE);
}

int main() {
    print_timestamp(stdout);
    printf("Start program C-testCursors\n");
    const char *conninfo = "host=192.168.20.94 dbname=hh24odds_prod user=my_user password=my_password";
    PGconn *conn = PQconnectdb(conninfo);

    if (PQstatus(conn) != CONNECTION_OK) {
        print_timestamp(stderr);
        fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
        PQfinish(conn);
        return EXIT_FAILURE;
    }

    print_timestamp(stdout);
    printf("Connected to database.\n");

    // Start the transaction
    PGresult *res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "BEGIN");
    }
    PQclear(res);

    // Creating temporary table
    res = PQexec(conn, "CREATE TEMP TABLE IF NOT EXISTS tt1 (like template_table)");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "CREATE TEMP TABLE");
    }
    PQclear(res);
    print_timestamp(stdout);
    printf("Temp table created.\n");

    // Caricamento della temp table
    res = PQexec(conn, "INSERT INTO tt1 SELECT * FROM test_baseline_table order by nelab, bkmkc_id, sport_id, pal_c_id, bt_id, oc_id, mp_id, spread");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "INSERT INTO TEMP TABLE");
    }
    PQclear(res);
    print_timestamp(stdout);
    printf("Temp table loaded.\n");

    // Declaring parent cursor on temporary table
    res = PQexec(conn, "DECLARE parent_cursor CURSOR FOR SELECT bt_id, evento_id FROM tt1");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "DECLARE parent_cursor");
    }
    PQclear(res);

    // Declaring child cursor out-of-parent loop
    // Just as POC
    res = PQexec(conn, "DECLARE child_cursor CURSOR FOR SELECT descri FROM lookup_table");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "DECLARE child_cursor");
    }
    PQclear(res);

    // Parent cursor loop
    while (1) {
        res = PQexec(conn, "FETCH NEXT FROM parent_cursor");
        if (PQresultStatus(res) != PGRES_TUPLES_OK) {
            handle_error(conn, res, "FETCH parent_cursor");
        }

        int nrows = PQntuples(res);
        if (nrows == 0) {
            PQclear(res);
            break;
        }

        for (int i = 0; i < nrows; i++) {
            char *parent_id = PQgetvalue(res, i, 0);
            char *parent_name = PQgetvalue(res, i, 1);

            // Child cursor whith parametric query
            char query[256];
            snprintf(query, sizeof(query), "CLOSE child_cursor; DECLARE child_cursor CURSOR FOR SELECT descri FROM lookup_table WHERE id = %s", parent_id);

            PGresult *child_res = PQexec(conn, query);
            if (PQresultStatus(child_res) != PGRES_COMMAND_OK) {
                handle_error(conn, child_res, "OPEN child_cursor");
            }
            PQclear(child_res);

            // Child cursor loop
            while (1) {
                child_res = PQexec(conn, "FETCH NEXT FROM child_cursor");
                if (PQresultStatus(child_res) != PGRES_TUPLES_OK) {
                    handle_error(conn, child_res, "FETCH child_cursor");
                }

                int child_nrows = PQntuples(child_res);
                if (child_nrows == 0) {
                    PQclear(child_res);
                    break;
                }

                for (int j = 0; j < child_nrows; j++) {
                    char *detail = PQgetvalue(child_res, j, 0);
                    print_timestamp(stdout);
                    printf("Child row: %s\n", detail);
                }

                PQclear(child_res);
            }
        }

        PQclear(res);
    }

    print_timestamp(stdout);
    printf("End of cursors process.\n");
    // Chiudi il cursore padre
    res = PQexec(conn, "CLOSE parent_cursor");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "CLOSE parent_cursor");
    }
    PQclear(res);

    // Note: the last child cursor was intentionally leaved open

    // Closing transaction
    res = PQexec(conn, "END");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        handle_error(conn, res, "END");
    }
    PQclear(res);

    PQfinish(conn);
    print_timestamp(stdout);
    printf("End program C-testCursors2\n");
    return EXIT_SUCCESS;
}

Attachment: C-testCursors.sh
Description: application/shellscript