table_format for single screen outputs

[wchao]

Description

I use pspg and table_format csv in pgcli. This works well for long result sets with lots of rows (more than one page or screen). I pass --quit-if-one-screen to the pspg pager. The problem is that when the result set is only one page, pspg quits and I am left with output in csv rather than in pretty tabular format.

The desired solution would be to have one table format when results get passed to pspg and another table format for single screen outputs so that pgcli can format the output in csv for pspg when needed, or in tabular format with nice unicode lines when the output is single screen and no pspg is needed.

Your environment

CentOS 8
Python 3.6
pgcli 3.0.0
pspg 3.1.2

pip freeze:
ansiwrap==0.8.4
asteval==0.9.18
Babel==2.8.0
certifi==2020.6.20
cffi==1.14.2
chardet==3.0.4
cli-helpers==2.1.0
click==7.1.2
colorama==0.4.3
configobj==5.0.6
cryptography==3.0
cycler==0.10.0
dbus-python==1.2.16
decorator==4.4.2
distro==1.5.0
fail2ban==0.11.1
future==0.18.2
gkeep==0.2.1
gkeepapi==0.11.16
gpg==1.10.0
gpsoauth==0.4.1
humanize==2.6.0
idna==2.10
imageio==2.9.0
importlib-metadata==1.7.0
iniparse==0.5
isc==2.0
jeepney==0.4.3
Jinja2==2.11.2
jrnl==2.4.5
kiwisolver==1.2.0
M2Crypto==0.36.0
Mako==1.1.3
MarkupSafe==1.1.1
matplotlib==3.3.1
msgpack==1.0.0
NeuroTools==0.3.1
nftables==0.1
numpy==1.19.1
packaging==20.4
parsedatetime==2.6
passlib==1.7.2
pathspec==0.8.0
pgcli==3.0.0
pgspecial==1.11.10
Pillow==7.2.0
pip-review==1.1.0
ply==3.11
prompt-toolkit==3.0.6
psutil==5.7.2
psycopg2==2.8.5
pycairo==1.19.1
pycparser==2.20
pycryptodomex==3.9.8
pycurl==7.43.0.5
Pygments==2.6.1
PyGObject==3.36.1
pyparsing==2.4.7
PySocks==1.7.1
python-dateutil==2.8.1
pytz==2020.1
pyxdg==0.26
PyYAML==5.3.1
pyzmq==19.0.2
requests==2.24.0
rpm==4.14.2
salt==3001.1
SecretStorage==3.1.2
selinux==2.9
setproctitle==1.1.10
six==1.15.0
SLIP==20191113
slip.dbus==0.6.4
sqlparse==0.3.1
systemd-python==234
tabulate==0.8.7
terminaltables==3.1.0
textwrap3==0.9.2
tornado4==4.5.2
typing==3.7.4.3
tzlocal==2.1
urllib3==1.25.10
wcwidth==0.2.5
yamllint==1.24.2
zipp==3.1.0

[lelit]

That's strange: I'm using the following settings in my pgcli.conf

pager = /usr/bin/pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header on -s 4 -X

and get the expected result, regardless of the result size. I'm using current master of pgcli though, not 3.0.0, so maybe something has changed since that release?

[wchao]

Thanks. I'm using

pager = /usr/bin/pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header on

I added -s 4 -X to match yours, and other than changing the color scheme, it didn't make a difference. Can you try \d (List or describe tables, views and sequences)? Meta-commands seem to dump output in CSV format rather than outputting to pspg.

Another issue I have is that the paginated results from pgcli show an extra row that says "SELECT 2" (or however many rows) as the final row. That "SELECT X" row doesn't show up when using regular psql with pspg. Where does it come from? The number of results also counts the "SELECT X" row as one of the results, so if there are two actual rows in the result set, there will be a third that says "SELECT 2" in the third row, and then after the table of results, PostgreSQL will report "(3 rows)" even though only two rows matched the query. The third row is undoubtedly the "SELECT 2" row. Any idea how I get rid of that spurious row?

[lelit]

Even that works (again, using pgcli master), I tried many meta-commands, and all of them shown the expected output (that is, no CSV).

Not sure I understand the other issue, this is what I see:

