Thread: 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
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 thetables. I agree with Rich. Permissions, or you’re connecting as the wrong user. You might find it helpful to simplify and not usea GUI application until you get your connection issues sorted out. A simple command line Python app that connects to thedatabase and prints the result of “select * from activitytypes limit 1” will enable quick(er) debugging of your connectionissues. 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 databasebut I can do so directly using psql:$ psql --host salmo --user rshepard --dbname bustracpsql: error: could not connect to server: could not connect to server: Connection refusedIs the server running on host "salmo" (127.0.1.1) and acceptingTCP/IP connections on port 5432?
yet,$ psql bustracpsql (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> 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 code1 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> 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
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
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
Rich Shepard <rshepard@appl-ecosys.com> 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
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, localhostshould be 127.0.0.1May be an issue in /etc/hosts for "salmo" host?Edson,salmo, 127.0.0.1 is the server/workstation that has everything installed. Itis localhost.127.0.0.1 localhost.localdomain localhost127.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.
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
On Mon, Jun 7, 2021 at 9:36 AM Rich Shepard <rshepard@appl-ecosys.com> 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.
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(<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
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.klaver@aklaver.com
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
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
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.klaver@aklaver.com
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
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 joinpg_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
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.klaver@aklaver.com
On Mon, Jun 7, 2021 at 2:26 PM Rich Shepard <rshepard@appl-ecosys.com> 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.
On Mon, Jun 7, 2021 at 10:20 AM Rich Shepard <rshepard@appl-ecosys.com> 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.
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.klaver@aklaver.com
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
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
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.klaver@aklaver.com
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 <packagename>' and 'installpkg <packagename>'. Rich
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 <packagename>' and 'installpkg <packagename>'. 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.klaver@aklaver.com
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
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.klaver@aklaver.com
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
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<some_version> 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.klaver@aklaver.com
On Monday, June 7, 2021, Adrian Klaver <adrian.klaver@aklaver.com> 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.
The version 12 compatibility commit was done in 5.15 only (5.12 works up to v11).
David J.
On 6/7/21 4:19 PM, David G. Johnston wrote: > On Monday, June 7, 2021, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> 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 > <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.klaver@aklaver.com
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
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
On Mon, Jun 7, 2021 at 9:24 AM Alan Hodgson <ahodgson@lists.simkin.ca> 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. Itis localhost.127.0.0.1 localhost.localdomain localhost127.0.1.1 salmo.appl-ecosys.com salmo # for slrnYeah 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.
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
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 DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 192.168.55.0/24 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust Rich
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.klaver@aklaver.com
> 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
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
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