Prepared SQL query and bad_alloc

SGaist

Are you batching the inserts ? Or are you preparing the query with all the content from the file and only then execute it ?

Vinorcola

I'm reading a line from the file, parse it, bind the values, and execute the query.
Then I read the next line from the file, parse it, bind the values, and execute the query.
etc.
etc.

SGaist

Sounds right, can you show your code ?

By the way, what version of Qt/OS are you using ?

Vinorcola

Ok, I created a smal test that I run with Qt 5.2 under Windows 7. And I effectively notice the memory used by the program is constantly increasing until the work finish. Here is the code for the test.

main.cpp
@
#include <QApplication>
#include "Window.hpp"

int main(int argc, char *argv[])
{
// Configuration de l'application.
QApplication app(argc, argv);

// Création du contrôleur principal.
Window* window(new Window);
window->show();

return app.exec&#40;&#41;;

}
@

Window.hpp
@
#include <QPushButton>

class Window : public QWidget
{
Q_OBJECT

private:
    QPushButton* button;
    
public:
    Window(&#41;;
    
public slots:
    void start(&#41;;

};
@

Window.cpp
@
#include "Window.hpp"

#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>
#include <QVBoxLayout>

Window::Window() :
QWidget(),
button(new QPushButton("Start"))
{
QVBoxLayout* layout(new QVBoxLayout);
layout->addWidget(button);
setLayout(layout);

setMinimumSize(400, 200);

connect(button, &QPushButton::clicked, this, &Window::start);

}

void Window::start()
{
// Database connection configuration...
QSqlDatabase db(QSqlDatabase::addDatabase("QPSQL"));
db.setHostName("");
db.setPort(5432);
db.setDatabaseName("");
db.setUserName("");
db.setPassword("");

// Test configuration...
int nbColumn(30);
int nbRow(1000000);

if (db.open())
{
    // Queries preparation...
    QString requestCreate("CREATE TABLE test (id INTEGER, ");
    QString requestInsert("INSERT INTO test (id");
    QString requestValues(") VALUES (?");
    for (int col(0); col < nbColumn; ++col)
    {
        requestCreate += "column" + QString::number(col) + " varchar(100), ";
        requestInsert += ", column" + QString::number(col);
        requestValues += ", ?";
    }
    requestCreate += "CONSTRAINT pk_test PRIMARY KEY (id))";
    requestInsert += requestValues + ")";
    
    // Table creation...
    QSqlQuery query(requestCreate, db);
    query.exec&#40;&#41;;
    
    // Data insertion...
    QSqlQuery query2(db&#41;;
    query2.prepare(requestInsert&#41;;
    for (int row(1&#41;; row <= nbRow; ++row)
    {
        query2.bindValue(0, row);// Insert the id
        for (int col(1); col <= nbColumn; ++col)
        {
            query2.bindValue(col, "Data from the row " + QString::number(row) + " and column " + QString::number(col));
        }
        query2.exec(&#41;;
    }
    
    // Close the connection.
    db.close(&#41;;
}

}
@

If some can test and see if the memory usage grows up too. If it does, it means apparently, there is something stored all along in QSqlQuery (maybe QSlqDatabase ?) which is released when the object is destroyed.

SGaist

I don't have a PostgreSQL server at hand. Can you do the test with sqlite ?

Vinorcola

I can try tomorow with MySQL. Can SQLite manager such big files? If it does, I can try tomorow too.
It could come from the Postgres driver? I'll also try on Ubuntu to see if I get the same issue.

SGaist

AFAIK, it should.

It can come from several places, first thing to do is determine whether it's specific to PostgreSQL then the rest

Vinorcola

Ok, so I made few different test on Windows (seven - 32bits - Qt 5.2.1) :
In the test configuration part of my code, I put nbColumn = 50 and nbRow = 500,000. I get those results:

Postgres:

at the end of the task: around 120,000 K of memory used
after exit the start() method: around 18,000 K of memory used

MySQL:

at the end of the task: around 58,000 K of memory used
after exit the start() method: around 6,000 K of memory used

SQLite:

at the end of the task: around 120,000 K of memory used
after exit the start() method: around 16,000 K of memory used

I did only 1 test for each because it take too much time. If some other people can to the test on another machine to see if they get the same result, it will be more relevant. I didn't have enough time to test on Ubuntu.

DoctorBit

I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

terminate called after throwing an instance of 'std::bad_alloc'
what(): std::bad_alloc
Aborted

My system:

Core i5 2500K processor
16 GB of RAM
Linux Mint 64 bit
Postgresql 9.4.0
GCC 4.9.2 64 bit
Qt Creator 3.1.1
Qt 5.2.1 (GCC 4.6.1, 64 bit)

DoctorBit

I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

terminate called after throwing an instance of 'std::bad_alloc'
what(): std::bad_alloc
Aborted

My system:

Core i5 2500K processor
16 GB of RAM
Linux Mint 64 bit
Postgresql 9.4.0
GCC 4.9.2 64 bit
Qt Creator 3.1.1
Qt 5.2.1 (GCC 4.6.1, 64 bit)

DoctorBit

Upgraded to Qt 5.4.0 and the problem seems to have gone away.

DoctorBit

Upgraded to Qt 5.4.0 and the problem seems to have gone away.