Re: Aw: Re: Database issues when adding GUI

[Rich Shepard]

On Tue, 8 Jun 2021, Karsten Hilbert wrote:


The problem source is postgres telling me it cannot connect to the database
via TCP/IP
but I can do so directly using psql:
via UNIX domain sockets.


Karsten,

That's because psql is direct while PyQt5-5.12.3 cannot access postgres-12.x
tables. I'm working on getting Qt5-5.15.2 built.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:

2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to accept 
connections
2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128

"


Adrian,

Postgres dropped the pg_attrdev.adsrc column from the system table in
version 12.0. As you wrote yesterday, Qt5 added support for postgres-12 in
the QPSQL driver for 5.15.0. I'm now working on building Qt5-5.15.2 on my
slackware-14.2 host.

While QSqlDatabase is ready to accept connections it cannot recognize tables
using the installed version.

Regards,

Rich

Aw: Re: Database issues when adding GUI

[Karsten Hilbert]

> The problem source is postgres telling me it cannot connect to the database

via TCP/IP

> but I can do so directly using psql:

via UNIX domain sockets.


That makes a difference. See pg_hba.conf.

Karsten

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 6:47 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Rich Shepard wrote:


The only use of 127.0.1.1 is for slrn, a newsreader I've not used in
years.


The thing is this post:

https://www.postgresql.org/message-id/alpine.LNX.2.20.2106071424401.1822%40salmo.appl-ecosys.com

and this:
"2021-06-07 10:36:38.304 PDT [29537] LOG:  starting PostgreSQL 12.7 on 
x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
2021-06-07 10:36:38.304 PDT [29537] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2021-06-07 10:36:38.310 PDT [29537] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2021-06-07 10:36:38.322 PDT [29538] LOG:  database system was shut down 
at 2021-06-07 10:36:17 PDT
2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to 
accept connections
2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does 
not exist at character 128

"

shows you where connecting.




Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rich Shepard wrote:


The only use of 127.0.1.1 is for slrn, a newsreader I've not used in
years.


Ah, I was looking in the wrong directory. /usr/share/postgresql-12/ should
have only the templates. The conf file postgres uses is in
/var/lib/pgsql/12/data/ and has this content:
# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
hostall all 192.168.55.0/24 trust
# IPv6 local connections:
hostall all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication all ::1/128 trust

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Sam Gendler wrote:


It still looks a lot like the salmo hostname is going to be a problem once
you have the client version sorted out. Postgresql is listening only on
127.0.0.1:5432 and pg_hba.conf only has entries for 127.0.0.1:5432, not
127.0.1.1 You can tell it to listen on all addresses with * in
listen_addresses, and then just put the appropriate value in pg_hba.conf
to allow connections on the address you will be using for the user you
will be using. That's in addition to the version mismatch problems you
seem to be having.


Sam,

The only use of 127.0.1.1 is for slrn, a newsreader I've not used in years.

I'm the only user on this system but I'll change listen_addresses to *.

Thanks,

Rich

Re: Database issues when adding GUI

[Sam Gendler]

On Mon, Jun 7, 2021 at 9:24 AM Alan Hodgson 
wrote:

> On Mon, 2021-06-07 at 09:22 -0700, Rich Shepard wrote:
>
>
> salmo, 127.0.0.1 is the server/workstation that has everything installed.
> It
> is localhost.
> 127.0.0.1   localhost.localdomain   localhost
> 127.0.1.1   salmo.appl-ecosys.com   salmo # for slrn
>
>
> Yeah that's your problem. PostgreSQL isn't going to be listening on
> 127.0.1.1
>
> It still looks a lot like the salmo hostname is going to be a problem once
you have the client version sorted out.  Postgresql is listening only on
127.0.0.1:5432 and pg_hba.conf only has entries for 127.0.0.1:5432, not
127.0.1.1 You can tell it to listen on all addresses with  * in
listen_addresses, and then just put the appropriate value in pg_hba.conf to
allow connections on the address you will be using for the user you will be
using.  That's in addition to the version mismatch problems you seem to be
having.

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


The version 12 compatibility commit was done in 5.15 only (5.12 works up
to v11).


David,

Thanks for that information. I'll see if I can upgrade to 5.15.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


I'm guessing qt5-5.12.8 is not recent enough to deal with the changes in
Postgres 11+.


Adrian,

I agree. I've overlooked that PyQt is a wrapper around Qt and that's where
the drivers are installed.


Does your version of Slackware allow you to upgrade the Qt version to
5.15.x?


It should, unless it requires newer versions of libraries such as glibc.
I'll check first.

Thanks,

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 4:19 PM, David G. Johnston wrote:
On Monday, June 7, 2021, Adrian Klaver > wrote:


I'm guessing qt5-5.12.8 is not recent enough to deal with the
changes in Postgres 11+. You might want to ask this on the Qt
list(s). I have tried to determine this, but the Qt repo structure
is lets say interesting and I can't find any relevant information.



https://code.qt.io/cgit/qt/qtbase.git/log/src/plugins/sqldrivers/psql/qsql_psql.cpp 



