Whenever people look for help with queries, one of the first things that I ask is: what is \d of the table.
Which works great, with the caveat, that the person on the other end must be somewhat familiar with psql. And quite often it's not the case.
So I figured, I can try to fix it.
FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands.
But, can I use it in psql?
Recently we had interesting problem. There are some maintenance tools, that do stuff that touch database. One can be calling repack, another can be specific dump, and yet another can be applying migrations from application.
The problem is that they can step over each other toes, and cause issues.
So we needed to add some way to prevent them from running at the same time…
At a company we have literally thousands of Pg servers. The layout is also kinda non-obvious. Each database is named the same, but contains different data. And in front of it all, we have pgbouncers.
After some talk, it was suggested that perhaps we could make psql prompt show which database it is connected to. And perhaps some more information, like backend pid. I thought it will be simple…
Continue reading A tale of making company-wide standard psqlrc
On 11st of September 2020, Alvaro Herrera committed patch:
psql: Display stats target of extended statistics The stats target can be set since commit d06215d03, but wasn't shown by psql. Author: Justin Pryzby <justin@telsasoft.com> Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com Reviewed-by: Georgios Kokolatos <gkokolatos@protonmail.com> Reviewed-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Continue reading Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics
I just released new version of Pg::Explain Perl library that is handling parsing of plans for explain.depesz.com.
There are quite a lot of changes, but mostly internal, but one thing is pretty interesting – Pg::Explain, and because of this also explain.depesz.com should be able to parse plans with arbitrary values of border, linestyle, format, unicode_border_linestyle, unicode_column_linestyle, and unicode_header_linestyle psql options.
You can see five simple examples already uploaded:
Continue reading Changes on explain.depesz.com
On 12nd of February 2020, Michael Paquier committed patch:
Add %x to default PROMPT1 and PROMPT2 in psql %d can be used to track if the current connection is in a transaction block or not, and adding it by default to the prompt has the advantage to not need a modification of .psqlrc, something not possible depending on the environment. This discussion has happened across various sources, and there was a strong consensus in favor of this change. Author: Vik Fearing Reviewed-by: Fabien Coelho Discussion: https://postgr.es/m/09502c40-cfe1-bb29-10f9-4b3fa7b2bbb2@2ndquadrant.com
Continue reading Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql
On 19th of November 2019, Thomas Munro committed patch:
Allow invisible PROMPT2 in psql. Keep track of the visible width of PROMPT1, and provide %w as a way for PROMPT2 to generate the same number of spaces. Author: Thomas Munro, with ideas from others Discussion: https://postgr.es/m/CA%2BhUKG%2BzGd7RigjWbxwhzGW59gUpf76ydQECeGdEdodH6nd__A%40mail.gmail.com
Continue reading Waiting for PostgreSQL 13 – Allow invisible PROMPT2 in psql.
On 26th of November 2018, Tom Lane committed patch:
Add CSV table output mode in psql. "\pset format csv", or --csv, selects comma-separated values table format. This is compliant with RFC 4180, except that we aren't too picky about whether the record separator is LF or CRLF; also, the user may choose a field separator other than comma. This output format is directly compatible with the server's COPY CSV format, and will also be useful as input to other programs. It's considerably safer for that purpose than the old recommendation to use "unaligned" format, since the latter couldn't handle data containing the field separator character. Daniel Vérité, reviewed by Fabien Coelho and David Fetter, some tweaking by me Discussion: https://postgr.es/m/a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org
Continue reading Waiting for PostgreSQL 12 – Add CSV table output mode in psql.
On 12nd of September 2017, Tom Lane committed patch:
Add psql variables to track success/failure of SQL queries. This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated after every query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE, which are updated only when a query fails. The expected usage of these is for scripting. Fabien Coelho, reviewed by Pavel Stehule Discussion: https://postgr.es/m/alpine.DEB.2.20..12290@lancre