Qt doesn't detect PostgreSQL asynchronous notifications in same instance

[Auryn]

I have created a rule in a PosgreSQL table to execute it when inserting a row.

It calls "NOTIFY myNotify" and works fine when I insert a row with an INSERT SQL command. Qt detects the notification well.

When I insert a row in a table view that depends on a table model, the rule works fine, but Qt doesn't detect the notification.

Here is a sample main function:


#include <QApplication>
#include <QSqlDriver>
#include "myClass.hpp"

int main(int argc, char *argv[]) {
QApplication aplication(argc, argv);

// Open conection to database
QSqlDatabase bd(QSqlDatabase::addDatabase("QPSQL"));
bd.setHostName("localhost");
bd.setDatabaseName("myDB");
bd.open("postgres", "postgres");

// Load data in the table
QTableView myTV;
QSqlRelationalTableModel myModel(&myTV, bd);
myModel.setEditStrategy(QSqlTableModel::OnManualSu bmit);
myModel.setTable("myTable");

// Show table with the data from database
myModel.select();
myTV.setModel(&myModel);
myTV.show();

// Create the object with the slot to save changes to database
TmyClass myObject(0, &myModel);

// Subscribe and define the treatment for the notification thrown when updating a field
bd.driver()->subscribeToNotification("modified_row");
QObject::connect((QObject*)bd.driver(), SIGNAL(notification(const QString&)), (QObject*)&myObject, SLOT(myNotifyHandler(const QString&)));

return aplication.exec();
}


The class to save changes manually:


#ifndef TMYCLASS
#define TMYCLASS

#include <QtCore/QDebug>
#include <QtGui/QTableView>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRelationalDelegate>

class TmyClass : public QObject
{
Q_OBJECT

public:
TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel);

public slots:
void mySave(const QModelIndex &a, const QModelIndex &b);
void myNotifyHandler(const QString &notification);

private:
QSqlRelationalTableModel *m_model;
};

#endif

TmyClass::TmyClass(QObject *parent, QSqlRelationalTableModel *oneModel): QObject(parent), m_model(oneModel)
{
// When something changes, call the slot mySave
connect(m_model, SIGNAL(dataChanged(const QModelIndex&, const QModelIndex&)), this, SLOT(mySave(const QModelIndex&, const QModelIndex&)));
}


void TmyClass::mySave(const QModelIndex &a, const QModelIndex &b)
{
if(!m_model->submitAll())
{
qDebug()<<"Error when submitting:"<<m_model->lastError().text();
}
}


void TmyClass::myNotifyHandler(const QString &notification)
{
qDebug()<<"Detected notification from PostgreSQL: "<<notification;
}


PostgreSQL sample database dump (is a very simple table, the important detail is "CREATE RULE "myRule" AS ON UPDATE TO "myTable" DO NOTIFY modified_row;"):


SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE "myTable" (
"ID" integer NOT NULL,
name text
);
ALTER TABLE public."myTable" OWNER TO postgres;
CREATE SEQUENCE "myTable_ID_seq"
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public."myTable_ID_seq" OWNER TO postgres;
ALTER SEQUENCE "myTable_ID_seq" OWNED BY "myTable"."ID";
SELECT pg_catalog.setval('"myTable_ID_seq"', 3, true);
ALTER TABLE "myTable" ALTER COLUMN "ID" SET DEFAULT nextval('"myTable_ID_seq"'::regclass);
COPY "myTable" ("ID", name) FROM stdin;
1 One
2 Two
3 Three
\.

ALTER TABLE ONLY "myTable" ADD CONSTRAINT pk PRIMARY KEY ("ID");
CREATE RULE "myRule" AS ON UPDATE TO "myTable" DO NOTIFY modified_row;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


Some links I have read:
QSqlDriver notifications (http://doc.trolltech.com/main-snapshot/qsqldriver.html#subscribeToNotification)
Blog about asynchronous database event notifications (http://labs.trolltech.com/blogs/2007/11/02/asynchronous-database-event-notifications/)
Asynchronous notifications in PostgreSQL (http://www.postgresql.org/docs/8.3/static/libpq-notify.html)

[wysota]

If you modify the model, the changes are not saved to the database immediately. They are instead cached in the model. To force a flush to the database, call submitAll() on the model.

[Auryn]

Yes, I do the submitAll in a slot, but it doesn't detect the notify.

With my example, if I execute directly in pgadmin3:


UPDATE "myTable" SET name='changed' WHERE "ID"=1;

My example shows what I wanted:

Detected notification from PostgreSQL: "modified_row"

Thank you very much

[Auryn]

I have tested with two instances of my example at the same time, and one detects the update done in the other.
Then, one instance can't detect its own database modifications.
Any idea?

[wysota]

Are you sure the changes are submitted to the database?

[Auryn]

Yes, I am sure the changes are submitted to the database.

[wysota]

Are you sure postgres sends notifications to the same connection that caused the modification? Could you verify that with a simple program using PSQL API directly?

[Auryn]

I have used psql to listen for the notification:


LISTEN modified_row;


It works well like that:


myDB=# SELECT * FROM "myTable";
ID | name
----+-------
1 | Tom
2 | Peter
3 | John
(3 rows)

myDB=# UPDATE "myTable" SET name='Mike' WHERE "ID"=3;
UPDATE 1
Asynchronous notification «modified_row» received from server process with PID 13043.
myDB=# SELECT * FROM "myTable";
ID | name
----+-------
1 | Tom
2 | Peter
3 | Mike
(3 rows)

[Auryn]

So notifications work:
- when using psql. For example, when writing in the psql window: "Update..." then the Qt app gets informed of the change.
- when doing the test of: having two instances of my example program at the same time (one detects the update done in the other).

But it does not work when:
- One program can't detect the modifications done within the same program.

But maybe it's a minor change that I should do. :(

[darkman_dev]

hi,

I found the same problem. It should be a QT problem (bug), the interesting part of all this is that, when the same application "NOTIFY" it self, all notifications are buffered, by the driver, but the receiver's slot is not called until a "NOTIFY" from other process occur.



class Test : public QObject
{
Q_OBJECT

public slots:
void receiver()
{
qDebug("signal emitted");
}

public:
Test(){};
};
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
initDB();
Test r;

if(!db.driver()->subscribeToNotification("aaaa"))
qDebug( qPrintable(db.driver()->lastError().databaseText()) );

QObject::connect((QObject*)db.driver(), SIGNAL(notification(const QString&)), (QObject*)&r, SLOT(receiver()) );


QSqlQuery q0("NOTIFY aaaa;");
q0.exec();
QSqlQuery q1("NOTIFY aaaa;");
q1.exec();

return a.exec();
}


In this case, when some other process execute "NOTIFY aaaa;" the "receiver()" slot will be called 3 times.

Any suggestions?

[Lesiok]

Renew this thread.
Problem occurs on Linux and NOT occurs on Windows. In attachment simple test application.