The version 12 compatibility commit was done in 5.15 only (5.12 works up 
to v11).


Aah, I didn't dig deep enough.



David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[David G. Johnston]

On Monday, June 7, 2021, Adrian Klaver  wrote:

> I'm guessing qt5-5.12.8 is not recent enough to deal with the changes in
> Postgres 11+. You might want to ask this on the Qt list(s). I have tried to
> determine this, but the Qt repo structure is lets say interesting and I
> can't find any relevant information.



https://code.qt.io/cgit/qt/qtbase.git/log/src/plugins/sqldrivers/psql/qsql_psql.cpp

The version 12 compatibility commit was done in 5.15 only (5.12 works up to
v11).

David J.

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 3:58 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Adrian Klaver wrote:



That will show what is currently installed, not what has been installed
over time. The bottom line is that this is Qt issue and to solve it is
going to require working from the Qt side and that is only going to work
by knowing what version of Qt(not PyQt) is being used.


This is the qt version installed: qt5-5.12.8-x86_64-1_SBo. Currently. Now.


So that does not match your current version of PyQt:

PyQt5-5.15.2-x86_64-3


If I understand this:

https://www.riverbankcomputing.com/static/Docs/PyQt5/installation.html#understanding-the-correct-version-to-install

PyQT will downshift to using older libraries then what it 
is built against. PyQt is just a wrapper(binding) to the underlying Qt 
libraries. It is the Qt libraries that have the database drivers, in 
your case QPSQL. I'm guessing qt5-5.12.8 is not recent enough to deal 
with the changes in Postgres 11+. You might want to ask this on the Qt 
list(s). I have tried to determine this, but the Qt repo structure is 
lets say interesting and I can't find any relevant information.


Does your version of Slackware allow you to upgrade the Qt version to 
5.15.x?




If you want to know what prior versions were installed I can send you a 
list

of removed packages.

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:



That will show what is currently installed, not what has been installed
over time. The bottom line is that this is Qt issue and to solve it is
going to require working from the Qt side and that is only going to work
by knowing what version of Qt(not PyQt) is being used.


This is the qt version installed: qt5-5.12.8-x86_64-1_SBo. Currently. Now.

If you want to know what prior versions were installed I can send you a list
of removed packages.

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 3:45 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Adrian Klaver wrote:


Alright. What is the version of Qt installed on the machine. Do in
terminal:


$ ls /var/log/packages/ | grep qt
qt5-5.12.8-x86_64-1_SBo

among others.


That is why asked you to do in a terminal:

qtdiag

That will show what is currently installed, not what has been installed 
over time. The bottom line is that this is Qt issue and to solve it is 
going to require working from the Qt side and that is only going to work 
by knowing what version of Qt(not PyQt) is being used.





Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


Alright. What is the version of Qt installed on the machine. Do in
terminal:


$ ls /var/log/packages/ | grep qt
qt5-5.12.8-x86_64-1_SBo

among others.

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 3:12 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Adrian Klaver wrote:


How did you upgrade? Exact command please.


Adrian,

Because slackware64-14.2 had python3-PyQt-5.13.2 and the latest version in
-current64 is PyQt5-5.15.2-x86_64-3, I removed the former and installed the
latter.

The commands are 'removepkg ' and 'installpkg '.


Alright. What is the version of Qt installed on the machine.
Do in terminal:

qtdiag

That will print out a bunch of stuff, but the Qt version will right at 
the top.




Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


How did you upgrade? Exact command please.


Adrian,

Because slackware64-14.2 had python3-PyQt-5.13.2 and the latest version in
-current64 is PyQt5-5.15.2-x86_64-3, I removed the former and installed the
latter.

The commands are 'removepkg ' and 'installpkg '.

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 3:04 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, David G. Johnston wrote:


If all you did was upgrade the server, and not the client that is issuing
the query, then, yes, you will get the same error, since the server isn't
the problem.


David,

I also upgraded the client from PyQt5-5.13.2 to PyQt5-5.15.2. I'm hoping 
for

an answer there.


How did you upgrade? Exact command please.



Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


If all you did was upgrade the server, and not the client that is issuing
the query, then, yes, you will get the same error, since the server isn't
the problem.


David,

I also upgraded the client from PyQt5-5.13.2 to PyQt5-5.15.2. I'm hoping for
an answer there.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


See:

https://www.postgresql.org/docs/11/catalog-pg-attrdef.html
https://www.postgresql.org/docs/12/catalog-pg-attrdef.html

So in version 12.


Adrian,

Then it doesn't matter which minor version's installed. I've asked on the
PyQt mail list about this because the issue must be with the QPSQL driver.

Thanks,

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 2:26 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

In what version was that removed? Now I have postgresql-12.7 installed and
when I try to access the activitytypes table it still fails with the same
error:



Should have added to my previous post that going from 12.2 --> 12.7 is 
bug patch move. Since version 10 the second number in a version is a 
bug/minor release. In any case moving forward is not going to bring the 
adrsc column back.


