Thread: logging

Hi all,

I am trying to setup logging.  I have updated the postgresql.conf by making
the following changes:

#debug_level = 0 # range 0-16
debug_level = 2 # range 0-16

#debug_print_query = false
debug_print_query = true
...

#       Syslog
#
#ifdef ENABLE_SYSLOG
#syslog = 0 # range 0-2
syslog = 2 # range 0-2

I have also update the postgresql script that we use to start postmaster to
redirect output to a log file rather than /dev/null.  I get the following
in the log file:

postmaster successfully started
invoking IpcMemoryCreate(size=1236992)
FindExec: found "/usr/bin/postmaster" using argv[0]
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling writing 5
/usr/bin/postmaster: BackendStartup: pid 30039 user postgres db dioh socket
5
/usr/bin/postmaster child[30039]: starting with (postgres -d2 -v131072 -p
dioh )
FindExec: found "/usr/bin/postgres" using argv[0]
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: CleanupProc: pid 30039 exited with status 0
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling writing 5
/usr/bin/postmaster: BackendStartup: pid 30054 user postgres db
counterpoint soc
ket 5
/usr/bin/postmaster child[30054]: starting with (postgres -d2 -v131072 -p
counte
rpoint )
....

I want the log file to show activity that occurs when queries are executed.
But this seems to be only showing output when I open a psql session or log
out of it.  Could you please kindly let me know what I am missing.  Thank
you.

p.s. The whole reason for turning on logging is that we are processing
sales data from about 100 locations to generate inventory numbers.  The
program runs fine for all but 1 store.  The problem is that postmaster
keeps on running for about 3 days processing something when it only should
take about 30 mintues.  I figured maybe logging will show what postmaster
is doing while it's on its 3 day cruise.


Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474

Hi all,

We have a web application which has 15 tables. Out of these, 7 tables has >
400000 records. The client application automatically connects to Postgres
database (at server) after 1 1/2 hours and performs INCREMENTAL UPLOAD to
server from client.

Prob :
Upload option works fine after VACUUM but after 3-4 days, it starts giving
ERRORs... Again, we have to run VACUUM to solve the prob..

So, now a days, it has become our routine to run VACUUM every day to avoid
ERRORs.. caz, when ERROR shown, it causes bad impression on client.

Is there any parameters setting in DATABASE or anything else we can do
(except VACUUM regularly) ?

Postgres 7.2 on Redhat Linux 7.2


Thanx

Denis

Hi !

We do vacuumall (and dumpall) with a cronjob every night.

BR,

aarni


On Thursday 26 September 2002 08:20 am, you wrote:
> Hi all,
>
> We have a web application which has 15 tables. Out of these, 7 tables has >
> 400000 records. The client application automatically connects to Postgres
> database (at server) after 1 1/2 hours and performs INCREMENTAL UPLOAD to
> server from client.
>
> Prob :
> Upload option works fine after VACUUM but after 3-4 days, it starts giving
> ERRORs... Again, we have to run VACUUM to solve the prob..
>
> So, now a days, it has become our routine to run VACUUM every day to avoid
> ERRORs.. caz, when ERROR shown, it causes bad impression on client.
>
> Is there any parameters setting in DATABASE or anything else we can do
> (except VACUUM regularly) ?
>
> Postgres 7.2 on Redhat Linux 7.2
>
>
> Thanx
>
> Denis
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

denis@coralindia.com writes:
> Upload option works fine after VACUUM but after 3-4 days, it starts giving
> ERRORs... Again, we have to run VACUUM to solve the prob..

We need more detail ... like, what ERRORs exactly?  What do you mean by
"incremental upload" --- is that INSERTs, COPYs, or what?

A daily VACUUM *is* standard procedure, cf
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/maintenance.html

But omitting it shouldn't cause any problems beyond slowdown and disk
space bloat (at least in the short run).  So I'm curious as to what
you're doing.

            regards, tom lane

Actually, we are connecting to Postgres using "PQsetdbLogin" and executing
statements using "PQexec".

Sorry about using word "Incremental Upload".. It is actually DML statements
(Insert, Update and Delete) only..

We are using .O file compiled with cc -c <filename> (the file is appended
below). Then we are using FlagShip compiler for compiling our programme
files with .O file.

When we are running the same on our LOCAL LINUX machine, it works smoothly..
but it is not possible to DEBUG the same at SERVER. The entire programe
behaves very differently like the files which actually exists, it say it
does not exist !! But the same programme, when not linked with .O file,
works fine.. Also, after vacuum, the programme works well (even it is
compiled with .O file)

Note : I am not an expert of C but can understand this C file a little.

Also, Reading the document
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/routine-vacuumin
g.html, it seems that i should use "Truncate Table <tablename>" instead of
"Delete from <tablename>" for deleting all rows of certain tables..

