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…
Continue reading How to get advisory lock in shell?
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
Author: Justin Pryzby <firstname.lastname@example.org>
Reviewed-by: Georgios Kokolatos <email@example.com>
Reviewed-by: Tatsuro Yamada <firstname.lastname@example.org>
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
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
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
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
Continue reading Waiting for PostgreSQL 11 – Add psql variables to track success/failure of SQL queries.
On 5th of September 2017, Tom Lane committed patch:
Add \gdesc psql command.
This command acts somewhat like \g, but instead of executing the query
buffer, it merely prints a description of the columns that the query
result would have. (Of course, this still requires parsing the query;
if parse analysis fails, you get an error anyway.) We accomplish this
using an unnamed prepared statement, which should be invisible to psql
Pavel Stehule, reviewed by Fabien Coelho
Continue reading Waiting for PostgreSQL 11 – Add \gdesc psql command.
On 30th of March 2017, Tom Lane committed patch:
Support \if ... \elif ... \else ... \endif in psql scripting.
This patch adds nestable conditional blocks to psql. The control
structure feature per se is complete, but the boolean expressions
understood by \if and \elif are pretty primitive; basically, after
variable substitution and backtick expansion, the result has to be
"true" or "false" or one of the other standard spellings of a boolean
value. But that's enough for many purposes, since you can always
do the heavy lifting on the server side; and we can extend it later.
Along the way, pay down some of the technical debt that had built up
* Refactor exec_command() into a function per command, instead of
being a 1500-line monstrosity. This makes the file noticeably longer
because of repetitive function header/trailer overhead, but it seems
much more readable.
* Teach psql_get_variable() and psqlscanslash.l to suppress variable
substitution and backtick expansion on the basis of the conditional
stack state, thereby allowing removal of the OT_NO_EVAL kluge.
* Fix the no-doubt-once-expedient hack of sometimes silently substituting
mainloop.c's previous_buf for query_buf when calling HandleSlashCmds.
(It's a bit remarkable that commands like \r worked at all with that.)
Recall of a previous query is now done explicitly in the slash commands
where that should happen.
Corey Huinker, reviewed by Fabien Coelho, further hacking by me
Continue reading Waiting for PostgreSQL 10 – Support \if … \elif … \else … \endif in psql scripting.