As others have stated you need to work on updating the client software, 
it is the part that is the issue here.



Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[David G. Johnston]

On Mon, Jun 7, 2021 at 10:20 AM Rich Shepard 
wrote:

> On Mon, 7 Jun 2021, David G. Johnston wrote:
>
> > Those are PostgreSQL versions...but it's your non-psql client software
> > that needs upgrading, not PostgreSQL.
>
> David,
>
> Ah! Now I understand.
>

Apparently you do not...?
David J.

Re: Database issues when adding GUI

[David G. Johnston]

On Mon, Jun 7, 2021 at 2:26 PM Rich Shepard 
wrote:

> On Mon, 7 Jun 2021, Tom Lane wrote:
>
> > We removed the pg_attrdef.adsrc catalog column a couple versions back.
> > You're evidently using quite an old version of whichever client-side
> > library is issuing this command. You need to get a more up-to-date copy
> > that knows what to do instead.
>
> Tom,
>
> In what version was that removed? Now I have postgresql-12.7 installed and
> when I try to access the activitytypes table it still fails with the same
> error:
>
> 2021-06-07 10:36:38.304 PDT [29537] LOG:  starting PostgreSQL 12.7 on
> x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
> 2021-06-07 10:36:38.304 PDT [29537] LOG:  listening on IPv4 address
> "127.0.0.1", port 5432
> 2021-06-07 10:36:38.310 PDT [29537] LOG:  listening on Unix socket
> "/tmp/.s.PGSQL.5432"
> 2021-06-07 10:36:38.322 PDT [29538] LOG:  database system was shut down at
> 2021-06-07 10:36:17 PDT
> 2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to
> accept connections
> 2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does
> not exist at character 128
> 2021-06-07 14:19:45.491 PDT [31353] STATEMENT:  select
> pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull,
> pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc from
> pg_class, pg_attribute left join pg_attrdef on (pg_attrdef.adrelid =
> pg_attribute.attrelid and pg_attrdef.adnum = pg_attribute.attnum) where
> pg_table_is_visible(pg_class.oid) and pg_class.relname = 'activitytypes'
> and pg_attribute.attnum > 0 and pg_attribute.attrelid = pg_class.oid and
> pg_attribute.attisdropped = false order by pg_attribute.attnum
>
>
If all you did was upgrade the server, and not the client that is issuing
the query, then, yes, you will get the same error, since the server isn't
the problem.

David J.

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 2:26 PM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

In what version was that removed? Now I have postgresql-12.7 installed and
when I try to access the activitytypes table it still fails with the same
error:



See:

https://www.postgresql.org/docs/11/catalog-pg-attrdef.html
https://www.postgresql.org/docs/12/catalog-pg-attrdef.html

So in version 12.


Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

In what version was that removed? Now I have postgresql-12.7 installed and
when I try to access the activitytypes table it still fails with the same
error:

2021-06-07 10:36:38.304 PDT [29537] LOG:  starting PostgreSQL 12.7 on 
x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
2021-06-07 10:36:38.304 PDT [29537] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2021-06-07 10:36:38.310 PDT [29537] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2021-06-07 10:36:38.322 PDT [29538] LOG:  database system was shut down at 
2021-06-07 10:36:17 PDT
2021-06-07 10:36:38.330 PDT [29537] LOG:  database system is ready to accept 
connections
2021-06-07 14:19:45.491 PDT [31353] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128
2021-06-07 14:19:45.491 PDT [31353] STATEMENT:  select pg_attribute.attname, 
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen, 
pg_attribute.atttypmod, pg_attrdef.adsrc from pg_class, pg_attribute left join 
pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and pg_attrdef.adnum = 
pg_attribute.attnum) where pg_table_is_visible(pg_class.oid) and pg_class.relname 
= 'activitytypes' and pg_attribute.attnum > 0 and pg_attribute.attrelid = 
pg_class.oid and pg_attribute.attisdropped = false order by pg_attribute.attnum

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


The code snippet I showed is not tied to PyQt5, it is using psycopg2 and
was just a way of seeing if you could connect to the database via Python
and get results.


Adrian,

Yes, it's a PyQt5 version issue.

Thanks,

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 10:21 AM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Adrian Klaver wrote:


No this is because you did not use an actual connection string e.g:
psycopg2.connect("host=salmo user=rshepard dbname=bustrac")
Instead you used an undefined variable bustrac per the error message:
NameError: name 'bustrac' is not defined


Adrian,

First I'll upgrade 12.2 to 12.7, then I'll upgrade PyQt5.


The code snippet I showed is not tied to PyQt5, it is using psycopg2 and 
was just a way of seeing if you could connect to the database via Python 
and get results.


The issue seems to be with the QT driver for the Postgres. This is 
supplied by QT itself:


https://doc.qt.io/qt-5/sql-driver.html
https://www.riverbankcomputing.com/static/Docs/PyQt5/api/qtsql/qsqldatabase.html

Not sure if a PyQt5 upgrade will by itself do an upgrade of the drivers. 
That may require an update of QT5.




Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:


