agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Christian Barthel <bch@online.de>
To: Sebastien FLAESCH <sf@4js.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: libpq: How are result sets fetched behind the scene?
Date: Sat, 07 Sep 2019 14:43:58 +0200
Message-ID: <87zhjg8eep.fsf@x230.onfire.org> (raw)
In-Reply-To: <7e0c3606-1513-1120-555a-6a14b615d939@4js.com>
References: <6f2f210b-53e6-f350-4850-83f49b2a1354@4js.com>
	<7e0c3606-1513-1120-555a-6a14b615d939@4js.com>

Sebastien FLAESCH <sf@4js.com> writes:

> Is the whole result set fetched to the client app, not matter what row
> number is provided to the first PQgetvalue() call (or similar API call
> on result set data or meta-data)?

I have tested this as well and came to the same result as you.
The entire result set seems to be fetched at once.
Attached is a test program: I have loaded 30MB of random strings
and did a simple SELECT on the random data.  I stopped the output
with getchar() and looked at the network traffic and the memory
usage with top(1).  Everything gets allocated and fetched at once
as far as I can see. 

However, I would not build an application that "depends" on this 
behavior.  I think that it is better to use a declared cursor and
use FETCH.
-- 
Christian Barthel <bch@online.de>

Attachments:

  [text/x-csrc] pg-fetchtest.c (2.1K, ../87zhjg8eep.fsf@x230.onfire.org/2-pg-fetchtest.c)
  download | inline:
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

/* Source Code based on PostgreSQL/libpq Example Code */

/* 
begin; 
create table string_test (s char(32));
BEGIN
CREATE TABLE
insert into string_test(s) SELECT md5(random()::text) from generate_series(1,1000000);
commit;
 */

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

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

    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "host=192.168.4.102 dbname=dbname";

    conn = PQconnectdb(conninfo);

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

    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);
    }

    PQclear(res);

    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);


    res = PQexec(conn, "select s from public.string_test");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "PQexec failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n-----\n");

    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
				getchar();
    }

    PQclear(res);

    res = PQexec(conn, "ABORT");
    PQclear(res);
    PQfinish(conn);
    return 0;
}

view thread (6+ messages)  latest in thread

Message-ID: <87zhjg8eep.fsf@x230.onfire.org>
Permalink:  ../87zhjg8eep.fsf@x230.onfire.org/
Also on:    postgresql.org/message-id/87zhjg8eep.fsf@x230.onfire.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: bch@online.de, sf@4js.com, pgsql-sql@lists.postgresql.org
  Subject: Re: libpq: How are result sets fetched behind the scene?
  In-Reply-To: <87zhjg8eep.fsf@x230.onfire.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox