Thread: Inserting large number of rows using libpq++ stops responding in c++
postgresql-7.2-103 using libpq++ in c++ program in SuSE Linux 7.2 (i386) opening dataset using: dbdata = new PgTransaction(db_server.c_str()); executing SQL statements using: dbdata->ExecCommandOk( query.c_str() ) forcing data to write when done with: dbdata->ExecCommandOk( "COMMIT;" ); Executing multiple commands such as: dbdata->ExecCommandOk( "INSERT INTO playeritems (playerid, linenumber, level, depot, id, wear, number)VALUES (24725, 102, 3, true, 126, 2, 1);" (SQL command is actually stored in std::string) It will execute a number of these commands, 50 to 90 or so, then the ExecCommandOk will not respond on one. It just never returns. Running the exact same series of SQL statements it varies on how many it will execute before locking up. Upgraded to the lastest postgreSQL in RPM form to try to resolve (postgresql-7.2-103) with no change. My only theory as to what might be causing this would be the large number of SQL commands executed before the commit. Is this right? Is there another (better?) way to insert data? Or a work around? This is frustrating and is keeping me from progressing with my programming. Any solutions/help/suggestions is appreciated. Regards, Jim Langston ===== Taz Master mailto:tazmaster@rocketmail.com __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Taz Master <tazmaster@rocketmail.com> writes:
> My only theory as to what might be causing this would be the large
> number of SQL commands executed before the commit. Is this right?
No. 100 inserts hardly qualifies as "a large number" -- people
routinely run transactions with many thousand commands.
I'm guessing a bug in your own code, though it's possible that libpq++
is the source of the problem.
If you have any other clients active at the same time, it's also
possible that there is no bug, and the thing is simply waiting for
a lock held by some other client. This would be more likely if you
have foreign keys in the table being inserted into --- foreign key
references take write-locks on the referenced rows.
regards, tom lane
There is no other client connected to this table at this particular
time, though there will be in the future, so that wouldn't be in. Not
a possible deadlock condition.
I don't see a possbile bug that could cause this. The line that is
locking up is the dbdata->ExecCommandOk( query.c_str() ) and for
debugging I look at the query before hand and it's fine.
What possible program bug could cause ExecCommandOk to not return?
Is my series of libpq++ methods okay?
#include <string>
#include "libpq++.h"
class CDBConnect
{
public:
//! Constructor
CDBConnect();
CDBConnect( std::string server );
//! Destructor
virtual ~CDBConnect();
bool executeSQLcommand( std::string query );
bool commit( );
private:
PgTransaction* dbdata;
std::string db_server;
std::string db_query;
};
CDBConnect::CDBConnect( std::string server )
{
std::cout << std::endl << "SERVER: " << server << std::endl;
db_server = server;
dbdata = new PgTransaction(db_server.c_str());
if ( dbdata->ConnectionBad() )
{
std::cerr << "Did not connect to server '" << db_server <<
std::endl;
std::cout << "Did not connect to server '" << db_server <<
std::endl;
delete (dbdata);
dbdata = NULL;
}
}
// dataserver = "dbname=testserver user=baldur"
CDBConnect* DBInventory = new CDBConnect( dataserver );
if ( ! DBInventory->executeSQLcommand( "DELETE FROM playeritems
where playerid = " + toString( id ) ) )
{
std::cout << "Player: " + name + " in inventory not found to
delete. Saveing anyway." << std::endl;
}
// Now I call many of these.
std::string SQLCommand =
" INSERT INTO playeritems ("
"playerid, linenumber, level, depot, id, wear, number)"
"VALUES (" +
toString( id ) + ", " + toString( linenumber++ ) + ", 0,
false, " +
toString( characterItems[ thisItemSlot ].id ) + ", " +
toString( characterItems[ thisItemSlot ].wear ) + ", " +
toString( characterItems[ thisItemSlot ].number ) + ")";
if ( ! DBInventory->executeSQLcommand( SQLCommand ))
{
}
// And when one of those never returns
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Taz Master <tazmaster@rocketmail.com> writes:
> > My only theory as to what might be causing this would be the large
> > number of SQL commands executed before the commit. Is this right?
>
> No. 100 inserts hardly qualifies as "a large number" -- people
> routinely run transactions with many thousand commands.
>
> I'm guessing a bug in your own code, though it's possible that
> libpq++
> is the source of the problem.
>
> If you have any other clients active at the same time, it's also
> possible that there is no bug, and the thing is simply waiting for
> a lock held by some other client. This would be more likely if you
> have foreign keys in the table being inserted into --- foreign key
> references take write-locks on the referenced rows.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
=====
Taz Master
mailto:tazmaster@rocketmail.com
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
(( I forgot to show my executeSQLcommand method
bool CDBConnect::executeSQLcommand( std::string query )
{
if ( query[query.length()] != ';' )
{
query = query + ";";
}
if ( dbdata == NULL )
{
std::cout << "Not connected to server." << std::endl;
return false;
}
if ( ! dbdata->ExecCommandOk( query.c_str() ) )
{
std::cerr << "SQL command failed!" << std::endl;
std::cerr << "Error was: " << dbdata->ErrorMessage() <<
std::endl;
std::cout << "SQL command failed!" << std::endl;
std::cout << "Error was: " << dbdata->ErrorMessage() <<
std::endl;
return false;
}
return true;
}
=====
Taz Master
mailto:tazmaster@rocketmail.com
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com