No this is because you did not use an actual connection string e.g:
psycopg2.connect("host=salmo user=rshepard dbname=bustrac")
Instead you used an undefined variable bustrac per the error message:
NameError: name 'bustrac' is not defined


Adrian,

First I'll upgrade 12.2 to 12.7, then I'll upgrade PyQt5.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, David G. Johnston wrote:


Those are PostgreSQL versions...but it's your non-psql client software
that needs upgrading, not PostgreSQL.


David,

Ah! Now I understand.


Given that the messages above indicate you are presently running 12.2 you
should be upgrading to the current minor release in the v12 branch
regardless.


That's what I thought.

I'll upgrade 12-2 to 12.7, then upgrade PyQt5.

Thanks for clarifying,

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 9:00 AM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Adrian Klaver wrote:

What Philip is suggesting is to use Python only simple script to 
connect to database and retrieve from table. Something like:


import psycopg2

con = psycopg2.connect()
cur = con.cursor()
cur.execute('select * from activitytypes')
rs = cur.fetchall()
print(rs)


Adrian,


con = psycopg2.connect(bustrac)

Traceback (most recent call last):
   File "", line 1, in 
NameError: name 'bustrac' is not defined


No this is because you did not use an actual connection string e.g:

psycopg2.connect("host=salmo user=rshepard dbname=bustrac")

Instead you used an undefined variable bustrac per the error message:

 NameError: name 'bustrac' is not defined


That's because there's a problem with connecting to the database. See my
reply to Rob's message.

Thanks,

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[David G. Johnston]

On Mon, Jun 7, 2021 at 9:36 AM Rich Shepard 
wrote:

> On Mon, 7 Jun 2021, Tom Lane wrote:
>
> > We removed the pg_attrdef.adsrc catalog column a couple versions back.
> > You're evidently using quite an old version of whichever client-side
> > library is issuing this command. You need to get a more up-to-date copy
> > that knows what to do instead.
>
>
> I appear to have a choice: 12.7 or 13.3. Which would be better for me to
> upgrade?
>
>
Those are PostgreSQL versions...but it's your non-psql client software that
needs upgrading, not PostgreSQL.

Given that the messages above indicate you are presently running 12.2 you
should be upgrading to the current minor release in the v12 branch
regardless.

David J.

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command. You need to get a more up-to-date copy
that knows what to do instead.


Tom,

I appear to have a choice: 12.7 or 13.3. Which would be better for me to
upgrade?

Thanks!

Rich

Re: Database issues when adding GUI

[Alan Hodgson]

On Mon, 2021-06-07 at 09:22 -0700, Rich Shepard wrote:
> On Mon, 7 Jun 2021, Edson Carlos Ericksson Richter wrote:
> 
> > Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost
> > should be 127.0.0.1
> > May be an issue in /etc/hosts for "salmo" host?
> 
> Edson,
> 
> salmo, 127.0.0.1 is the server/workstation that has everything installed. It
> is localhost.
> 127.0.0.1   localhost.localdomain   localhost
> 127.0.1.1   salmo.appl-ecosys.com   salmo # for slrn

Yeah that's your problem. PostgreSQL isn't going to be listening on 127.0.1.1

Good catch, Edson.

Re: Database issues when adding GUI

[Tom Lane]

Rich Shepard  writes:
> 2021-06-07 08:47:21.108 PDT [6734] ERROR:  column pg_attrdef.adsrc does not 
> exist at character 128
> 2021-06-07 08:47:21.108 PDT [6734] STATEMENT:  select pg_attribute.attname,
> pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen,
> pg_attribute.atttypmod, pg_attrdef.adsrc from pg_class, pg_attribute left
> join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and
> pg_attrdef.adnum = pg_attribute.attnum) where
> pg_table_is_visible(pg_class.oid) and pg_class.relname = 'industrytypes' and
> pg_attribute.attnum > 0 and pg_attribute.attrelid = pg_class.oid and
> pg_attribute.attisdropped = false order by pg_attribute.attnum

Ah-hah, now we are getting somewhere.  Yes, this seems much more
consistent with your original symptoms, ie app connects but fails
to see any tables.  (Its error reporting is still poor, though.)

> Fixing this error might well fix the issues I'm experiencing; I don't know
> where to start.

We removed the pg_attrdef.adsrc catalog column a couple versions back.
You're evidently using quite an old version of whichever client-side
library is issuing this command.  You need to get a more up-to-date
copy that knows what to do instead.

regards, tom lane

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Edson Carlos Ericksson Richter wrote:


Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost
should be 127.0.0.1
May be an issue in /etc/hosts for "salmo" host?


Edson,

salmo, 127.0.0.1 is the server/workstation that has everything installed. It
is localhost.
127.0.0.1   localhost.localdomain   localhost
127.0.1.1   salmo.appl-ecosys.com   salmo # for slrn

Thanks,

Rich

Re: Database issues when adding GUI

[Edson Carlos Ericksson Richter]

Em 07/06/2021 13:08, Alan Hodgson escreveu:

