int
lobject_unlink(lobjectObject *self)
{
PGresult *pgres = NULL;
char *error = NULL;
int retvalue = -1;
Py_BEGIN_ALLOW_THREADS;
pthread_mutex_lock(&(self->conn->lock));
retvalue = pq_begin_locked(self->conn, &pgres, &error);
if (retvalue < 0)
goto end;
/* first we make sure the lobject is closed and then we unlink */
retvalue = lobject_close_locked(self, &error);
if (retvalue < 0)
goto end;
retvalue = lo_unlink(self->conn->pgconn, self->oid);
if (retvalue < 0)
collect_error(self->conn, &error);
end:
pthread_mutex_unlock(&(self->conn->lock));
Py_END_ALLOW_THREADS;
if (retvalue < 0)
pq_complete_error(self->conn, &pgres, &error);
return retvalue;
}
//update wave_detail_rpt set efile=%d where
bool SPostgres::UpdateLobFromFile(SString sTable,SString sLobField,SString sWhere,SString sFile)
{
LOGBASEDEBUG("Into SPostgres::UpdateLobFromFile(%s,%s,%s,%s)",
sTable.data(),sLobField.data(),sWhere.data(),sFile.data());
SString sql;
Oid oid;
SString param;
PGresult *pRes = PQexec(m_pConn, "begin");
if(pRes == NULL)
{
if(TestConnect() == true)//连接可用
{
LOGERROR("Error in SPostgres::UpdateLobFromFile, exec begin error, err=%s", PQerrorMessage(m_pConn));
return false;
}
//失败自动重连一次数据库
if(!Reconnect())
{
LOGERROR("Error in SPostgres::UpdateLobFromFile, exec begin error, err=%s", PQerrorMessage(m_pConn));
return false;
}
pRes = PQexec(m_pConn, "begin");
if(pRes == NULL)
{
LOGERROR("Error in SPostgres::UpdateLobFromFile, exec begin error, err=%s", PQerrorMessage(m_pConn));
return false;
}
}
PQclear(pRes);
oid = lo_import(m_pConn, sFile.data());
if(oid == 0)
{
LOGERROR("Error in SPostgres::UpdateLobFromFile(%s,%s,%s,%s), err=%s",
sTable.data(),sLobField.data(),sWhere.data(),sFile.data(),PQerrorMessage(m_pConn));
pRes = PQexec(m_pConn, "end");
if(pRes != NULL)
PQclear(pRes);
return false;
}
sql.sprintf("update %s set %s=%d where %s",sTable.data(),sLobField.data(),oid,sWhere.data());
pRes = PQexec(m_pConn, sql.data());
LOGBASEDEBUG("%s",sql.data());
if(pRes == NULL || PQresultStatus(pRes) != PGRES_COMMAND_OK )
{
LOGERROR("Error in SPostgres::UpdateLobFromFile(%s,%s,%s,%s), sql=%s, err=%s",
sTable.data(),sLobField.data(),sWhere.data(),sFile.data(),sql.data(),PQerrorMessage(m_pConn));
lo_unlink(m_pConn,oid);
if(pRes != NULL)
PQclear(pRes);
pRes = PQexec(m_pConn, "end");
if(pRes != NULL)
PQclear(pRes);
return false;
}
PQclear(pRes);//add by skt at 2007-9-13不清除将导致内存缓慢增长
pRes = PQexec(m_pConn, "end");
if(pRes != NULL)
PQclear(pRes);
return pRes != NULL;
}
/*
* do_lo_unlink()
*
* removes a large object out of the database
*/
bool
do_lo_unlink(const char *loid_arg)
{
int status;
Oid loid = atooid(loid_arg);
bool own_transaction;
if (!start_lo_xact("\\lo_unlink", &own_transaction))
return false;
SetCancelConn();
status = lo_unlink(pset.db, loid);
ResetCancelConn();
if (status == -1)
{
psql_error("%s", PQerrorMessage(pset.db));
return fail_lo_xact("\\lo_unlink", own_transaction);
}
if (!finish_lo_xact("\\lo_unlink", own_transaction))
return false;
print_lo_result("lo_unlink %u", loid);
return true;
}
/*
* do_lo_unlink()
*
* removes a large object out of the database
*/
bool
do_lo_unlink(const char *loid_arg)
{
int status;
Oid loid = atooid(loid_arg);
bool own_transaction;
if (!start_lo_xact("\\lo_unlink", &own_transaction))
return false;
SetCancelConn();
status = lo_unlink(pset.db, loid);
ResetCancelConn();
if (status == -1)
{
fputs(PQerrorMessage(pset.db), stderr);
return fail_lo_xact("\\lo_unlink", own_transaction);
}
if (!finish_lo_xact("\\lo_unlink", own_transaction))
return false;
fprintf(pset.queryFout, "lo_unlink %u\n", loid);
return true;
}
CAMLprim value lo_unlink_stub(value v_conn, value v_oid)
{
CAMLparam1(v_conn);
PGconn *conn = get_conn(v_conn);
value v_res;
caml_enter_blocking_section();
v_res = Val_int(lo_unlink(conn, Int_val(v_oid)));
caml_leave_blocking_section();
CAMLreturn(v_res);
}
/* ************************************************************************* */
int pg_lo_unlink(ClipMachine* mp, SQLCONN* c, unsigned int OID){
PG_CONN *conn = (PG_CONN*)c;
int rt;
if(!conn->at){
_clip_trap_err(mp,0,0,0,subsys,ER_START,er_start);
return 1;
}
rt = lo_unlink(conn->conn, OID);
if (rt < 0){
_clip_trap_err(mp,0,0,0,subsys,ER_START,er_blob_unlink);
return 1;
}
return 0;
}
virtual void bind(int col,std::istream &in)
{
check(col);
if(blob_ == bytea_type) {
std::ostringstream ss;
ss << in.rdbuf();
params_values_[col-1]=ss.str();
params_set_[col-1]=binary_param;
}
else {
Oid id = 0;
int fd = -1;
try {
id = lo_creat(conn_, INV_READ|INV_WRITE);
if(id == 0)
throw pqerror(conn_,"failed to create large object");
fd = lo_open(conn_,id,INV_READ | INV_WRITE);
if(fd < 0)
throw pqerror(conn_,"failed to open large object for writing");
char buf[4096];
for(;;) {
in.read(buf,sizeof(buf));
int bytes_read = in.gcount();
if(bytes_read > 0) {
int n = lo_write(conn_,fd,buf,bytes_read);
if(n < 0) {
throw pqerror(conn_,"failed writing to large object");
}
}
if(bytes_read < int(sizeof(buf)))
break;
}
int r = lo_close(conn_,fd);
fd=-1;
if(r < 0)
throw pqerror(conn_,"error closing large object after write");
bind(col,id);
}
catch(...) {
if(fd<-1)
lo_close(conn_,fd);
if(id!=0)
lo_unlink(conn_,id);
throw;
}
}
}
/*
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
*/
static int
vacuumlo(const char *database, const struct _param *param)
{
PGconn *conn;
PGresult *res,
*res2;
char buf[BUFSIZE];
long matched;
long deleted;
int i;
bool new_pass;
bool success = true;
static bool have_password = false;
static char password[100];
/* Note: password can be carried over from a previous call */
if (param->pg_prompt == TRI_YES && !have_password)
{
simple_prompt("Password: "******"host";
values[0] = param->pg_host;
keywords[1] = "port";
values[1] = param->pg_port;
keywords[2] = "user";
values[2] = param->pg_user;
keywords[3] = "password";
values[3] = have_password ? password : NULL;
keywords[4] = "dbname";
values[4] = database;
keywords[5] = "fallback_application_name";
values[5] = param->progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
conn = PQconnectdbParams(keywords, values, true);
if (!conn)
{
fprintf(stderr, "Connection to database \"%s\" failed\n",
database);
return -1;
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
!have_password &&
param->pg_prompt != TRI_NO)
{
PQfinish(conn);
simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
database, PQerrorMessage(conn));
PQfinish(conn);
return -1;
}
if (param->verbose)
{
fprintf(stdout, "Connected to database \"%s\"\n", database);
if (param->dry_run)
fprintf(stdout, "Test run: no large objects will be removed!\n");
}
res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "Failed to set search_path:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* First we create and populate the LO temp table
*/
buf[0] = '\0';
strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
if (PQserverVersion(conn) >= 90000)
strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
else
strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
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);
/*
* Analyze the temp table so that planner will generate decent plans for
* the DELETEs below.
*/
buf[0] = '\0';
strcat(buf, "ANALYZE vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to vacuum temp table:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* Now find any candidate tables that have columns of type oid.
*
* NOTE: we ignore system tables and temp tables by the expedient of
* rejecting tables in schemas named 'pg_*'. In particular, the temp
* table formed above is ignored, and pg_largeobject will be too. If
* either of these were scanned, obviously we'd end up with nothing to
* delete...
*
* NOTE: the system oid column is ignored, as it has attnum < 1. This
* shouldn't matter for correctness, but it saves time.
*/
buf[0] = '\0';
strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
strcat(buf, " AND a.attrelid = c.oid ");
strcat(buf, " AND a.atttypid = t.oid ");
strcat(buf, " AND c.relnamespace = s.oid ");
strcat(buf, " AND t.typname in ('oid', 'lo') ");
strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
strcat(buf, " AND s.nspname !~ '^pg_'");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
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 *schema,
*table,
*field;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
field = PQgetvalue(res, i, 2);
if (param->verbose)
fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
schema = PQescapeIdentifier(conn, schema, strlen(schema));
table = PQescapeIdentifier(conn, table, strlen(table));
field = PQescapeIdentifier(conn, field, strlen(field));
if (!schema || !table || !field)
{
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
if (schema != NULL)
PQfreemem(schema);
if (schema != NULL)
PQfreemem(table);
if (schema != NULL)
PQfreemem(field);
return -1;
}
snprintf(buf, BUFSIZE,
"DELETE FROM vacuum_l "
"WHERE lo IN (SELECT %s FROM %s.%s)",
field, schema, table);
res2 = PQexec(conn, buf);
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to check %s in table %s.%s:\n",
field, schema, table);
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
PQfreemem(schema);
PQfreemem(table);
PQfreemem(field);
return -1;
}
PQclear(res2);
PQfreemem(schema);
PQfreemem(table);
PQfreemem(field);
}
PQclear(res);
/*
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
*
* We don't want to run each delete as an individual transaction, because
* the commit overhead would be high. However, since 9.0 the backend will
* acquire a lock per deleted LO, so deleting too many LOs per transaction
* risks running out of room in the shared-memory lock table. Accordingly,
* we delete up to transaction_limit LOs per transaction.
*/
res = PQexec(conn, "begin");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to start transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
buf[0] = '\0';
strcat(buf,
"DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
param->transaction_limit > 0 ? param->transaction_limit : 1000L);
deleted = 0;
while (1)
{
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH FORWARD failed: %s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
matched = PQntuples(res);
if (matched <= 0)
{
/* at end of resultset */
PQclear(res);
break;
}
for (i = 0; i < matched; i++)
{
Oid lo = atooid(PQgetvalue(res, i, 0));
if (param->verbose)
{
fprintf(stdout, "\rRemoving lo %6u ", lo);
fflush(stdout);
}
if (param->dry_run == 0)
{
if (lo_unlink(conn, lo) < 0)
{
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
fprintf(stderr, "%s", PQerrorMessage(conn));
if (PQtransactionStatus(conn) == PQTRANS_INERROR)
{
success = false;
PQclear(res);
break;
}
}
else
deleted++;
}
else
deleted++;
if (param->transaction_limit > 0 &&
(deleted % param->transaction_limit) == 0)
{
res2 = PQexec(conn, "commit");
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to commit transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res2);
res2 = PQexec(conn, "begin");
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to start transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res2);
}
}
PQclear(res);
}
/*
* That's all folks!
*/
res = PQexec(conn, "commit");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to commit transaction:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
PQfinish(conn);
if (param->verbose)
{
if (param->dry_run)
fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
deleted, database);
else if (success)
fprintf(stdout,
"\rSuccessfully removed %ld large objects from database \"%s\".\n",
deleted, database);
else
fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
database, deleted, matched);
}
return ((param->dry_run || success) ? 0 : -1);
}
/*
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
*/
int
vacuumlo(char *database, struct _param * param)
{
PGconn *conn;
PGresult *res,
*res2;
char buf[BUFSIZE];
int matched;
int deleted;
int i;
static char *password = NULL;
bool new_pass;
if (param->pg_prompt == TRI_YES && password == NULL)
password = simple_prompt("Password: "******"Connection to database \"%s\" failed\n",
database);
return -1;
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
param->pg_prompt != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
database, PQerrorMessage(conn));
PQfinish(conn);
return -1;
}
if (param->verbose)
{
fprintf(stdout, "Connected to %s\n", database);
if (param->dry_run)
fprintf(stdout, "Test run: no large objects will be removed!\n");
}
/*
* Don't get fooled by any non-system catalogs
*/
res = PQexec(conn, "SET search_path = pg_catalog");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to set search_path:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* First we create and populate the LO temp table
*/
buf[0] = '\0';
strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
if (PQserverVersion(conn) >= 90000)
strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
else
strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
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);
/*
* Analyze the temp table so that planner will generate decent plans for
* the DELETEs below.
*/
buf[0] = '\0';
strcat(buf, "ANALYZE vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to vacuum temp table:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res);
/*
* Now find any candidate tables that have columns of type oid.
*
* NOTE: we ignore system tables and temp tables by the expedient of
* rejecting tables in schemas named 'pg_*'. In particular, the temp
* table formed above is ignored, and pg_largeobject will be too. If
* either of these were scanned, obviously we'd end up with nothing to
* delete...
*
* NOTE: the system oid column is ignored, as it has attnum < 1. This
* shouldn't matter for correctness, but it saves time.
*/
buf[0] = '\0';
strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
strcat(buf, " AND a.attrelid = c.oid ");
strcat(buf, " AND a.atttypid = t.oid ");
strcat(buf, " AND c.relnamespace = s.oid ");
strcat(buf, " AND t.typname in ('oid', 'lo') ");
strcat(buf, " AND c.relkind = 'r'");
strcat(buf, " AND s.nspname !~ '^pg_'");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
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 *schema,
*table,
*field;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
field = PQgetvalue(res, i, 2);
if (param->verbose)
fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
snprintf(buf, BUFSIZE,
"DELETE FROM vacuum_l "
"WHERE lo IN (SELECT \"%s\" FROM \"%s\".\"%s\")",
field, schema, table);
res2 = PQexec(conn, buf);
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to check %s in table %s.%s:\n",
field, schema, table);
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
return -1;
}
PQclear(res2);
}
PQclear(res);
/*
* Run the actual deletes in a single transaction. Note that this would
* be a bad idea in pre-7.1 Postgres releases (since rolling back a table
* delete used to cause problems), but it should be safe now.
*/
res = PQexec(conn, "begin");
PQclear(res);
/*
* Finally, those entries remaining in vacuum_l are orphans.
*/
buf[0] = '\0';
strcat(buf, "SELECT lo ");
strcat(buf, "FROM vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
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++)
{
Oid lo = atooid(PQgetvalue(res, i, 0));
if (param->verbose)
{
fprintf(stdout, "\rRemoving lo %6u ", lo);
fflush(stdout);
}
if (param->dry_run == 0)
{
if (lo_unlink(conn, lo) < 0)
{
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
fprintf(stderr, "%s", PQerrorMessage(conn));
}
else
deleted++;
}
else
deleted++;
}
PQclear(res);
/*
* That's all folks!
*/
res = PQexec(conn, "end");
PQclear(res);
PQfinish(conn);
if (param->verbose)
fprintf(stdout, "\r%s %d large objects from %s.\n",
(param->dry_run ? "Would remove" : "Removed"), deleted, database);
return 0;
}
