I'm developing a plugin for QGIS3 for my company, where my managers can allocate tasks to other users. I have programmed two tables within the python script, witch will be only visible according to the specific settings for every user. All that is fine and is working very well.

enter image description here

What I want to achieve is to be able to LISTEN to changes being made within the main table in Postgres, so when a manager allocates a task to somebody, that person will receive the signal, get a QgsMessageLog and a refresh of the table.

As an example: if you select an item in the dropdown displayed, that will send a query with an "UPDATE SET column etc." that will modify that specific cell in the postgres table. That works fine. I just want to capture THAT specific row being modified and notify QGIS.

I thought about linking the table refresh to the canvas rendering or even every certain time, but this won't be efficient, especially if the table gets too big one day.

I've been using this snippet from the psycopg2 documentation:

conn = psycopg2.connect("dbname='xxx' user='xxx' host='xxx' password='xxx'")
cur = conn.cursor()
cur.execute("LISTEN UpdateTable;")

print ("Waiting for notifications on channel 'test'")
while 1:
    if select.select([conn],[],[],5) == ([],[],[]):
        print ("Timeout")
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print ("Got NOTIFY:", notify.pid, notify.channel, notify.payload)

It works well, however it only stays live for 1 second, causing it to die and to crash QGIS.

I've read the newest QGIS 3 documentation and they have implemented the possibility of creating a live connection with specific layers to LISTEN to NOTIFY events from postgres. As far as I understand, this is achieved through QgsDataSourceUri or another qgs class. Maybe that would be the best way forward?

I tried to create a task, but the task either doesn't run or it crashes the QGIS. The documentation states that we shouldn't allocate tasks in the task manager that can interact with the GUI.

So, is it even possible to achieve this at all?

  • Welcome to GIS SE! As a new user, please be sure to take the tour to learn about this site's focused Q&A format. – Andy Dec 6 '18 at 14:52
  • yes qgis3 you can use directly listener to postgres triggers... can you detail when do you have crash? did you test with master? Usually crash can be related with a thread part trying to use directly GUI that is managed in the main thread.... but no idea is your case. – Luigi Pirelli Feb 17 '19 at 9:59
  • If the LISTEN statement is done through python, yes it get's stuck since it stays in 'LISTEN' mode forever (the code can't keep going forward therefore it crashes). The only way to listen/notify between qgis and postgres is through QgsDataProvider or through the QgsMapLayer.setRefreshOnNotifyEnabled(True) which targets a layer necessarily. I will pull a request so the notify could be implemented in the QgsProject. Thanks! – Raúl Feb 26 '19 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.