On Mon, 2021-06-07 at 09:03 -0700, Rich Shepard wrote:
The problem source is postgres telling me it cannot connect to the 
database

but I can do so directly using psql:

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to 
server: Connection refused

Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?



Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost 
should be 127.0.0.1


May be an issue in /etc/hosts for "salmo" host?


Just my 2c,


Edson

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Alan Hodgson wrote:


If you don't specify a host name, psql/libpq connects using the UNIX
domain socket in /tmp. If you do specify a host name it connects using a
TCP socket. Your PostgreSQL doesn't seem to be listening on TCP, or
possibly you have a firewall issue.


Alan,

The firewall is external to this host. It's a Ubiquiti ER-X between the FiOS
and the switch connecting all hosts.

Thanks,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


I think you could clarify things quite a bit by enabling log_connections
and log_statement, and then watching the postmaster log while you run the
app.


Tom,

In /var/log/postgresql-12 the shut-down and restart so an error I don't
understand:

2021-06-07 08:46:51.980 PDT [1455] LOG:  received smart shutdown request
2021-06-07 08:46:51.983 PDT [1455] LOG:  background worker "logical replication 
launcher" (PID 1462) exited with exit code 1
2021-06-07 08:46:51.984 PDT [1457] LOG:  shutting down
2021-06-07 08:46:51.998 PDT [1455] LOG:  database system is shut down
2021-06-07 08:46:55.375 PDT [6708] LOG:  starting PostgreSQL 12.2 on 
x86_64-slackware-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
2021-06-07 08:46:55.375 PDT [6708] LOG:  listening on IPv4 address "127.0.0.1", 
port 5432
2021-06-07 08:46:55.378 PDT [6708] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2021-06-07 08:46:55.394 PDT [6709] LOG:  database system was shut down at 
2021-06-07 08:46:51 PDT
2021-06-07 08:46:55.398 PDT [6708] LOG:  database system is ready to accept 
connections
2021-06-07 08:47:21.108 PDT [6734] ERROR:  column pg_attrdef.adsrc does not 
exist at character 128
2021-06-07 08:47:21.108 PDT [6734] STATEMENT:  select pg_attribute.attname,
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen,
pg_attribute.atttypmod, pg_attrdef.adsrc from pg_class, pg_attribute left
join pg_attrdef on (pg_attrdef.adrelid = pg_attribute.attrelid and
pg_attrdef.adnum = pg_attribute.attnum) where
pg_table_is_visible(pg_class.oid) and pg_class.relname = 'industrytypes' and
pg_attribute.attnum > 0 and pg_attribute.attrelid = pg_class.oid and
pg_attribute.attisdropped = false order by pg_attribute.attnum

Fixing this error might well fix the issues I'm experiencing; I don't know
where to start.

Thanks,

Rich

Re: Database issues when adding GUI

[Tom Lane]

Rich Shepard  writes:
> On Mon, 7 Jun 2021, Tom Lane wrote:
>> What you've got there is that Unix-socket connections work, but TCP
>> connections do not. Check the server's listen_addresses setting. If that
>> says to allow connections on these IP addresses, next check your kernel
>> firewall.

> What I read on  is

pg_hba.conf is exactly not what I told you to check.

The "connection refused" failure implies that you're not getting as
far as where it would look at pg_hba.conf.  If you were, and the
contents of that file were wrong, you'd get a more on-point message.
Hence, you need to look at what is stopping TCP connections from
going through at all.

