`KeyError` on transactions with foreign keys

[dbeecham]

Description

Running pgcli version 2.2.0, installed through nix:

$ nix run nixpkgs.pgcli
$ pgcli --version
Version: 2.2.0

The postgres server is version 11.5 (Debian 11.5-1.pgdg90+1) which I'm running through docker.

I created a new database, and I ran this query:

begin;

create table users (
    user_email varchar(128) not null primary key
);

create table tags (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    primary key (user_email, tag)
);

create table entry_types (
    user_email varchar(128) not null references users(user_email),
    entry_type varchar(128) not null,
    primary key (user_email, entry_type)
);

create table positions (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    entry_type varchar(128) not null,
    timestamp timestamp not null,
    foreign key (user_email) references users(user_email),
    foreign key (user_email, entry_type) references entry_types(user_email, entry_type),
    primary key (user_email, timestamp)
);

commit;

Then I got this error:

Traceback (most recent call last):
  File "/nix/store/d89hv7fvmky59qqgzb1yiwagv34i1q75-python3-3.7.5/lib/python3.7/threading.py", line 926, in _bootstrap_inner
[... a bit of garbage because of cli overwriting error...]
  File "/nix/store/d89hv7fvmky59qqgzb1yiwagv34i1q75-python3-3.7.5/lib/python3.7/threading.py", line 870, in run
    self._target(*self._args, **self._kwargs)
  File "/nix/store/gj1a2x73rf4b0zw6y92barym9pfhvx7s-pgcli-2.2.0/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
    refresher(completer, executor)
  File "/nix/store/gj1a2x73rf4b0zw6y92barym9pfhvx7s-pgcli-2.2.0/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 110, in refresh_tables
    completer.extend_foreignkeys(executor.foreignkeys())
  File "/nix/store/gj1a2x73rf4b0zw6y92barym9pfhvx7s-pgcli-2.2.0/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 278, in extend_foreignkeys
    childcolmeta = meta[childschema][childtable][childcol]
KeyError: 'entry_types'

Since I'm not installing pgcli through pip, the pip freeze output is irrelevant - pgcli is not going to use those libraries. But this command:

$ strace -e trace=file pgcli 2>&1 | cut -d '"' -f 2 | cut -d '/' -f "1-4" | grep "/" | sort | uniq

Gives a pretty good idea of libraries used and file paths touched:

