PostgreSQL › PostgreSQL - general

Database issues when adding GUI

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

56 messages Options

1 2 3

Rich Shepard

Database issues when adding GUI

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

Re: 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?
>
>

lup

Re: Database issues when adding GUI

In reply to this post by Rich Shepard

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

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

Philip Semanchuk-2

Re: Database issues when adding GUI

In reply to this post by lup

> 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

Rich Shepard

Re: Database issues when adding GUI

In reply to this post by lup

Rich Shepard

Re: Database issues when adding GUI

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

Re: Database issues when adding GUI

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.

Adrian Klaver-4

Re: Database issues when adding GUI

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()
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

Rich Shepard

Re: Database issues when adding GUI

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

Rich Shepard

Re: Database issues when adding GUI

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

Tom Lane-2

Re: Database issues when adding GUI

In reply to this post by Rich Shepard

Rich Shepard < > writes:
> Must be something else,

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

regards, tom lane

Rich Shepard

Re: Database issues when adding GUI

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()
> 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

Tom Lane-2

Re: Database issues when adding GUI

In reply to this post by Rich Shepard

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

Rich Shepard

Re: Database issues when adding GUI

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

Alan Hodgson-3

Re: Database issues when adding GUI

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.

Tom Lane-2

Re: Database issues when adding GUI

In reply to this post by Rich Shepard

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

Rich Shepard

Re: Database issues when adding GUI

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

Rich Shepard

Re: Database issues when adding GUI

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

Tom Lane-2

Re: Database issues when adding GUI

In reply to this post by Rich Shepard

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

Rich Shepard

Re: Database issues when adding GUI

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

1 2 3