(Whether this is really the cause of your original problem remains
doubtful to me, but we'll see.)

regards, tom lane

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


What you've got there is that Unix-socket connections work, but TCP
connections do not. Check the server's listen_addresses setting. If that
says to allow connections on these IP addresses, next check your kernel
firewall.


Tom,

What I read on  is
# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASEUSERADDRESS METHOD
hostall all 127.0.0.1/32trust

Here, in /usr/share/postgresql-12/pg_hba.conf I have
# IPv4 local connections:
hostall all 127.0.0.1/32trust

The firewall is on the router, not this server/workstation.

And I did restart postgres after editing pg_hba.conf.

Thanks,

Rich

Re: Database issues when adding GUI

[Tom Lane]

Rich Shepard  writes:
> On Mon, 7 Jun 2021, Tom Lane wrote:
>> Are you sure the app is connecting to the right database?

> The problem source is postgres telling me it cannot connect to the database
> but I can do so directly using psql:

If the app is indeed failing to connect at all, those are some
pretty damn awful error reports.  I'd have thought for example
that

>>> INFO:root:found database

at least indicates that it made a database connection to somewhere.
Thus my suspicion that "somewhere" might be different from where
you're connecting to manually.

I think you could clarify things quite a bit by enabling log_connections
and log_statement, and then watching the postmaster log while you run
the app.

regards, tom lane

Re: Database issues when adding GUI

[Alan Hodgson]

On Mon, 2021-06-07 at 09:03 -0700, Rich Shepard wrote:
> The problem source is postgres telling me it cannot connect to the database
> but I can do so directly using psql:
> 
> $ psql --host salmo --user rshepard --dbname bustrac
> psql: error: could not connect to server: could not connect to server:
> Connection refused
> Is the server running on host "salmo" (127.0.1.1) and accepting
> TCP/IP connections on port 5432?

> 
> yet,
> 
> $ psql bustrac
> psql (12.2)
> Type "help" for help.
> 
> bustrac=#
> 
> I'm thoroughly confused not before encountering this issue.


If you don't specify a host name, psql/libpq connects using the UNIX domain
socket in /tmp. If you do specify a host name it connects using a TCP socket.
Your PostgreSQL doesn't seem to be listening on TCP, or possibly you have a
firewall issue.

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Tom Lane wrote:


Are you sure the app is connecting to the right database?



Tom,

The problem source is postgres telling me it cannot connect to the database
but I can do so directly using psql:

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to server: 
Connection refused
Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?

yet,

$ psql bustrac
psql (12.2)
Type "help" for help.

bustrac=#

I'm thoroughly confused not before encountering this issue.

Regards,

Rich

Re: Database issues when adding GUI

[Tom Lane]

Rich Shepard  writes:
> $ psql --host salmo --user rshepard --dbname bustrac
> psql: error: could not connect to server: could not connect to server: 
> Connection refused
>   Is the server running on host "salmo" (127.0.1.1) and accepting
>   TCP/IP connections on port 5432?
> could not connect to server: Connection refused
>   Is the server running on host "salmo" (192.168.55.1) and accepting
>   TCP/IP connections on port 5432?

> Yet, /tmp has .s.PGSQL.5432= and .s.PGSQL.5432.lock

> and I can access all databases using psql; e.g., 
> $ psql jerrittmine 
> psql (12.2)
> Type "help" for help.

> You're on the right path here. The server is running on salmo (the local
> host) and accepting connections on port 5432. At least, that's how I'm
> seeing it.

What you've got there is that Unix-socket connections work, but
TCP connections do not.  Check the server's listen_addresses
setting.  If that says to allow connections on these IP addresses,
next check your kernel firewall.

regards, tom lane

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Adrian Klaver wrote:

What Philip is suggesting is to use Python only simple script to connect to 
database and retrieve from table. Something like:


import psycopg2

con = psycopg2.connect()
cur = con.cursor()
cur.execute('select * from activitytypes')
rs = cur.fetchall()
print(rs)


Adrian,


con = psycopg2.connect(bustrac)

Traceback (most recent call last):
  File "", line 1, in 
NameError: name 'bustrac' is not defined

That's because there's a problem with connecting to the database. See my
reply to Rob's message.

Thanks,

Rich

Re: Database issues when adding GUI

[Tom Lane]

Rich Shepard  writes:
> Must be something else,

Are you sure the app is connecting to the right database?

regards, tom lane

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


psql --host machine --user role --dbname something
For your app it might be a config file with corresponding entries, or perhaps 
you command line invocation?

I suspect there's a mis-match between the two.


Rob,

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to server: 
Connection refused
Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "salmo" (192.168.55.1) and accepting
TCP/IP connections on port 5432?

Yet, /tmp has .s.PGSQL.5432= and .s.PGSQL.5432.lock

and I can access all databases using psql; e.g., 
$ psql jerrittmine 
psql (12.2)

Type "help" for help.

You're on the right path here. The server is running on salmo (the local
host) and accepting connections on port 5432. At least, that's how I'm
seeing it.

Regards,

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


Sorry, I see they are in public.  Still need you conection setttings, for
both psql and app.  And any ~/.pg* files.


Rob, et al.:

Somehow pg_hba.conf got lost. Only the sample is present in
/usr/share/postgresql-12.

I edited pg_hba.conf set DATABASE and USER to all and METHOD to trust.
Restarted postgres. Application still shows blank window and the log says it
cannot find the table.

Must be something else,

Rich

Re: Database issues when adding GUI

[Adrian Klaver]

On 6/7/21 8:32 AM, Rich Shepard wrote:

On Mon, 7 Jun 2021, Philip Semanchuk wrote:


I agree with Rich. Permissions, or you’re connecting as the wrong user.
You might find it helpful to simplify and not use a GUI application until
you get your connection issues sorted out. A simple command line Python
app that connects to the database and prints the result of “select * from
activitytypes limit 1” will enable quick(er) debugging of your connection
issues.


Philip,

I've used the psql shell for years. That's not a GUI.


What Philip is suggesting is to use Python only simple script to connect 
to database and retrieve from table. Something like:


import psycopg2

con = psycopg2.connect()
cur = con.cursor()
cur.execute('select * from activitytypes')
rs = cur.fetchall()
print(rs)





bustrac=# select * from activitytypes;
   act_name 
  Phone
  Email
  Fax
  Meeting
  Conference
  Referral
  Called me
  Other
(8 rows)

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Database issues when adding GUI

[Rob Sargent]

can you show a psql session which accesses these tables, including
connection string?

Not sure about the 'connection string' part but:

bustrac=# select * from activitytypes;

psql --host machine --user role --dbname something
For your app it might be a config file with corresponding entries, or 
perhaps you command line invocation?