/dev/tty
/etc/ld-nix.so.preload
/etc/localtime
/etc/nsswitch.conf
/etc/passwd
/home
/home/dbe
/home/dbe/.config
/home/dbe/.pgclirc
/home/dbe/.pgpass
/nix
/nix/store
/nix/store/03c4106zarkpdrf2yscs9f1ph4rnlpcx-python3.7-singledispatch-3.4.0.3
/nix/store/2x3rzl2181qgfgdcbin1fqfk0m9nb661-python3.7-psycopg2-2.8.4
/nix/store/3cz3xgnghkpy41ab9hcj7011sqbrpg4n-ncurses-6.1-20190112
/nix/store/46bbd4s1p40q3f8hzlqk9niqa9zsw5wi-python3.7-click-7.0
/nix/store/4vv0b5qch3b4n4nwsxabl13crk4rc8vk-openssl-1.1.1d
/nix/store/6bz6zqhsbbgcjdxps792a3754ql4hhq4-xz-5.2.4
/nix/store/6w52i29bpdmi3x0h5w4a5cinm3l4g8yv-python3.7-jeepney-0.4.1
/nix/store/7ydp5qhy2pj8ymlqr8qmvr1wdy1dl0b4-python3.7-pygobject-3.32.1
/nix/store/88b5jfiywj0bfqz2qv0fml2p1kljw62n-python3.7-wcwidth-0.1.7
/nix/store/8mg3cjnnjvhdv0ixxf9vg208fkhs9irp-python3.7-configobj-5.0.6
/nix/store/95m314hwvmldzarwyfckc3r7y8s9m2ba-python3.7-entrypoints-0.3
/nix/store/9dv2hymh9a1w568vqzvy4yq1b37v60rp-python3.7-sqlparse-0.3.0
/nix/store/9w4llsiixfyc3q66fqdxgc4m2f608ijj-python3.7-Pygments-2.4.2
/nix/store/arjlvns0ahabnskdqxavyj9rpa8nghb6-python3.7-cli_helpers-1.2.1
/nix/store/bk68i8li3n3h1772znw70cdxnqyvjmzl-python3.7-certifi-2019.9.11
/nix/store/brbfl68gm56aqrfa43ffrbhx46qxqhlm-python3.7-docutils-0.15.2
/nix/store/c9q465hrmcwlxal44gl4qn0n221lza2s-python3.7-setproctitle-1.1.10
/nix/store/d89hv7fvmky59qqgzb1yiwagv34i1q75-python3-3.7.5
/nix/store/f7n6lgyxg0l4p67zpdh52wlx3xgr728r-bzip2-1.0.6.0.1
/nix/store/ff824gxsp05l44gi35mfgqadvha3fp60-python3.7-cryptography-2.8
/nix/store/g5rhq6rcwhsn3kv6nx1n5g4lwfg21y1g-python3.7-packaging-19.2
/nix/store/gj1a2x73rf4b0zw6y92barym9pfhvx7s-pgcli-2.2.0
/nix/store/gn6mp6xch4vk0ks1vazyqm03v7mz2v0y-python3.7-tabulate-0.8.6
/nix/store/jls9vdr32nmhp17xm0igk0fw9plq883b-python3.7-dbus-python-1.2.12
/nix/store/kh3ipi40nja6hw3k25s8bnqlpny9hiiv-postgresql-11.6-lib
/nix/store/klhkh53svwvzxi14yh23r8n06x0wcbz6-python3.7-humanize-0.5.1
/nix/store/kvjb1rvvb29y83j4pnms2q50qk1q68ah-python3.7-cffi-1.13.2
/nix/store/p4hhm3lgi7qa4kiv68wppflxf6yfhl6n-python3.7-pgspecial-1.11.9
/nix/store/r9mn29i5wxflwscrvcbjck452dmlhcic-python3.7-tornado-5.1
/nix/store/r9r668v95455vglg75nsyvn0idx77css-python3.7-prompt_toolkit-2.0.10
/nix/store/sf2qjfd4dygh4cl4jgr62bjbplcjzzn2-python3.7-secretstorage-3.1.1
/nix/store/sm7gvn5x104cmqh26lpszds9azqjp2mj-python3.7-keyring-19.2.0
/nix/store/ssmz9r19xfqd1hmymy9rda3chmg3wvry-python3.7-pycairo-1.18.2
/nix/store/v8qv6zzqhi34y2hkppp3dx36fb7njppf-python3.7-backports_abc-0.5
/nix/store/wxs2das9y77fiwn4fwvzccgiajyzk7qd-python3.7-pyparsing-2.4.5
/nix/store/xhpwab5kavygbr1fswawmdyqvmn3wa4i-glibc-2.27
/nix/store/y19x72fgsb62bb0qr6x1pbhvl7hr624w-python3.7-terminaltables-3.1.0
/nix/store/ylhic97zbw8qsarzg5a26abgpqhi8a9v-python3.7-six-1.12.0
/nix/store/ylpi31dwsgm9c9rndp0j5b4na749d97z-zlib-1.2.11
/nix/store/zdl0hjgkyiclzzgdi0q4z2zm78zdvnif-libffi-3.3
/nix/store/zmc43wsgwg8ms0l3vjg1dlfh08y59x3z-python3.7-pycparser-2.19
/proc/mounts
/proc/self/status
/run/postgresql/.s.PGSQL.5432
/selinux

Interesting addition; if I change the query to this:

begin;

create table tags (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    primary key (user_email, tag)
);

create table positions (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    timestamp timestamp not null,
    primary key (user_email, timestamp)
);

commit;

Then, instead, I get this error:

KeyError: 'positions'

So I'm guessing pgcli just has issues with transactions?

Your environment

• [X] Please provide your OS and version information.
• [X] Please provide your CLI version.
• [X] What is the output of pip freeze command.

[dbeecham]

This works:

begin;

create table users (
    user_email varchar(128) not null primary key
);

create table tags (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    primary key (user_email, tag)
);

commit;

But this does not:

begin;

create table tags (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    primary key (user_email, tag)
);

create table positions (
    user_email varchar(128) not null,
    tag varchar(128) not null,
    timestamp timestamp not null,
    foreign key (user_email, tag) references tags(user_email, tag),
    primary key (user_email, timestamp)
);

commit;

I think the issue is that if you create a transaction query with two tables and a foreign key relationship between them, you get a KeyError of the table with the foreign keys.

[dbaty]

I cannot reproduce this issue with the latest version (version 3.5.0). Tables are successfully created.

Output of `pip freeze`

cli-helpers==2.3.0
click==8.1.7
configobj==5.0.8
pendulum==2.1.2
pgcli==3.5.0
pgspecial==2.1.0
prompt-toolkit==3.0.39
psycopg==3.1.12
Pygments==2.16.1
python-dateutil==2.8.2
pytzdata==2020.1
setproctitle==1.3.2
six==1.16.0
sqlparse==0.4.4
tabulate==0.9.0
typing_extensions==4.8.0
wcwidth==0.2.6

Shall we close this issue?

[j-bennet]

@dbaty Yes, let's close. Thank you.