QSqlTableModel.setTable() doesn't work with PostgreSQL 12

[skoczian]

After upgrading to PostgreSQL 12 I can't use QSqlTableModel any more with PostgreSQL. While QSqlQueries work as expected, creating a usable QSqlTableModel fails:

def create_stamm_model():
    model = QtSql.QSqlTableModel()
    logging.debug("Message by model: %s", model.lastError().text())
    db = model.database()
    logging.debug("Data base tables: %s", db.tables())  # <-- correct answer
    model.setTable("test_table")  # <-- name is contained in the list from db.tables()
    logging.debug("Message by model.setTable: %s",
                  model.lastError().text())  # <-- error, see below
...

output:

DEBUG:root:Message by model:  
DEBUG:root:Data base tables: ['t_manip', 't_spiel', 't_stamm', 't_ewtab', 't_pps', 't_tab']
DEBUG:root:Message by model.setTable:  Unable to find table t_ewtab

I'm using PyQt5, but this looks more like a Qt bug, I think. I found the very similar QTBUG-79033, but that talks about model.select() failing, while setTable() fails even earlier.

[skoczian]

As far as I know the Qt model classes don't necessarily import all the data they work with into the application and I doubt the QSqlTableModel does.

But that's definitely not the point here. The point is that this class doesn't work with the current version of PostgreSQL - and that the first thing that goes wrong isn't the select() method as described in the bug report I cited, but the setTable() method which must be called first. And if you don't pepper your code with logging statements or the like it goes wrong silently. You just wonder about your completely empty QTableView components.

I know that the QtSql classes are not the only way to use databases with a Qt (or PyQt) GUI. But, again, that can't mean they don't need to work correctly.

[SGaist]

Hi,

Based on the but report, you would need to build the PostgreSQL plugin yourself with the patch applied (taking into account the remark about breaking the backward compatibility) with the Qt version using for the release of PyQt5 you are using.

[Christian Ehrlicher]

The best is to not upgrade to PostgreSQL 12 until the fix is in - or do you really need any feature from PostgreSQL 12?

[skoczian]

I don't see why using QtSql should destroy the separation of GUI and business logic. The controls in the GUI get their data from a QAbstractItemModel subclass. They don't need to know what sort of a model that is: QSqlQueryModel or one of its subclasses or a custom model working with data from a Python database module.

Of course, using QStandardItemModel or a custom model not even the model needs to know about the origin of the data. It could be imported to the model and exported from it in the form of a list of tuples, namedtuples, dicts or whatever other pure Python data structure suggests itself.

I think I will look first if psycopg2 works normally with PostgreSQL 12. If yes, I might change my application to use it. If not, perhaps really return to version 11 for a time.

[terma.aba]

This is giving us a problem, part of our system uses the ability of QSqlTableModel to tell us the columns in a table which we match with properties of objects we are saving. This problem means our customers can't upgrade to PSQL 12. It worked up until PSQL 11.