Skip site navigation (1) Skip section navigation (2)

BUG #3548: When quickly switching between databases the server lags behind

From: "Daniel Heyder" <Daniel(dot)Heyder(at)comsoft(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3548: When quickly switching between databases the server lags behind
Date: 2007-08-17 14:52:43
Message-ID: 200708171452.l7HEqhit046412@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3548
Logged by:          Daniel Heyder
Email address:      Daniel(dot)Heyder(at)comsoft(dot)de
PostgreSQL version: 8.2.4
Operating system:   Linux (Red Hat EL4 Update 4 + PostgreSQL 8.2.4 update +
compat libraries)
Description:        When quickly switching between databases the server lags
behind
Details: 

Hi,

when I do quick PQconnectdb give the connection something to do PQfinish the
connection and PQconnectdb to another database the database server does not
keep up, neither does PQconnectdb or PQfinish block until the work is
complete. This is annoying when I want to delete the still working database.
(Causes an error as it is still in use.)

Here some code which demonstrates the problem (make sure it is the only
process accessing the database):

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

int main()
{
 char *pq_db;
 char *tb_db;
 PGconn *conn;
 PGresult *res;
 
 for (int x = 0; x < 2000; x++)
 { 
  conn = PQconnectdb("host=127.0.0.1 dbname=postgres port=5432");
 
  pq_db = PQdb(conn);
  res = PQexec(conn, "SELECT * FROM pg_catalog.pg_stat_activity ORDER BY
usename, procpid;");
  tb_db = PQgetvalue(res, 0, 1);
  if (strcmp(pq_db, tb_db) != 0)
  {
   fprintf(stderr, "********* ERROR WRONG DATABASE OPEN (pq=%s, tb=%s)
**********\n", pq_db, tb_db);
   return 1;
  }
  PQclear(res);

  PQexec(conn, "CREATE TABLE x1 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x2 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x3 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x4 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x5 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x6 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x7 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x8 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");
  PQexec(conn, "CREATE TABLE x9 (x integer PRIMARY KEY, y integer,z
timestamp without time zone,u timestamp without time zone);");

  PQexec(conn, "DROP TABLE x1");
  PQexec(conn, "DROP TABLE x2");
  PQexec(conn, "DROP TABLE x3");
  PQexec(conn, "DROP TABLE x4");
  PQexec(conn, "DROP TABLE x5");
  PQexec(conn, "DROP TABLE x6");
  PQexec(conn, "DROP TABLE x7");
  PQexec(conn, "DROP TABLE x8");
  PQexec(conn, "DROP TABLE x9");

  PQfinish(conn);

  conn = PQconnectdb("host=127.0.0.1 dbname=template1 user=csntool
password=comsoft port=5432");
 
  pq_db = PQdb(conn);
  res = PQexec(conn, "SELECT * FROM pg_catalog.pg_stat_activity ORDER BY
usename, procpid;");
  tb_db = PQgetvalue(res, 0, 1);
  if (strcmp(pq_db, tb_db) != 0)
  {
   fprintf(stderr, "********* ERROR WRONG DATABASE OPEN (pq=%s, tb=%s)
**********\n", pq_db, tb_db);
   return 1;
  }
  PQclear(res);

  PQfinish(conn);
 }
 
 return 0;
}

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-08-17 15:04:43
Subject: Re: error while starting database
Previous:From: Douglas ToltzmanDate: 2007-08-17 14:48:42
Subject: Re: error while starting database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group