$Header: /cvsroot/pgsql/contrib/vacuumlo/Attic/README.vacuumlo,v 1.1 2000/06/19 14:02:16 momjian Exp $

$Header: /cvsroot/pgsql/contrib/vacuumlo/Attic/README.vacuumlo,v 1.2 2000/11/21 17:54:21 tgl Exp $

This is a simple utility that will remove any orphaned large objects out of a

PostgreSQL database.

PostgreSQL database. An orphaned LO is considered to be any LO whose OID

does not appear in any OID data column of the database.

Compiling

--------

Simply run make. A single executable "vacuumlo" is created.

Useage

------

Usage

-----

vacuumlo [-v] database [db2 ... dbn]

The -v flag outputs some progress messages to stdout.

Method

------

First, it builds a temporary table which contains all of the oid's of the

large objects in that database.

It then scans through any columns in the database that are of type 'oid', and

removes any entries from the temporary table.

It then scans through all columns in the database that are of type 'oid',

and removes any matching entries from the temporary table.

The remaining entries in the temp table identify orphaned LOs. These are

removed.

I decided to place this in contrib as it needs further testing, but hopefully,

this (or a variant of it) would make it into thebacked as a "vacuum lo" command

in a later release.

this (or a variant of it) would make it into thebackend as a "vacuum lo"

commandin a later release.

Peter Mount <peter@retep.org.uk>

http://www.retep.org.uk

#defineatooid(x) ((Oid) strtoul((x), NULL, 10))

#defineBUFSIZE1024

intvacuumlo(char*,int);

vacuumlo(char*database,intverbose)

PGresult*res,

*res2;

charbuf[BUFSIZE];

intmatched=0;/* Number matched per scan */

intmatched;

intdeleted;

inti;

conn=PQsetdb(NULL,NULL,NULL,NULL,database);

if (PQstatus(conn)==CONNECTION_BAD)

{

fprintf(stderr,"Connection to database '%s' failed.\n",database);

fprintf(stderr,"Connection to database '%s' failed:\n",database);

fprintf(stderr,"%s",PQerrorMessage(conn));

PQfinish(conn);

return-1;

}

if (verbose)

fprintf(stdout,"Connected to %s\n",database);

buf[0]='\0';

strcat(buf,"SELECT DISTINCT loid AS lo ");

strcat(buf,"INTO TEMP TABLE vacuum_l ");

strcat(buf,"FROM pg_largeobject ");

if (!(res=PQexec(conn,buf)))

res=PQexec(conn,buf);

if (PQresultStatus(res)!=PGRES_COMMAND_OK)

{

fprintf(stderr,"Failed to create temp table:\n");

fprintf(stderr,"%s",PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

return-1;

}

PQclear(res);

buf[0]='\0';

strcat(buf,"VACUUM ANALYZE vacuum_l ");

res=PQexec(conn,buf);

if (PQresultStatus(res)!=PGRES_COMMAND_OK)

{

fprintf(stderr,"Failed to create temp table.\n");

fprintf(stderr,"Failed to vacuum temp table:\n");

fprintf(stderr,"%s",PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

return-1;

}

PQclear(res);

buf[0]='\0';

strcat(buf,"SELECT c.relname, a.attname ");

strcat(buf," AND a.attrelid = c.oid ");

strcat(buf," AND a.atttypid = t.oid ");

strcat(buf," AND t.typname = 'oid' ");

strcat(buf," AND c.relkind = 'r'");

strcat(buf," AND c.relname NOT LIKE 'pg_%'");

if (!(res=PQexec(conn,buf)))

res=PQexec(conn,buf);

if (PQresultStatus(res)!=PGRES_TUPLES_OK)

{

fprintf(stderr,"Failed to create temp table.\n");

fprintf(stderr,"Failed to find OID columns:\n");

fprintf(stderr,"%s",PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

return-1;

}

for (i=0;i<PQntuples(res);i++)

{

char*table,

field=PQgetvalue(res,i,1);

if (verbose)

{

fprintf(stdout,"Checking %s in %s: ",field,table);

fflush(stdout);

}

res2=PQexec(conn,"begin");

PQclear(res2);

buf[0]='\0';

strcat(buf,"DELETE FROM vacuum_l ");

strcat(buf,"WHERE lo IN (");

strcat(buf,"SELECT ");

strcat(buf,field);

strcat(buf," FROM ");

strcat(buf,table);

strcat(buf,");");

if (!(res2=PQexec(conn,buf)))

{

fprintf(stderr,"Failed to check %s in table %s\n",field,table);

PQclear(res);

PQfinish(conn);

return-1;

}

fprintf(stdout,"Checking %s in %s\n",field,table);

sprintf(buf,"DELETE FROM vacuum_l WHERE lo = \"%s\".\"%s\" ",

table,field);

res2=PQexec(conn,buf);

if (PQresultStatus(res2)!=PGRES_COMMAND_OK)

{

fprintf(stderr,

"Failed to check %s in table %s\n%s\n",

field,table,

PQerrorMessage(conn)

);

fprintf(stderr,"Failed to check %s in table %s:\n",

field,table);

fprintf(stderr,"%s",PQerrorMessage(conn));

PQclear(res2);

PQclear(res);

PQfinish(conn);

return-1;

}

PQclear(res2);

res2=PQexec(conn,"end");

PQclear(res2);

}

PQclear(res);

res=PQexec(conn,"begin");

PQclear(res);

buf[0]='\0';

strcat(buf,"SELECT lo ");

strcat(buf,"FROM vacuum_l");

if (!(res=PQexec(conn,buf)))

res=PQexec(conn,buf);

if (PQresultStatus(res)!=PGRES_TUPLES_OK)

{

fprintf(stderr,"Failed to read temp table.\n");

fprintf(stderr,"Failed to read temp table:\n");

fprintf(stderr,"%s",PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

return-1;

}

matched=PQntuples(res);

deleted=0;

for (i=0;i<matched;i++)

{

Oidlo=(Oid)atoi(PQgetvalue(res,i,0));

Oidlo=atooid(PQgetvalue(res,i,0));

if (verbose)

{

fprintf(stdout,"\rRemoving lo %6d \n",lo);

fprintf(stdout,"\rRemoving lo %6u",lo);

fflush(stdout);

}

if (lo_unlink(conn,lo)<0)

fprintf(stderr,"Failed to remove lo %d\n",lo);

{

fprintf(stderr,"\nFailed to remove lo %u: ",lo);

fprintf(stderr,"%s",PQerrorMessage(conn));

}

deleted++;

}

PQclear(res);

res=PQexec(conn,"end");

PQclear(res);

PQfinish(conn);

if (verbose)

fprintf(stdout,"\rRemoved %d large objects from %s.\n",matched,database);

fprintf(stdout,"\rRemoved %d large objects from %s.\n",

deleted,database);

return0;

}

if (strcmp("-v",argv[arg])==0)

verbose= !verbose;

rc+=vacuumlo(argv[arg],verbose);

rc+=(vacuumlo(argv[arg],verbose)!=0);

}

returnrc;