Thanx for replying

Denis

----------------------------------------------------- c file starts from
here -------------

/*
 *  Compile : cc -c SQLkit_PG.c
 */

#include <stdio.h>
#include <FSextend.h>
#include <libpq-fe.h>

static PGconn* conn;
static PGresult* res;
static int IsOpen = 0;

/*************************************************************/
/* OPEN a postgres95 database
 * A UDF to connect to a postgres95 database
 *
 * PARAMETERS : One is required - the name of the database
 *
 */
FSudfname( open_pg)
{
  char *db_name, *pghost, *pgport, *pgoptions, *pgtty, *err_log;
  char *ctime();
  long time(), now;
  FILE *fp;

  FSinit();
  err_log = getenv("PG_ERROR_LOG");

  if (PCOUNT != 1 || _parinfo (1) != CHARACTER)
    {
      time( &now );
      fp = fopen(err_log, "a");
      fprintf(fp, "%s ", getenv("USER") );
      fprintf(fp, "%s", ctime(&now));
      fprintf(fp, "open_pg() requires a parameter\n");
      fclose( fp );
      _retni (-1);
      FSreturn;
    }

  db_name = _parc(1);

  pghost = NULL;
  pgport = NULL;
  pgoptions = NULL;
  pgtty = NULL;

  /* only one open connection allowed */
  if (IsOpen) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "%s\n", "Attempt to open a database twice.");
    fclose( fp );
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  /* make a connect to the database */
  conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty,
db_name,"mydatabaseis","mypasswordis");
  IsOpen = 1;

  /* check that the backend connect was successfully made */
  if (PQstatus(conn) == CONNECTION_BAD) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "Connect to database %s failed.\n", db_name);
    fprintf(fp, "%s\n", PQerrorMessage(conn));
    fclose( fp );
    PQfinish(conn);
    IsOpen = 0;
    _retni(-1);
    FSreturn;
  }

  _retni (0);
  FSreturn;
}

/*************************************************************/
/* CLOSE a postgres95 database */
FSudfname( close_pg)
{
  FSinit();
  PQfinish(conn);
  IsOpen = 0;
  _retni (0);
  FSreturn;
}

/*************************************************************/
/* RETRIEVE tuples from a postgres95 database
 *
 *   Three parameters required.
 *
 *      select_statement
 *
 *      pointer to temp_file_name
 *
 *      pointer to number_of_fields
 *
 *   Return value : number_of_tuples
 *
 */
FSudfname( ret_pg )
{
  char *sel_statement, *retrieve_dir, *err_log, *getenv();
  char cmd[3000];
  char tfilename[80], *heapPtr;
  int nFields, nTuples, i, j, pid;
  static int RelCount = 0;
  FILE *fp, *tfp;
  char *ctime();
  long time(), now;

  FSinit();

  err_log = getenv("PG_ERROR_LOG");
  if (PCOUNT != 3 || _parinfo(1) != CHARACTER)
    {
      time( &now );
      fp = fopen(err_log, "a");
      fprintf(fp, "%s ", getenv("USER") );
      fprintf(fp, "%s ", ctime(&now));
      fprintf(fp, "ret_pg() requires three parameters\n");
      fclose( fp );
      _retni(-1);
      FSreturn;
    }

  sel_statement = _parc(1);

  /* start a transaction block */
  res = PQexec(conn, "BEGIN");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "BEGIN command failed in ret_pg()\n");
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  PQclear(res);

  /* make the exec/select statement */
  strcpy(cmd, "DECLARE myportal CURSOR FOR ");
  strcat(cmd, sel_statement);

  /* fetch tuples */
  res = PQexec(conn, cmd);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "DECLARE CURSOR command failed in ret_pg()\n");
    fprintf(fp, "%s\n", cmd);
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  PQclear(res);

  /* fetch and print */
  res = PQexec(conn, "FETCH ALL in myportal");
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "FETCH ALL command failed in ret_pg()\n");
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  nFields = PQnfields(res);
  nTuples = PQntuples(res);

  /* store number of fields in third parameter */
  _storni( (long)nFields, 3 );

  if (nTuples > 0) {

     /* make temp retrieved-records file */
     retrieve_dir = getenv("PG_RETRIEVE_DIR");
     pid = getpid();

     sprintf( tfilename, "%s%s%d%c%d", retrieve_dir, "pgselect", ++RelCount,
'.', pid);
     heapPtr = _xgrab (_parclen(2) + strlen(tfilename) + 1);
     strcpy (heapPtr, _parc(2));
     strcat (heapPtr, tfilename);
     _storc (heapPtr, 2);
     _xfree (heapPtr);

     tfp = fopen( tfilename, "w" );
     if (tfp == NULL)
       {
         time( &now );
         fp = fopen(err_log, "a");
         fprintf(fp, "%s ", getenv("USER") );
         fprintf(fp, "%s ", ctime(&now));
         fprintf(fp, "unable to open tmp file in ret_pg()\n");
         fclose( fp );
         _retni(-1);
         FSreturn;
       }

     for (i=0; i < nTuples; i++) {
       for (j=0  ; j < nFields; j++) {
         /*      fprintf(fp, "%-15s|", PQgetvalue(res,i,j)); */
         fprintf(tfp, "%s|", PQgetvalue(res,i,j));
       }
       fprintf(tfp, "\n");
     }
     fclose( tfp );
     /*  unlink( tfilename ); */
  }

  PQclear(res);

  /* close the portal */
  res = PQexec(conn, "CLOSE myportal");
  PQclear(res);

  /* end the transaction */
  res = PQexec(conn, "END");
  PQclear(res);

  _retni( nTuples );   /* return number of records retrieved */
  FSreturn;
}

