Database issues when adding GUI

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
56 messages Options
123
Reply | Threaded
Open this post in threaded view
|

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




lup
Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

lup
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?
>
>



lup
Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

lup
In reply to this post by Rich Shepard
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.




Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Philip Semanchuk-2
In reply to this post by lup


> On Jun 7, 2021, at 11:07 AM, Rob Sargent <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by lup
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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Philip Semanchuk-2
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


lup
Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

lup
In reply to this post by Rich Shepard

>> 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.


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Adrian Klaver-4
In reply to this post by Rich Shepard
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by lup
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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by lup
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



Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Tom Lane-2
In reply to this post by Rich Shepard
Rich Shepard <[hidden email]> writes:
> Must be something else,

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

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Adrian Klaver-4
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



Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Tom Lane-2
In reply to this post by Rich Shepard
Rich Shepard <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Alan Hodgson-3
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.

Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Tom Lane-2
In reply to this post by Rich Shepard
Rich Shepard <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Tom Lane-2
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> 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



Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Tom Lane-2
In reply to this post by Rich Shepard
Rich Shepard <[hidden email]> 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> 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


Reply | Threaded
Open this post in threaded view
|

Re: Database issues when adding GUI

Rich Shepard
In reply to this post by Alan Hodgson-3
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


123