user@db> select id from auth.operators limit 6;
┌──────────────────────────────────────┐
│                  id                  │
├──────────────────────────────────────┤
│ 2e37e330-8179-11e9-88f2-caf4e5664242 │
│ d121df60-ae32-11ea-9408-ee79ad44c7fa │
│ 58ec6c7e-bd04-11e9-bc39-da4fb7b9ccc3 │
│ d12565d6-ae32-11ea-9408-ee79ad44c7fa │
│ e10f4fa0-5baa-11ea-8542-aa7bc77feb78 │
│ e45aa482-9857-11e9-b86b-2eaa8ed8e92f │
└──────────────────────────────────────┘
(6 rows)
Time: 0.002s

without seeing the pager, raising the limit over my terminal $ROWS triggers the pager and I see the same footer.

Could you try out with current master?

[wchao]

This is what I see:

user@db:user> SELECT id, namespace, mime_type FROM file_vw;
id,namespace,mime_type
1006,personal,application/pdf
1005,personal,application/pdf
SELECT 2
Time: 0.003s

That shows both problems: first that it displays in CSV because pspg isn't being called (or pspg is exiting immediately due to the output being only a single page) and second, that there is a third row that just says "SELECT 2". When it is piped to pspg, it also says (3 rows) as the line after SELECT 2 and before Time. When it is piped into pspg, the SELECT 2 is within the borders of the result set, whereas the (3 rows) is after the result set grid.

How do I try out the current master? I am installing with pip install pgcli. Can I use pip to install the current master, or is there some other procedure for installing the current master?

[lelit]

Yes, you can say for example

pip install https://github.com/dbcli/pgcli/archive/master.zip

[wchao]

Nice. I ran pip install https://github.com/dbcli/pgcli/archive/master.zip. It seemed to install fine. When I run pip list, it still shows pgcli 3.0.0. Is that normal?

When I try pgcli now, I get the same results. The CSV output shows rather than being converted into table grid lines. My config settings are:

pager = /usr/bin/pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header on -X
table_format = csv

Are yours the same? Not sure what the problem is, but if you have other ideas for tests, happy to try them.

[lelit]

When I run pip list, it still shows pgcli 3.0.0. Is that normal?

Yes, I see the same, the number gets bumped before release.

My config settings are:

pager = /usr/bin/pspg --csv --rr=2 --quit-if-one-screen --ignore-case --csv-header on -X
table_format = csv

Are yours the same?

Yes again.

I doubt it matters, but I'd try to install the master version of pgspecial too, with

pip install https://github.com/dbcli/pgspecial/archive/master.zip

[wchao]

Thanks. Just tried pip install https://github.com/dbcli/pgspecial/archive/master.zip, and it reports "Requirement already satisfied" and then does not install anything since I already have pgspecial==1.11.10.

How does psql send data to pspg for formatting? I don't think it uses csv. Maybe psql preformats the data into a grid? Here is my .psqlrc:

\set HISTSIZE 100000
\pset linestyle unicode
\pset null ''
-- border 2 for pspg.
\pset border 2
-- see https://github.com/okbob/pspg for details on pspg.
\setenv PAGER 'pspg -X'
-- allow :x and :xx to toggle between less and pspg for pager.
\set x '\setenv PAGER less'
\set xx '\setenv PAGER 'pspg -X''

Is there a way to have pgcli send data to pspg with border 2 and linestyle unicode to emulate psql? I think pgcli has superior input capabilities, but the output and integration with pspg is causing problems and isn't as good as psql right now (possibly due to my configuration rather than anything to do with pgcli's capabilities).

[lelit]

Uhm, didn't it say the same when install pgcli perhaps? I'd try saying pip install -U...

Wrt sending preformatted output to the pager, I think it would defeat most of the advantages: emitting CSV is much faster than doing all the alignment on the pgcli side.

[wchao]

I tried the pip install -U for pgspecial, and that caused it to install the version on master.

Unfortunately, that didn't make a difference. I still get output in raw CSV as opposed to formatted nicely in a grid. It only happens when the output is not sent to pspg, or maybe sent, but not processed by pspg because it's a single page. When pspg processes the CSV, the results are nicely formatted.

I think for now I'm going to have pgcli format the output (rather than emit csv) to pspg, but I'm happy to try more tests if anyone has things they want me to try. The optimal situation would be pgcli emits CSV when it knows pspg will process the data, and otherwise pgcli does the formatting.

I'm running on CentOS 8 if that makes a difference.

[ahopkins]

This is the same behavior that I am seeing whether from pypi or master.

After running this, all worked as expected:

\pset pager always