I suspect there's a mis-match between the two.

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Philip Semanchuk wrote:


I agree with Rich. Permissions, or you’re connecting as the wrong user.
You might find it helpful to simplify and not use a GUI application until
you get your connection issues sorted out. A simple command line Python
app that connects to the database and prints the result of “select * from
activitytypes limit 1” will enable quick(er) debugging of your connection
issues.


Philip,

I've used the psql shell for years. That's not a GUI.

bustrac=# select * from activitytypes;
  act_name 


 Phone
 Email
 Fax
 Meeting
 Conference
 Referral
 Called me
 Other
(8 rows)

Rich

Re: Database issues when adding GUI

[Rich Shepard]

On Mon, 7 Jun 2021, Rob Sargent wrote:


This looks like a permissions problem, as though you are connecting as a
role/user without permission to select from the tables.


Rob,

I'm a trusted user for all my databases as I'm the only one using them.

bustrac=# \d
List of relations
 Schema |   Name|   Type   |  Owner 
+---+--+--

 public | activities| table| rshepard
 public | activitytypes | table| rshepard
 public | industrytypes | table| rshepard
 public | locations | table| rshepard
 public | organizations | table| rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people| table| rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table| rshepard
 public | statustypes   | table| rshepard
(10 rows)


Are these tables in a schema other than public.


Nope.


can you show a psql session which accesses these tables, including
connection string?


Not sure about the 'connection string' part but:
bustrac=# select * from activitytypes;
  act_name 


 Phone
 Email
 Fax
 Meeting
 Conference
 Referral
 Called me
 Other
(8 rows)

Did I answer your questions?

Rich

Re: Database issues when adding GUI

[Philip Semanchuk]

> On Jun 7, 2021, at 11:07 AM, Rob Sargent  wrote:
> 
> On 6/7/21 9:02 AM, Rich Shepard wrote:
>> This is an unusual message and I hope someone(s) here can offer insights
>> into the cause of the problem I've been wrestling with for the past couple
>> of weeks.
>> 
>> Context: For the past decade I've run my business tracking database from the
>> psql shell (currently with postgresql-12.2) and decided it's time to add a
>> frontend so it's a stand-alone desktop application. I'm learning to use
>> PyQt5 as the widget set and application framework.
>> 
>> The database contains three lookup tables: activitytypes, industrytypes, and
>> statustypes, all have a single column and few rows. So I've started with
>> these to get python3 and PyQt5-5.13.2 to run the SELECT query and display
>> the results in a QTableView.
>> 
>> The problem with all three is that my code produces an empty window and
>> hangs. There's no python error displayed and the application reports finding
>> the database but not the tables. For example, the activitytypes debugging
>> log contains:
>> INFO:root:found database
>> DEBUG:root:Defining model/view
>> DEBUG:root:model error:  Unable to find table activitytypes
>> DEBUG:root:about to execute select query
>> DEBUG:root:End of Program
>> 
>> I, and others on the python and pyqt mail lists and stackoverflow, can find
>> nothing wrong with the python code. This suggests it's something with the
>> database itself. But I have no idea where to look. The database structure
>> is:
>> bustrac=# \d
>> List of relations
>>  Schema |   Name|   Type   |  Owner 
>> +---+--+--
>>  public | activities| table| rshepard
>>  public | activitytypes | table| rshepard
>>  public | industrytypes | table| rshepard
>>  public | locations | table| rshepard
>>  public | organizations | table| rshepard
>>  public | organizations_org_nbr_seq | sequence | rshepard
>>  public | people| table| rshepard
>>  public | people_person_nbr_seq | sequence | rshepard
>>  public | projects  | table| rshepard
>>  public | statustypes   | table| rshepard
>> (10 rows)
>> 
>> What might stop a front-end application from finding a table that has been
>> readily accessed from the psql shell?
>> 
>> All suggestions and recommendations are needed.
>> 
>> TIA,
>> 
>> Rich
>> 
>> \
> This looks like a permissions problem, as though you are connecting as a 
> role/user without permission to select from the tables.

I agree with Rich. Permissions, or you’re connecting as the wrong user. You 
might find it helpful to simplify and not use a GUI application until you get 
your connection issues sorted out. A simple command line Python app that 
connects to the database and prints the result of “select * from activitytypes 
limit 1” will enable quick(er) debugging of your connection issues.

Cheers
Philip

Re: Database issues when adding GUI

[Rob Sargent]

On 6/7/21 9:02 AM, Rich Shepard wrote:

This is an unusual message and I hope someone(s) here can offer insights
into the cause of the problem I've been wrestling with for the past 
couple

of weeks.

Context: For the past decade I've run my business tracking database 
from the
psql shell (currently with postgresql-12.2) and decided it's time to 
add a

frontend so it's a stand-alone desktop application. I'm learning to use
PyQt5 as the widget set and application framework.

The database contains three lookup tables: activitytypes, 
industrytypes, and

statustypes, all have a single column and few rows. So I've started with
these to get python3 and PyQt5-5.13.2 to run the SELECT query and display
the results in a QTableView.

