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

The following bug has been logged online:

Bug reference:      3548
Logged by:          Daniel Heyder
Email address:      Daniel.Heyder@comsoft.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;
}

"Daniel Heyder" <Daniel.Heyder@comsoft.de> writes:
> 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.

As of 8.3, DROP DATABASE will wait a little bit to see if conflicting
sessions terminate.

            regards, tom lane