Database does not appear

[apiszcz]

Windows 10
PostGres 12.2
PostGIS 3.0
pgrouting 3.0.0-rc1
pgroutinglayer 3.0.0
QGIS 3.12

The database is loaded with OSM and works and display the layer properly in QGIS.
The pgroutinglayer plugin Database selection widget is blank.
The database has a username and password, however it is not on the standard postges port 5432.

Is there a configuration requirement for the connection? The connection appears correctly in the QGIS Browser.

Quickly reviewed the connectors/postgis.py, and am wondering the issue is related to this code not acquiring active database connections.

Thank you.

[cayetanobv]

Hi @apiszcz ,

This plugin uses all PostgreSQL connections you have added to QGIS so the port is not a problem. The issue could be you don't have a pgRouting database with pgRouting extension created. You could check what you need in pgRouting docs: http://docs.pgrouting.org/latest/en/pgRouting-concepts.html#create-a-routing-database

Thanks,

[apiszcz]

The following command works from PSQL with proper values in the place of id1, id2, id3 SELECT * FROM pgr_dijkstra( 'SELECT gid as id, source, target, cost, reverse_cost FROM ways', id1, array[id2 id3], directed := False ); == Another observation is the DB Manager Info View has a warning. Unsure if this means anything for the plugin. When selecting 'ways' No primary key defined for this table. Table and preview tabs work and display the data.

[apiszcz]

osm1=# select * from pgr_version();
 pgr_version
-------------
 3.0.0-rc1
(1 row)

[cayetanobv]

This plugin was developed with last stable (v2.6) but taking into account compatibility with dev version. We need to recheck that again. Did you try with v2.6?

[apiszcz]

Hi No. If there is anything to turn on trace/debug/etc. to review/display issues please let me know. While many things may have improved throughout the versions the database connection seems like something that would be 'farily' consistent. However I have located others in 2017 with the same question, not sure what their setup was though.

…

On Mon, Mar 9, 2020 at 7:31 AM Cayetano Benavent ***@***.***> wrote: This plugin was developed with last stable (v2.6) but taking into account compatibility with dev version. We need to recheck that again. Did you try with v2.6? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#126?email_source=notifications&email_token=AAK5KTMJOMJJABIII7TEP53RGTHSZA5CNFSM4LD23E4KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEOGXNNY#issuecomment-596473527>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAK5KTOTLO3GAYPDD6IZTZTRGTHSZANCNFSM4LD23E4A> .

[apiszcz]

postgis.py seems to capturing the parameters

2020-03-09T14:09:36     WARNING    SERVICE: , HOST: localhost, PORT: 5444, DBNAME: osm1, USER: postgres
        self.service = uri.service()
        self.host = uri.host()
        self.port = uri.port()
        self.dbname = uri.database()
        self.user = uri.username()
        self.passwd = uri.password()
        QgsMessageLog.logMessage("SERVICE: %s, HOST: %s, PORT: %s, DBNAME: %s, USER: %s"%(self.service,self.host,self.port,self.dbname,self.user), "PGR_DEBUG")

Connection string looks good

2020-03-09T14:17:32 WARNING con_str host='localhost' port=5444 dbname='osm1' user='postgres' password='pg'

[apiszcz]

Problem #1 with FIX

pgRoutingLayer_utils::getPgrVersion(con):
WAS
select version from pgr_version();

NOW
select pgr_version from pgr_version();

osm=# select version from pgr_version();
ERROR:  column "version" does not exist
LINE 1: select version from pgr_version();
               ^

osm=# select pgr_version from pgr_version();
 pgr_version
-------------
 3.0.0-rc1

[apiszcz]

The change described above allows me to connect to the database.

[cvvergara]

SELECT version FROM pgr_full_version();

This needs to be fixed

[tjukanovt]

I am facing the same issue with almost identical setup. Did you @apiszcz just edit the .py file directly in your plugin profiles folder and then reloaded it? I did that but still can't get the db connection to show.

[apiszcz]

Yes.
Be sure you connect to the database with the QGIS Browser, PostGres.

[tjukanovt]

Yes I have connection established already and accessible through the QGIS Browser.

Also changed this line:
cur.execute('SELECT pgr_version FROM pgr_version()')
But still not working.

[cOsprey]

I tried to follow and changed but still I'm having the issue.

[apiszcz]

1. Review QGIS->View->Message Logs
2. Review postgres log file for connection errors.
   The 'SELECT pgr_version FROM pgr_version()' command works ok from PSQL?

[tjukanovt]

Checked the pgRoutinLayer logs and it seems that the password authentication fails:

2020-04-02T15:22:35 INFO dbname:gisdata, FATAL: password authentication failed for user "postgres"
password retrieved from file "C:\Users\tjukanovt\AppData\Roaming/postgresql/pgpass.conf"

But when I check the pgpass file, it seems that the password (which is stored as plain text 🤨) is correct. And also like I said, normal authentication to the same database works in QGIS.

The query
SELECT pgr_version FROM pgr_version()
does work and returns pgr_version
3.0.0-rc1

[tjukanovt]

Now I got this working. If someone else faces the same issue: in the pgpass.conf file for some reason the text is in small letters although my credentials included capitals. For normal QGIS DB connection this is not a problem, but I changed the pgpass password manually and now the connection was succesful!

[dkastl]

Thanks for reporting your solution @tjukanovt !
Can others in this thread confirm that it was caused by the password?

[cayetanobv]

Hi all,

Fixed pgr_version issue to support v3 with #128

Regard password issue, I don't know why are occurring that. I know this side of the code is old so it needs a little bit of refactoring to better handling of new QGIS API :)

[Langlaeufer]

I have the same Issue
Postgres 12.3
PostGIS 3.0.1
QGIS 3.12.3 / 3.14.0
PgRoutingLayer 3.0.0

From pgRoutingLayer Protokoll
2020-06-26T11:15:26 INFO startup version 2.6
2020-06-26T11:15:45 INFO dbname:routing, fe_sendauth: no password supplied

layer is available in QGIS
username and password in pgpass.conf are correct

[darrencope]

I'm still seeing the original issue. Is it expected to be fixed in the current release?

The pgRoutingLayer log in QGIS says:
CRITICAL dbname:ncr_routing, connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied

I am able to run pgRouting queries in both pgAdmin and DB Manager plugin in QGIS with no problem, so the connection is fine, and everything appears to be up and running from the perspective of both QGIS and pgAdmin. It's just the pgRoutingLayer plug that seems to have the connection issue. I don't see pgpass.conf anywhere on my system, so perhaps this has changed in newer versions?

[sanak]

Hi @darrencope,
Thanks for reporting the issue!

If possible, could you share your current environment ?

• OS:
• PostgreSQL version:
• PostGIS version:
• pgRouting version:
• QGIS version:
• pgRoutingLayer version: (I think that you are using the latest (3.0.2).)

Thanks,

[darrencope]

@sanak Of course!

• OS: Windows 11 Home
• PostgreSQL: PostgreSQL 14.5, compiled by Visual C++ build 1914, 64-bit
• PostGIS: POSTGIS="3.3.1 3.3.1" [EXTENSION] PGSQL="140" GEOS="3.11.0-CAPI-1.17.0" PROJ="7.2.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
• pgRouting: 3.3.4
• QGIS: 3.22.11-Białowieża
• pgRoutingLayer: 3.0.2

[sanak]

@darrencope Thanks for the information!
I will take a look in this weekend with similar environment as much as possible.