9

I'm using pgAdmin 4 on a Debian OS. It was working fine about a month ago but today it the SQL Query Tool gives a cryptic error message: 'table_oid'. The "View/Edit Data" option in the right-click menu works fine as well as running queries from the psql command line. I tried dropping the server connection and restarting it to no avail

The pgadmin4.log file has this error statement several times:

2019-07-26 16:12:17,304: ERROR  pgadmin:    'table_oid'
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/lib/python3/dist-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/lib/python3/dist-packages/flask_login/utils.py", line 228, in decorated_view
    return func(*args, **kwargs)
  File "/usr/share/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py", line 432, in poll
    trans_obj.check_updatable_results_pkeys()
  File "/usr/share/pgadmin4/web/pgadmin/tools/sqleditor/command.py", line 899, in check_updatable_results_pkeys
    is_query_resultset_updatable(conn, sql_path)
  File "/usr/share/pgadmin4/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py", line 38, in is_query_resultset_updatable
    table_oid = _check_single_table(columns_info)
  File "/usr/share/pgadmin4/web/pgadmin/tools/sqleditor/utils/is_query_resultset_updatable.py", line 61, in _check_single_table
    table_oid = columns_info[0]['table_oid']
KeyError: 'table_oid'

This error has set me back about two hours. Any help would be greatly appreciated!

4 Answers 4

6

A ticket about this error exists in the postgres bugtracker.
The problem is due to python3-psycopg2. The latest pgadmin4 version requires psycopg2 2.8. But if you're on Debian/Ubuntu stable, apt installed v2.7.
So we need to add the latest version with pip :

sudo pip3 install -U psycopg2

Then add the /usr/local/lib in the pgadmin PythonPath (and hope it'll be first in the path):

nano ~/.config/pgadmin/pgadmin4.conf # or with a right click on the system tray icon
# add /usr/local/lib/python3.7/dist-packages to the PythonPath.

You'll have to change this path if you're using another version of Python.
Hope this helps.

Edit (after reading Quique response):
This is not a problem if you have two versions of psycopg2 ; the v2.8 will only be accessible through /usr/local/lib.
If you try to uninstall the v2.7 debian package, you may have some issues with other packages that depends on it (QGIS for example).

4
  • @vdlb thanks a lot for your help! It took sometime to get it working again but everything is good now! Jul 28, 2019 at 3:55
  • @vidlb can you please explain what full path to ~/.config?
    – humkins
    Aug 9, 2019 at 6:21
  • 1
    @gumkins i'm not sure what you're asking.. the full path is "/home/$USER/.config/pgadmin/pgadmin4.conf"
    – vidlb
    Aug 20, 2019 at 8:47
  • @vidlb Thank you!
    – humkins
    Aug 22, 2019 at 11:15
5

vidlb is spot on, I will just add a couple of details:

  1. In order to install psycopg2 2.8 using, you must first uninstall the psycopg2 debian package, which will also uninstall pgadmin4.
sudo apt-get remove --purge psycopg2
sudo pip3 install -U psycopg2
sudo apt install pgadmin4
  1. The easiest way to update the Python Path is to click with the mouse right button on the pgadmin system tray icon, and then select Configure... In the Python tab, fill the Python Path box with /usr/local/lib/python3.6/dist-packages (or the correct path for your system). Alternatively, you can edit the ~/.config/pgadmin/pgadmin4.conf to look something like
[General]
ApplicationPath=
BrowserCommand=
FixedPort=false
PortNumber=1
PythonPath=/usr/local/lib/python3.6/dist-packages
2

Thanks a lot vidlb and Quique!

As vidlb stated above, there is a currently a bug pertaining to the psycopg2 package for pgadmin4. If any one is having trouble with this issue on Debain and Ubuntu please refer to link below on this bug. Warning you have to have an account with Redmine before veiwing the status report.

Bug#4520

Note #7 in particular has a quick fix for Debian and Ubuntu users. I also updated from stretch to buster because stretch doesn't have apt support for Python 3.7 and I didn't feel like installing the Python updates manually.

Everything works fine now, cheers!

2

PgAdmin 4.11 now relies on psycopg2-binary, not psycopg2. You don't need to uninstall 2.7 for 2.8, just:

pip3 install --user psycopg2-binary
1
  • This worked for me, but I still had to configure the pgadmin4 pythonpath to /usr/local/lib/python3.7/dist-packages as per the additional step in vidlb's answer
    – Tedd
    Jul 31, 2019 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.