Setting intervalstyle to postgres_verbose sometimes gives wrong results

[autra]

Description

Setting intervalstyle to postgres_verbose gives incoherent results, and sometimes an error.

example:

psql> set intervalstyle = 'postgres';
SET
Time: 0.001s
psql> select now() - now()
 ;
╒════════════╕
│ ?column?   │
╞════════════╡
│ 0:00:00    │
╘════════════╛
SELECT 1
Time: 0.008s
psql> set intervalstyle = 'postgres_verbose';
SET
Time: 0.001s
psql> select now() - now()
 ;
invalid literal for int() with base 10: '@ 0'

Also, in one table, I calculate avg(time1 - time2). The results is a lot bigger with postgres_verbose than it should be (but that may be because of above error?).

Your environment

• ubuntu 20.04

ᐅ pgcli --version
Version: 3.1.0

> select version();
╒══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ version                                                                                                                          │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ PostgreSQL 12.6 (Ubuntu 12.6-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit │
╘══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╛
SELECT 1

Output of pip freeze

aiohttp==3.6.2 alembic==1.1.0.dev0 ansible==2.9.6 apache-libcloud==2.8.0 appdirs==1.4.4 apsw==3.28.0.post1 apt-xapian-index==0.49 argcomplete==1.8.1 asciinema==2.0.2 async-timeout==3.0.1 atomicwrites==1.1.5 attrs==19.3.0 autobahn==17.10.1 Automat==0.8.0 autopep8==1.5 Babel==2.8.0 backcall==0.1.0 bcrypt==3.1.7 beautifulsoup4==4.8.2 blessings==1.7 blinker==1.4 breezy==3.0.2 Brlapi==0.7.0 cached-property==1.5.1 cbor==1.0.0 certifi==2019.11.28 cffi==1.14.0 chardet==3.0.4 cli-helpers==2.1.0 click==7.1.2 colorama==0.4.3 colored==1.4.2 command-not-found==0.3 configobj==5.0.6 constantly==15.1.0 coverage==4.5.2 cryptography==2.8 css-parser==1.0.4 cssselect==1.1.0 cssutils==1.0.2 cupshelpers==1.0 cycler==0.10.0 Cython==0.29.14 dbus-python==1.2.16 decorator==4.4.2 defer==1.0.6 Deprecated==1.2.7 devscripts===2.20.2ubuntu2 diskcache==4.1.0 distlib==0.3.0 distro==1.4.0 distro-info===0.23ubuntu1 dnspython==1.16.0 docker==4.1.0 docker-compose==1.25.0 dockerpty==0.4.1 docopt==0.6.2 dulwich==0.19.15 duplicity==0.8.12.0 entrypoints==0.3 fasteners==0.14.1 fastimport==0.9.8 feedparser==5.2.1 filelock==3.0.12 flake8==3.7.9 Flask==1.1.2 Flask-BabelEx==0.9.3 Flask-Compress==1.4.0 Flask-Gravatar==0.4.2 Flask-Login==0.4.1 Flask-Mail==0.9.1 Flask-Migrate==2.5.2 Flask-Paranoid==0.2.0 Flask-Principal==0.4.0 Flask-Security-Too==3.4.2 Flask-SQLAlchemy==2.1 Flask-WTF==0.14.2 future==0.18.2 GDAL==3.0.4 geoapi==0.1 glean-parser==1.22.0 glean-sdk==31.1.1 gpg===1.13.1-unknown greenlet==0.4.15 html2text==2020.1.16 html5-parser==0.4.9 html5lib==1.0.1 httpie==1.0.3 httplib2==0.14.0 hyperlink==19.0.0 idna==2.8 ifaddr==0.1.6 importlib-metadata==1.5.0 incremental==16.10.1 iotop==0.6 ipython==7.13.0 ipython-genutils==0.2.0 itsdangerous==1.1.0 jedi==0.15.2 Jinja2==2.10.1 jmespath==0.9.4 jsonschema==3.2.0 jupyter-core==4.6.3 kazam==1.4.5 keyring==18.0.1 keyrings.alt==3.4.0 kiwisolver==1.0.1 language-selector==0.1 laspy==1.7.0 launchpadlib==1.10.13 lazr.restfulclient==0.14.2 lazr.uri==1.0.3 ldap3==2.4.1 lfm==3.1 lockfile==0.12.2 louis==3.12.0 lxml==4.5.0 lz4==3.0.2+dfsg Mako==1.1.0 Markdown==3.1.1 MarkupSafe==1.1.0 matplotlib==3.1.2 mccabe==0.6.1 mechanize==0.4.5 meld==3.20.2 mock==3.0.5 mohawk==1.1.0 monotonic==1.5 more-itertools==4.2.0 mozdevice==3.2.3 mozfile==2.1.0 mozinfo==1.2.1 mozInstall==2.0.0 mozlog==6.0 mozprocess==1.1.0 mozprofile==2.5.0 mozregression==4.0.6 mozrunner==7.8.0 mozterm==1.0.0 mozversion==2.3.0 mpi4py==3.0.3 msgpack==0.6.2 multidict==4.7.6 nbformat==5.0.4 neovim==0.3.1 netaddr==0.7.19 netifaces==0.10.4 nose==1.3.7 nose2==0.9.1 notify2==0.3 ntlm-auth==1.1.0 numpy==1.17.4 oauthlib==3.1.0 olefile==0.46 onboard==1.4.1 openshot-qt==2.4.3 OWSLib==0.19.1 packaging==20.3 paramiko==2.6.0 parso==0.5.2 passlib==1.7.2 pathspec==0.8.0 pathtools==0.1.2 pbr==5.4.5 pendulum==2.1.2 pep8==1.7.1 pexpect==4.6.0 pgcli==3.1.0 pgspecial==1.11.10 pickleshare==0.7.5 Pillow==7.0.0 Pivy==0.6.5 plotly==4.4.1 pluggy==0.13.0 ply==3.11 powerline-status==2.7 prettytable==0.7.2 prompt-toolkit==2.0.10 psutil==5.7.2 psycopg2==2.8.5 py==1.8.1 py-ubjson==0.14.0 pyasn1==0.4.2 pyasn1-modules==0.2.1 pycairo==1.16.2 pychm==0.8.6 pycodestyle==2.5.0 pycparser==2.20 pycrypto==2.6.1 pycups==1.9.73 pyflakes==2.1.1 PyGithub==1.43.7 Pygments==2.3.1 PyGObject==3.36.0 PyHamcrest==1.9.0 pyinotify==0.9.6 PyJWT==1.7.1 pykerberos==1.1.14 PyNaCl==1.3.0 pynvim==0.4.1 PyOpenGL==3.1.0 pyOpenSSL==19.0.0 pypandoc==1.4 pyparsing==2.4.6 pypng==0.0.20 pyproj==2.5.0 PyQRCode==1.2.1 PyQt5==5.14.1 PyQtWebEngine==5.14.0 pyrsistent==0.15.5 pystache==0.5.4 pytest==4.6.9 python-apt==2.0.0+ubuntu0.20.4.4 python-dateutil==2.7.3 python-debian===0.1.36ubuntu1 python-gitlab==2.0.1 python-magic==0.4.16 python-snappy==0.5.3 PyTrie==0.2 pytz==2019.3 pytzdata==2020.1 pywinrm==0.3.0 pyxdg==0.26 PyYAML==5.3.1 pyzmq==18.1.1 qrtools==2.0 redo==2.0.3 regex==2019.8.19 reportlab==3.5.34 repoze.lru==0.7 requests==2.22.0 requests-kerberos==0.12.0 requests-ntlm==1.1.0 requests-unixsocket==0.2.0 retrying==1.3.3 Routes==2.4.1 SecretStorage==2.3.1 selinux==3.0 service-identity==18.1.0 setproctitle==1.2.1 simplejson==3.16.0 sip==4.19.21 six==1.14.0 slugid==2.0.0 soupsieve==1.9.5 speaklater==1.4 SQLAlchemy==1.3.12 sqlparse==0.4.1 ssh-import-id==5.10 sshtunnel==0.1.4 systemd-python==234 tabulate==0.8.7 taskcluster==30.1.1 taskcluster-urls==12.1.0 termcolor==1.1.0 terminaltables==3.1.0 texttable==1.6.2 traitlets==4.3.3 Twisted==18.9.0 txaio==2.10.0 txt2tags==3.4 u-msgpack-python==2.1 ubuntu-advantage-tools==20.3 ubuntu-drivers-common==0.0.0 ufw==0.36 unattended-upgrades==0.1 Unidecode==1.1.1 unidiff==0.5.5 urllib3==1.25.8 usb-creator==0.3.7 vboxapi==1.0 virtualenv==20.0.20 vizex==1.0 wadllib==1.3.3 watchdog==0.10.2 wcwidth==0.1.8 webencodings==0.5.1 WebOb==1.8.5 weboob==2.0 websocket-client==0.53.0 Werkzeug==1.0.1 wrapt==1.11.2 wsaccel==0.6.2 WTForms==2.2.1 wxPython==4.0.7 xkit==0.0.0 xmltodict==0.12.0 yamllint==1.23.0 yarl==1.4.2 zeroconf==0.24.4 zipp==1.0.0 zope.interface==4.7.1

[gfrlv]

It's a long-standing issue in psycopg2 which they are not going to fix https://github.com/psycopg/psycopg2/issues/707 The exception is happening inside the C code (here), so it's tricky to access the data from Python, and we don't know that the result type is interval until after the query, so we cannot cast to text or do some other workaround.

At the moment, I'm afraid you'll have to cast intervals to text if you want to use postgres_verbose.