QGIS 3 Python plugin psycopg2 Postgres Notify/Listen Live Connection

by Raúl   Last Updated December 06, 2018 15:22 PM

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?

Related Questions

Exporting .shp file to PostgreSQL using python Python

Updated September 09, 2016 08:09 AM

JSON rings data to PostGIS

Updated November 29, 2017 03:22 AM

Get a point value with psycopg2 and PPyGIS

Updated January 31, 2018 01:22 AM