The problem with all three is that my code produces an empty window and
hangs. There's no python error displayed and the application reports 
finding

the database but not the tables. For example, the activitytypes debugging
log contains:
INFO:root:found database
DEBUG:root:Defining model/view
DEBUG:root:model error:  Unable to find table activitytypes
DEBUG:root:about to execute select query
DEBUG:root:End of Program

I, and others on the python and pyqt mail lists and stackoverflow, can 
find

nothing wrong with the python code. This suggests it's something with the
database itself. But I have no idea where to look. The database structure
is:
bustrac=# \d
    List of relations
 Schema |   Name    |   Type   |  Owner 
+---+--+--

 public | activities    | table    | rshepard
 public | activitytypes | table    | rshepard
 public | industrytypes | table    | rshepard
 public | locations | table    | rshepard
 public | organizations | table    | rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people    | table    | rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table    | rshepard
 public | statustypes   | table    | rshepard
(10 rows)

What might stop a front-end application from finding a table that has 
been

readily accessed from the psql shell?

All suggestions and recommendations are needed.

TIA,

Rich



Sorry, I see they are in public.  Still need you conection setttings, 
for both psql and app.  And any ~/.pg* files.

Re: Database issues when adding GUI

[Rob Sargent]

On 6/7/21 9:02 AM, Rich Shepard wrote:

This is an unusual message and I hope someone(s) here can offer insights
into the cause of the problem I've been wrestling with for the past 
couple

of weeks.

Context: For the past decade I've run my business tracking database 
from the
psql shell (currently with postgresql-12.2) and decided it's time to 
add a

frontend so it's a stand-alone desktop application. I'm learning to use
PyQt5 as the widget set and application framework.

The database contains three lookup tables: activitytypes, 
industrytypes, and

statustypes, all have a single column and few rows. So I've started with
these to get python3 and PyQt5-5.13.2 to run the SELECT query and display
the results in a QTableView.

The problem with all three is that my code produces an empty window and
hangs. There's no python error displayed and the application reports 
finding

the database but not the tables. For example, the activitytypes debugging
log contains:
INFO:root:found database
DEBUG:root:Defining model/view
DEBUG:root:model error:  Unable to find table activitytypes
DEBUG:root:about to execute select query
DEBUG:root:End of Program

I, and others on the python and pyqt mail lists and stackoverflow, can 
find

nothing wrong with the python code. This suggests it's something with the
database itself. But I have no idea where to look. The database structure
is:
bustrac=# \d
    List of relations
 Schema |   Name    |   Type   |  Owner 
+---+--+--

 public | activities    | table    | rshepard
 public | activitytypes | table    | rshepard
 public | industrytypes | table    | rshepard
 public | locations | table    | rshepard
 public | organizations | table    | rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people    | table    | rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table    | rshepard
 public | statustypes   | table    | rshepard
(10 rows)

What might stop a front-end application from finding a table that has 
been

readily accessed from the psql shell?

All suggestions and recommendations are needed.

TIA,

Rich

\
This looks like a permissions problem, as though you are connecting as a 
role/user without permission to select from the tables.

Are these tables in a schema other than public.
can you show a psql session which accesses these tables, including 
connection string?

Database issues when adding GUI

[Rich Shepard]

This is an unusual message and I hope someone(s) here can offer insights
into the cause of the problem I've been wrestling with for the past couple
of weeks.

Context: For the past decade I've run my business tracking database from the
psql shell (currently with postgresql-12.2) and decided it's time to add a
frontend so it's a stand-alone desktop application. I'm learning to use
PyQt5 as the widget set and application framework.

The database contains three lookup tables: activitytypes, industrytypes, and
statustypes, all have a single column and few rows. So I've started with
these to get python3 and PyQt5-5.13.2 to run the SELECT query and display
the results in a QTableView.

The problem with all three is that my code produces an empty window and
hangs. There's no python error displayed and the application reports finding
the database but not the tables. For example, the activitytypes debugging
log contains:
INFO:root:found database
DEBUG:root:Defining model/view
DEBUG:root:model error:  Unable to find table activitytypes
DEBUG:root:about to execute select query
DEBUG:root:End of Program

I, and others on the python and pyqt mail lists and stackoverflow, can find
nothing wrong with the python code. This suggests it's something with the
database itself. But I have no idea where to look. The database structure
is:
bustrac=# \d
List of relations
 Schema |   Name|   Type   |  Owner 
+---+--+--

 public | activities| table| rshepard
 public | activitytypes | table| rshepard
 public | industrytypes | table| rshepard
 public | locations | table| rshepard
 public | organizations | table| rshepard
 public | organizations_org_nbr_seq | sequence | rshepard
 public | people| table| rshepard
 public | people_person_nbr_seq | sequence | rshepard
 public | projects  | table| rshepard
 public | statustypes   | table| rshepard
(10 rows)

What might stop a front-end application from finding a table that has been
readily accessed from the psql shell?

All suggestions and recommendations are needed.

TIA,

Rich