Database issues when adding GUI

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?

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.

On Jun 7, 2021, at 11:07 AM, Rob Sargent <robjsargent@gmail.com> 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

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

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

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.

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(<connection_str>)
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.klaver@aklaver.com

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

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

Rich Shepard <rshepard@appl-ecosys.com> writes:

Must be something else,

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

regards, tom lane

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(<connection_str>)
cur = con.cursor()
cur.execute('select * from activitytypes')
rs = cur.fetchall()
print(rs)

Adrian,

con = psycopg2.connect(bustrac)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
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

Rich Shepard <rshepard@appl-ecosys.com> 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

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

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.

Rich Shepard <rshepard@appl-ecosys.com> 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

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 <https://www.postgresql.org/docs/13/auth-pg-hba-conf.html&gt; is
# The same using local loopback TCP/IP connections.
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 trust

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

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

And I did restart postgres after editing pg_hba.conf.

Thanks,

Rich

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

Rich Shepard <rshepard@appl-ecosys.com> 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 <https://www.postgresql.org/docs/13/auth-pg-hba-conf.html&gt; 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

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