/*************************************************************/
/* BEGIN a postgres95 transaction block
 *
 * PARAMETERS : none
 *
 */
FSudfname( begin_pg)
{
  char *ctime(), *err_log;
  long time(), now;
  FILE *fp;

  /* start a transaction block */
  res = PQexec(conn, "BEGIN");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "BEGIN command failed in begin_pg()\n");
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  PQclear(res);
  _retni(0);
  FSreturn;
}

/*************************************************************/
/* END a postgres95 transaction block
 *
 * same as COMMIT
 *
 * PARAMETERS : none
 *
 */
FSudfname( end_pg )
{
  char *ctime(), *err_log;
  long time(), now;
  FILE *fp;

  FSinit();
  err_log = getenv("PG_ERROR_LOG");

  /* end the transaction */
  res = PQexec(conn, "END");
  PQclear(res);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "END command failed in end_pg()\n");
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  _retni( 0 );
  FSreturn;
}

/*************************************************************/
/* ROLLBACK a postgres95 transaction block
 *
 * same as ABORT
 *
 * PARAMETERS : none
 *
 */
FSudfname( rback_pg )
{
  char *ctime(), *err_log;
  long time(), now;
  FILE *fp;

  FSinit();
  err_log = getenv("PG_ERROR_LOG");

  /* end the transaction */
  res = PQexec(conn, "ROLLBACK");
  PQclear(res);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "ROLLBACK command failed\n");
    fclose( fp );
    PQclear(res);
    PQfinish(conn);
    _retni(-1);
    FSreturn;
  }

  _retni( 0 );
  FSreturn;
}

/*************************************************************/
/* EXEC a postgres95 SQL command
 *
 * may be used for all SQL commands execept SELECT
 *
 * PARAMETERS : one required : the SQL string
 *
 */
FSudfname( exec_pg )
{
  char *cmd;
  char *ctime(), *err_log;
  long time(), now;
  FILE *fp;

  FSinit();
  err_log = getenv("PG_ERROR_LOG");

  if (PCOUNT != 1 || _parinfo (1) != CHARACTER)
    {
      time( &now );
      fp = fopen(err_log, "a");
      fprintf(fp, "%s ", getenv("USER") );
      fprintf(fp, "%s", ctime(&now));
      fprintf(fp, "exec_pg() requires a parameter\n");
      fclose( fp );
      _retni (-1);
      FSreturn;
    }

  cmd = _parc(1);
  res = PQexec(conn, cmd);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    time( &now );
    fp = fopen(err_log, "a");
    fprintf(fp, "%s ", getenv("USER") );
    fprintf(fp, "%s ", ctime(&now));
    fprintf(fp, "exec_pg() failed\n");
    fprintf(fp, "Bad command: %s\n", cmd);
    fclose( fp );
    PQclear(res);
    /* PQfinish(conn);     by Denis  */
    _retni(-1);
    FSreturn;
  }

  PQclear(res);
  _retni (0);
  FSreturn;
}

/* eof */







----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <denis@coralindia.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Thursday, September 26, 2002 7:46 PM
Subject: Re: [NOVICE] Vacuum again and again


> denis@coralindia.com writes:
> > Upload option works fine after VACUUM but after 3-4 days, it starts
giving
> > ERRORs... Again, we have to run VACUUM to solve the prob..
>
> We need more detail ... like, what ERRORs exactly?  What do you mean by
> "incremental upload" --- is that INSERTs, COPYs, or what?
>
> A daily VACUUM *is* standard procedure, cf
>
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/maintenance.html
>
> But omitting it shouldn't cause any problems beyond slowdown and disk
> space bloat (at least in the short run).  So I'm curious as to what
> you're doing.
>
> regards, tom lane