• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Blog

      • Home
      • Blog
      • Blog
      • LIBPQ   -Library Pooled Quota

      LIBPQ   -Library Pooled Quota

      • Posted by Nijamutheen J
      • Categories Blog
      • Date January 16, 2019
      • Comments 0 comment
      LIBPQ   -Library Pooled Quota
      •  Details About Connected Users Using tools
      •  libpq is the C application programmer’s interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
      • Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library
      • Here are also several complete examples of libpq applications in the directory src/test/examples in the source code distribution.
      libpq Example Program 1

      /*
       * testlibpq.c
       *
       *      Test the C version of libpq, the PostgreSQL frontend library.
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <libpq-fe.h>
      
      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 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, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * 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);
          }
      
          /*
           * Should PQclear PGresult whenever it is no longer needed to avoid memory
           * leaks
           */
          PQclear(res);
      
          /*
           * Fetch rows from pg_database, the system catalog of databases
           */
          res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
          if (PQresultStatus(res) != PGRES_COMMAND_OK)
          {
              fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
          PQclear(res);
      
          res = PQexec(conn, "FETCH ALL in myportal");
          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 */
          nFields = PQnfields(res);
          for (i = 0; i < nFields; i++)
              printf("%-15s", PQfname(res, i));
          printf("\n\n");
      
          /* next, print out the rows */
          for (i = 0; i < PQntuples(res); i++)
          {
              for (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 myportal");
          PQclear(res);
      
          /* end the transaction */
          res = PQexec(conn, "END");
          PQclear(res);
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }
       libpq Example Program 2

      /*
       * testlibpq2.c
       *      Test of the asynchronous notification interface
       *
       * Start this program, then from psql in another window do
       *   NOTIFY TBL2;
       * Repeat four times to get this program to exit.
       *
       * Or, if you want to get fancy, try this:
       * populate a database with the following commands
       * (provided in src/test/examples/testlibpq2.sql):
       *
       *   CREATE TABLE TBL1 (i int4);
       *
       *   CREATE TABLE TBL2 (i int4);
       *
       *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
       *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
       *
       * and do this four times:
       *
       *   INSERT INTO TBL1 VALUES (10);
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <string.h>
      #include <errno.h>
      #include <sys/time.h>
      #include <libpq-fe.h>
      
      static void
      exit_nicely(PGconn *conn)
      {
          PQfinish(conn);
          exit(1);
      }
      
      int
      main(int argc, char **argv)
      {
          const char *conninfo;
          PGconn     *conn;
          PGresult   *res;
          PGnotify   *notify;
          int         nnotifies;
      
          /*
           * 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, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * Issue LISTEN command to enable notifications from the rule's NOTIFY.
           */
          res = PQexec(conn, "LISTEN TBL2");
          if (PQresultStatus(res) != PGRES_COMMAND_OK)
          {
              fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          /*
           * should PQclear PGresult whenever it is no longer needed to avoid memory
           * leaks
           */
          PQclear(res);
      
          /* Quit after four notifies are received. */
          nnotifies = 0;
          while (nnotifies < 4)
          {
              /*
               * Sleep until something happens on the connection.  We use select(2)
               * to wait for input, but you could also use poll() or similar
               * facilities.
               */
              int         sock;
              fd_set      input_mask;
      
              sock = PQsocket(conn);
      
              if (sock < 0)
                  break;              /* shouldn't happen */
      
              FD_ZERO(&input_mask);
              FD_SET(sock, &input_mask);
      
              if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
              {
                  fprintf(stderr, "select() failed: %s\n", strerror(errno));
                  exit_nicely(conn);
              }
      
              /* Now check for input */
              PQconsumeInput(conn);
              while ((notify = PQnotifies(conn)) != NULL)
              {
                  fprintf(stderr,
                          "ASYNC NOTIFY of '%s' received from backend PID %d\n",
                          notify->relname, notify->be_pid);
                  PQfreemem(notify);
                  nnotifies++;
              }
          }
      
          fprintf(stderr, "Done.\n");
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }
      libpq Example Program 3

      /*
       * testlibpq3.c
       *      Test out-of-line parameters and binary I/O.
       *
       * Before running this, populate a database with the following commands
       * (provided in src/test/examples/testlibpq3.sql):
       *
       * CREATE TABLE test1 (i int4, t text, b bytea);
       *
       * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
       * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
       *
       * The expected output is:
       *
       * tuple 0: got
       *  i = (4 bytes) 1
       *  t = (11 bytes) 'joe's place'
       *  b = (5 bytes) \000\001\002\003\004
       *
       * tuple 0: got
       *  i = (4 bytes) 2
       *  t = (8 bytes) 'ho there'
       *  b = (5 bytes) \004\003\002\001\000
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <string.h>
      #include <sys/types.h>
      #include <libpq-fe.h>
      
      /* for ntohl/htonl */
      #include <netinet/in.h>
      #include <arpa/inet.h>
      
      
      static void
      exit_nicely(PGconn *conn)
      {
          PQfinish(conn);
          exit(1);
      }
      
      /*
       * This function prints a query result that is a binary-format fetch from
       * a table defined as in the comment above.  We split it out because the
       * main() function uses it twice.
       */
      static void
      show_binary_results(PGresult *res)
      {
          int         i,
                      j;
          int         i_fnum,
                      t_fnum,
                      b_fnum;
      
          /* Use PQfnumber to avoid assumptions about field order in result */
          i_fnum = PQfnumber(res, "i");
          t_fnum = PQfnumber(res, "t");
          b_fnum = PQfnumber(res, "b");
      
          for (i = 0; i < PQntuples(res); i++)
          {
              char       *iptr;
              char       *tptr;
              char       *bptr;
              int         blen;
              int         ival;
      
              /* Get the field values (we ignore possibility they are null!) */
              iptr = PQgetvalue(res, i, i_fnum);
              tptr = PQgetvalue(res, i, t_fnum);
              bptr = PQgetvalue(res, i, b_fnum);
      
              /*
               * The binary representation of INT4 is in network byte order, which
               * we'd better coerce to the local byte order.
               */
              ival = ntohl(*((uint32_t *) iptr));
      
              /*
               * The binary representation of TEXT is, well, text, and since libpq
               * was nice enough to append a zero byte to it, it'll work just fine
               * as a C string.
               *
               * The binary representation of BYTEA is a bunch of bytes, which could
               * include embedded nulls so we have to pay attention to field length.
               */
              blen = PQgetlength(res, i, b_fnum);
      
              printf("tuple %d: got\n", i);
              printf(" i = (%d bytes) %d\n",
                     PQgetlength(res, i, i_fnum), ival);
              printf(" t = (%d bytes) '%s'\n",
                     PQgetlength(res, i, t_fnum), tptr);
              printf(" b = (%d bytes) ", blen);
              for (j = 0; j < blen; j++)
                  printf("\\%03o", bptr[j]);
              printf("\n\n");
          }
      }
      
      int
      main(int argc, char **argv)
      {
          const char *conninfo;
          PGconn     *conn;
          PGresult   *res;
          const char *paramValues[1];
          int         paramLengths[1];
          int         paramFormats[1];
          uint32_t    binaryIntVal;
      
          /*
           * 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, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * The point of this program is to illustrate use of PQexecParams() with
           * out-of-line parameters, as well as binary transmission of data.
           *
           * This first example transmits the parameters as text, but receives the
           * results in binary format.  By using out-of-line parameters we can
           * avoid a lot of tedious mucking about with quoting and escaping, even
           * though the data is text.  Notice how we don't have to do anything
           * special with the quote mark in the parameter value.
           */
      
          /* Here is our out-of-line parameter value */
          paramValues[0] = "joe's place";
      
          res = PQexecParams(conn,
                             "SELECT * FROM test1 WHERE t = $1",
                             1,       /* one param */
                             NULL,    /* let the backend deduce param type */
                             paramValues,
                             NULL,    /* don't need param lengths since text */
                             NULL,    /* default to all text params */
                             1);      /* ask for binary results */
      
          if (PQresultStatus(res) != PGRES_TUPLES_OK)
          {
              fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          show_binary_results(res);
      
          PQclear(res);
      
          /*
           * In this second example we transmit an integer parameter in binary
           * form, and again retrieve the results in binary form.
           *
           * Although we tell PQexecParams we are letting the backend deduce
           * parameter type, we really force the decision by casting the parameter
           * symbol in the query text.  This is a good safety measure when sending
           * binary parameters.
           */
      
          /* Convert integer value "2" to network byte order */
          binaryIntVal = htonl((uint32_t) 2);
      
          /* Set up parameter arrays for PQexecParams */
          paramValues[0] = (char *) &binaryIntVal;
          paramLengths[0] = sizeof(binaryIntVal);
          paramFormats[0] = 1;        /* binary */
      
          res = PQexecParams(conn,
                             "SELECT * FROM test1 WHERE i = $1::int4",
                             1,       /* one param */
                             NULL,    /* let the backend deduce param type */
                             paramValues,
                             paramLengths,
                             paramFormats,
                             1);      /* ask for binary results */
      
          if (PQresultStatus(res) != PGRES_TUPLES_OK)
          {
              fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          show_binary_results(res);
      
          PQclear(res);
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }
      • Share:
      Admin bar avatar
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      Postgresql Caching Memory(About Shared Buffer Access)
      January 16, 2019

      Next post

      How to Tuning Checkpoint Parameters In PostgreSQL
      January 16, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now