erroneous postgis query when trying to discover relations

[beerockxs]

When I try to discover relations between my tables, I get this warning in the PostGIS message log:

Erroneous query: WITH foreign_keys AS ( SELECT c.conname, c.conrelid, c.confrelid, unnest(c.conkey) AS conkey, unnest(c.confkey) AS confkey, (SELECT relname FROM pg_catalog.pg_class WHERE oid = c.conrelid) as referencing_table, (SELECT relname FROM pg_catalog.pg_class WHERE oid = c.confrelid) as referenced_table, (SELECT relnamespace::regnamespace::text FROM pg_catalog.pg_class WHERE oid = c.confrelid) as constraint_schema FROM pg_constraint c WHERE contype = 'f' AND c.conrelid::regclass = '.'::regclass ) SELECT fk.conname as constraint_name, a.attname as column_name, fk.constraint_schema, referenced_table as table_name, af.attname as column_name, fk.confkey as ordinal_position FROM foreign_keys fk JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid ORDER BY fk.confrelid, fk.conname ; returned 7 [ERROR: invalid name syntax
LINE 1: ...ERE contype = 'f' AND c.conrelid::regclass = '.'::regcl...
^
]

QGIS version	3.15.0-Master	QGIS code revision	3119eb7
Compiled against Qt	5.11.2	Running against Qt	5.11.2
Compiled against GDAL/OGR	3.2.0dev	Running against GDAL/OGR	3.2.0dev
Compiled against GEOS	3.8.1-CAPI-1.13.3	Running against GEOS	3.8.1-CAPI-1.13.3
Compiled against SQLite	3.29.0	Running against SQLite	3.29.0
PostgreSQL Client Version	11.5	SpatiaLite Version	4.3.0
QWT Version	6.1.3	QScintilla2 Version	2.10.8
Compiled against PROJ	7.2.0	Running against PROJ	Rel. 7.2.0, November 1st, 2020
OS Version	Windows 10 (10.0)	This copy of QGIS writes debugging output.
Active python plugins	SpreadsheetLayers; db_manager; MetaSearch; processing

[elpaso]

I'm looking into it, the odd thing is that the warning is triggered by empty table and empty schema names.
I could add a check to skip that code if table name and schema name are blank (and suppress the warning) but I wonder how could it possibly end up in that situation.

Is there a chance you could share a SQL DB dump (without any data, schema only is ok) and be more precise about the steps to make to reproduce this situation?

[beerockxs]

I unfortunately can't share the database schema. I do have multiple schemes in the same database, if that is any help in diagnosing the issue.

[elpaso]

I tested it with multiple schemas but I cannot reproduce.

[beerockxs]

I still get this with QGIS Master 7f8c036:

WITH foreign_keys AS ( SELECT c.conname, c.conrelid, c.confrelid, unnest(c.conkey) AS conkey, unnest(c.confkey) AS confkey, (SELECT relname FROM pg_catalog.pg_class WHERE oid = c.conrelid) as referencing_table, (SELECT relname FROM pg_catalog.pg_class WHERE oid = c.confrelid) as referenced_table, (SELECT relnamespace::regnamespace::text FROM pg_catalog.pg_class WHERE oid = c.confrelid) as constraint_schema FROM pg_constraint c WHERE contype = 'f' AND c.conrelid::regclass = '"".""'::regclass ) SELECT fk.conname as constraint_name, a.attname as column_name, fk.constraint_schema, referenced_table as table_name, af.attname as column_name, fk.confkey as ordinal_position FROM foreign_keys fk JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid ORDER BY fk.confrelid, fk.conname ; returned 7 [ERROR: schema "" does not exist
LINE 1: ...ERE contype = 'f' AND c.conrelid::regclass = '"".""'::r...

[elpaso]

@beerockxs still cannot reproduce, can you at least share the project without data?

[beerockxs]

I did some debugging and removed layers until I could isolate the layer causing the issue, it's an SQL layer that loads features from a postgresql function and joins another table to it. I had to create a function for this because I needed some plpgsql and couldn't just use normal SQL to generate my geometry.

[elpaso]

I did some debugging and removed layers until I could isolate the layer causing the issue, it's an SQL layer that loads features from a postgresql function and joins another table to it. I had to create a function for this because I needed some plpgsql and couldn't just use normal SQL to generate my geometry.

A query layer was my main suspect too, that's why I asked for the project. I think we can silence the warning by checking if tableName and schemaName are availble for a particular layer.