Loading layers into QGIS - Heavy work in the background - Too many clients already

Question

I am 3rd year geoinformatics bachelor student currently developing a QGIS plugin that will allow me to import a larger number of layers from the data storage (Postgres) into the software. For the start I have made a script that imports all the layers, but I encountered a problem. When the script is running the program stops responding for about 10-20s, but when the transfer is done the program starts working again and all the imports are done successfully. Script uses just one connection (link) to the data base.

In the documentation QGIS I found a possible solution in the section called »doing heavy work in the background«. There are 3 possible solutions, I used the function approach. Link to documentation: https://docs.qgis.org/3.10/en/docs/pyqgis_developer_cookbook/tasks.html?fbclid=IwAR2FkJD8fbvXX8_yDwkzyLZ7arR9najpTWQXT2IGRI5Buqcy5CIKuUtDM0c

I modified the function that calls layers in QGIS into “heavy work function” which should work in the background and therefor stop software from crashing and/or stops responding. It only works for the first 100 layers. Problem is that the program creates a new link (connection) for each layer, but maximum of links is limited to 100. What I am looking to do is to open only one link for all the layers.

I know that the modified function isn’t done completely, because I have to add progress bar stat, but first I want to make sure, that function works properly for all the layers (to open connection just once).

def doSomething(task, wait_time):
    """
    Raises an exception to abort the task.
    Returns a result if success.
    The result will be passed, together with the exception (None in
    the case of success), to the on_finished method.
    If there is an exception, there will be no result.
    """
    QgsMessageLog.logMessage('Started task {}'.format(task.description()),
                             MESSAGE_CATEGORY, Qgis.Info)


    # My connection to local Database
    uri = QgsDataSourceUri()
    uri.setConnection("localhost", "5432", ime_db, "postgres", "pass", False)    

    # seznam_IME is a list that contains Layer names
    # seznam_OBL is a list that contains Layer Shape (Point, Line, Poylgon)
    # My heavy task that import layers from Postgre

    for tabele, oblike in zip(seznam_IME, seznam_OBL):

        uri.setDataSource("public", tabele, "geometry")
        uri.setSrid("0")
        uri.geometryAttributes = "geometry"

        if oblike == "Točka":    #Točka in our language means Point
            uri.setWkbType(QgsWkbTypes.Point)

        elif oblike == "Polilinija":    #Polilinija in our language means Polyline
            uri.setWkbType(QgsWkbTypes.LineString)

        elif oblike == "Poligon":       #Poligon in our language means Polygon
            uri.setWkbType(QgsWkbTypes.Polygon)

        iface.mainWindow().blockSignals(True)
        layer = QgsVectorLayer(uri.uri(), tabele, "postgres")
        crs = layer.crs()
        crs.createFromId(4326)
        layer.setCrs(crs)
        iface.mainWindow().blockSignals(False)

        QgsProject.instance().addMapLayer(layer, False)
        subgroup.addLayer(layer)

    return {'total': total, 'iterations': iterations,
            'task': task.description()}

def stopped(task):
    QgsMessageLog.logMessage(
        'Task "{name}" was canceled'.format(
            name=task.description()),
        MESSAGE_CATEGORY, Qgis.Info)

def completed(exception, result=None):
    """This is called when doSomething is finished.
    Exception is not None if doSomething raises an exception.
    result is the return value of doSomething."""
    if exception is None:
        if result is None:
            QgsMessageLog.logMessage(
                'Completed with no exception and no result ' \
                '(probably manually canceled by the user)',
                MESSAGE_CATEGORY, Qgis.Warning)
        else:
            QgsMessageLog.logMessage(
                'Task {name} completed\n'
                'Total: {total} ( with {iterations} '
                'iterations)'.format(
                    name=result['task'],
                    total=result['total'],
                    iterations=result['iterations']),
                MESSAGE_CATEGORY, Qgis.Info)
    else:
        QgsMessageLog.logMessage("Exception: {}".format(exception),
                                 MESSAGE_CATEGORY, Qgis.Critical)
        raise exception


# Creae a few tasks
task1 = QgsTask.fromFunction('Waste cpu 1', doSomething,
                             on_finished=completed, wait_time=4)

QgsApplication.taskManager().addTask(task1)

Answer 1

The heavy work is equal to threading in programmation. When you run your script, to improve the speed, your code is encapsulate and execute alone. One solution is to register an singleton object and refer to it each time you need it. Your connection will be unique but it will take the same time. You must commit changes only one time too. I suggess you to prefer using processing for this kind of task Qgis documentation