Re: Vacuum again and again

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(at)sss(dot)pgh(dot)pa(dot)us>
To: <denis(at)coralindia(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Thursday, September 26, 2002 7:46 PM
Subject: Re: [NOVICE] Vacuum again and again

> denis(at)coralindia(dot)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