A weekly podcast about all things PostgreSQL
Nikolay: Hello, hello, this is
Postgres.FM.
I'm Nikolay from Postgres.AI, and
as usual with me here Michael
from pgMustard.
Hi, Michael.
Michael: How are you, Nikolay?
I'm good, how are you?
Okay.
No, how are you?
Nikolay: I'm very good, I'm very
good.
Okay, so we are going to discuss
psql once again.
Last time we compared it to graphical
UIs, graphical tools.
And this, like, it was my idea
to discuss it once again, but
just purely just psql.
How to use it in various cases,
why use it, and and maybe some
tips and tricks, right?
Michael: Yeah, looking forward
to this 1.
You say last time we talked about
it but it was like 18 months
ago
Nikolay: yeah I was trying to say
we discussed it last time I
mean in this area yeah and we discussed
psql particularly right
I
Michael: just couldn't believe
it when I looked it up how long
ago that was.
So yeah,
Nikolay: your mind is focused,
is targeted to find gotchas everywhere
since last episode and You try
to find it in my speech now.
Okay, this is good.
But just for today, let's focus
on psql.
And I have some experience, I like
it, which is good, right,
for conversation.
You said you don't have a lot of
experience with it, but you
may be better prepared as usual
so maybe you know some recent
changes or something.
Where can we start?
Let's maybe discuss why, right?
Because we have Python, we have
various tools.
Why psql?
What's your answer to this?
Michael: Well, yeah, so I don't
feel like I'm an advanced user
of psql, personally.
I have to use, well, I try to use
it sometimes, I try to keep
familiar with it so that if I need
to.
The nice thing about psql is it's
always available.
We talked about this last time.
But if it's somebody else's database
or it's my own, it's always
available if I need to do it from
my mobile, via like, yeah,
so if I like a console, I can do
that.
It's very accessible.
But I don't feel like I'm an advanced
user the reason I've had
to get more familiar with it as
well there is like helping diagnose
issues, so I see a lot of query
plans and query plan formatting
can vary quite a lot depending
on the editor and psql or psql
has a bunch of formatting and alignment
options and different
pages and each 1 has its own formatting
quirks so supporting
all those different formats for
people copy and pasting, EXPLAIN
plans has given me some familiarity
as well.
But yeah, on the coding side of
things, I don't have much experience
at all.
But I did check out what was the
program called?
Is it postgres_dba?
postgres_dba?
Nikolay: postgres_dba, yeah.
This is a bunch of tools and interactive
menu I've built using
some tricks in psql.
It was long ago, but yeah, I still
use it sometimes and some
people use it.
It's quite interesting.
Right.
So it sounds like you say it's
always available and I partially
agree.
It's available if you install Postgres
with client packages.
If you install only server packages
or if you are a user of RDS,
it's not available, right?
Or maybe user of, I don't know,
Supabase.
psql, maybe it's not that natural
because there is editor in,
or new 1, for example, there is
an editor right in browser and
maybe it's closer for this particular
packaging, I don't know,
like, there's no psql by default
there, right?
Michael: Some cloud providers you
can, so I know on Google Cloud,
which is what I use myself, you
can, there's a console, there's
a cloud console that you can have
that you can get access to
Nikolay: that's good yeah
Michael: yeah some you can
Nikolay: and I think this is not
they do it not bringing psql
to browser but maybe like emulating
console running on server.
This is what I suspect.
Michael: I assume so, yeah.
Nikolay: Yeah, this is good, but
others don't do this, unfortunately,
for me.
I would prefer everyone to provide
a psql and make it available
like it is when you deal with pure
open source Postgres.
Right?
So, to me, the reason why psql,
there's a bunch of reasons.
First of all, it's the only official
and well-maintained client.
If we consider all the clients
regardless, like terminal-based
or graphical, doesn't matter.
This is the only officially maintained
client.
Well, if we don't count pgbench
as client, right?
Because it's also a client but
it's very specific.
It's very specific, it's also official,
well-maintained, somehow
included to server packaging for
Ubuntu and Debian, I don't know
why, unlike psql.
But it's not a regular client because
it's needed for different
tasks, for benchmarks, for research
of performance and so on.
psql is a universal client, terminal-based,
and this is the only
1.
There is no graphical tools, graphical
interfaces officially
supported by Postgres itself.
Postgres shipped with Postgres
and so on.
And pgAdmin is also a third-party
tool.
Which means the release cycle,
quality, features, you can see
changes, new stuff added to psql,
in release notes of Postgres
itself, this is good.
Quality and also trust.
So when you say explain plans formatting
can be very strange.
Well, from psql, you actually said
it's from psql can be very
strange, but I'm going on purpose
to mix psql and psql as we
discussed, right?
Because I don't care about this.
Michael: Same.
Nikolay: Yeah, so this is specific
case plans, right?
I know this pain of bad formatting,
shitty formatting, and you
need to deal with it, and it's
like, it's a lot of pain.
But in all other areas, when you
do something, you expect behavior
from psql, like the true behavior,
right?
I can give you an example.
I had some small project to convert
something.
I don't remember.
Maybe to DELETE many million rows
or convert some table to something.
I don't remember.
Maybe it was int4 (primary key conversion).
So we decided to code it purely
in psql and SQL.
So you just combine files.
I like to name such files dot psql.
So it emphasizes that it's not
only pure SQL, but they can have
some commons only psql supports,
like \if, for example,
and we will talk about it in a
few minutes.
And something was interesting,
like some query was working there,
everything as expected.
We had tests, all good.
But attempt by some other developers
in that company, it was
a huge client.
They went to IPR like a few months
after we started working with
them.
It was great.
And they coded like similar stuff,
but in their system.
It was Java, actually.
And same queries didn't work well.
Like, I mean, they didn't work
at all.
And when I started troubleshooting,
I noticed that in 1 of the
graphical UIs, it's absolutely
the same behavior as in their
code.
And that graphical program was
also built using Java.
So I realized it's JDBC.
It was something with float, rounding,
rules or something.
It was very different behavior
compared to what I saw in psql
and this discrepancy in behavior
it can be big deal if you want
to like to have something reliable
and supported for many years,
it's good to deal with psql.
Michael: That's a great point.
I use it for debugging purposes
as well.
Like, it helps rule something out.
If I can, if I am seeing some behavior
from it via my application
or via a graphical user interface,
and it behaves the same in
psql, I'm gaining confidence that
there's a problem.
It helps rule out.
I assume there's not a bug in psql.
That's a default assumption of
mine.
I'm probably wrong, probably gonna
trip me up 1 day, but it hasn't
so far.
Nikolay: Yeah, sitting in psql,
talking to Postgres, you're basically
closer to Postgres, right?
There is no...
JDBC adds something and if it's
something, a third-party tool,
it also adds something and this
something, like these layers,
can change what you see.
And this can be a bug or intentional,
but this I always like,
it's like with monitoring, if I
see some graph, like I don't
understand.
Unless I see the code, how the
information was gathered and processed,
I don't understand what is presented.
Trust is not high in this area.
There's always doubt.
Am I seeing this?
This is spike really like what
I'm thinking it is, right?
So this is about rust and so on,
and also features, like features
are great.
And I'm sure, I don't know all
of them.
I'm constantly learning and finding
new stuff which I was not
aware of for many years.
So these are my basic ideas why
I use it a lot.
Michael: Yeah, so actually I had
a question for you on that front.
When I was looking through the
docs page for psql, like the number
of options and flags and it's just
so many when you're working
with it how do you learn about
new features do you think I wonder
if it can do X and then you look
up can it do X or is it more
a case of every now and again you'll
flick through and think
how yeah how do you learn more
about it
Nikolay: yeah it's a good question
I think it's a mix so sometimes
I just know what is possible and
I don't remember I my favorite
comment is \? and \H for help with
SQL and there's a lot of documentation, it's right there.
So help and the grammar of SQL itself, it's there.
But sometimes I see something which I'm thinking, is it possible?
For example, if we want to process a lot of rows and batches,
of course, it's good to write some full-fledged program using
Python or Go or anything, with monitoring, logging, but if you
need it quickly, my usual approach, like ad hoc processing, it's
needed really soon.
We need the result right now.
I just write a psql code using \watch.
Right?
And I was always like, okay, \watch is good.
Like it's for looping, right?
You run some query which takes a batch and process it.
For example, deleting, right?
We need to clean up some data.
And we cannot delete in Postgres.
It's a very bad idea to delete many millions of rows in a single
transaction.
Michael: We did an episode.
Nikolay: Yeah, yeah.
So massive delete can hit you.
If you need to delete like millions of rows in a huge table,
it's better to do it in batches.
And it's easy to write some CTE.
Quite easy.
To write some CTE, finding the scope of work right now, like
a thousand rows or something, delete them and report in a nice
way maybe with even with progress bar which wishes like this
what I like and not not to forget about vacuum of course and
dead tuples and processing
Michael: we took yeah
Nikolay: yeah yeah yeah
Michael: but but okay back to so we're talking about \watch back
to us \watch right badly named probably What do you think of the
name?
Nikolay: \watch?
Michael: Yeah.
Well, yeah.
It was created, I think, to, you know, like you have a select
from just activity.
You, for example, aggregate queries by state, understand how
many active backends we have, maybe wait events as well, and
you just observe them.
And that's why \watch, I think.
Michael: I get it.
But repeat or something that gives it a little bit more of like
an idea of what it's actually going to do.
Nikolay: Your mind definitely is targeted to find gotchas.
I'm with you here.
It's not perfect naming at all.
Yeah.
100%.
Michael: So yeah, super cool feature.
I've even seen it used, the time
I saw it used most recently
was I was watching a Patroni demo
for when I was preparing for
a few episodes ago.
Alexander Kukushkin used \watch
in 1 session to keep querying
a database like while trying to
fail over to show latencies and
to show what happened like did
any queries actually fail did
it like what was the what was the
latency of the failover that
kind of thing it was a really cool
use case to continually query
a database
Nikolay: right right exactly.
\watch is useful, but it's until,
I think it was not until Postgres
16 when we had, with Andrei and
Kirk, we had Postgres hacking
sessions, and we extended it to
support the number of loops you
want.
Because the only option is the
sleep time between your queries.
In the documentation of another
gotcha, I think it was described
as interval time.
But if you take into consideration
the duration of query itself,
it's not counted.
So it's only sleep time after 1
comment finished before running
the next 1.
And the idea was, before Postgres
16, the only option was to
specify time.
By default, I think it's 2 seconds.
I don't know why 2 seconds.
Specify some time.
You can specify 0.1, 100 milliseconds
slip time, like very quickly
running queries in a loop, and
it's infinite, unless it fails.
If it fails, it stops.
By the way, it could be an option
again.
I'm just thinking right now, maybe
this should be an option saying,
if it fails, still continue.
Right?
Because...
Michael: Interesting.
Nikolay: Yeah, default behavior
is just to stop.
In some cases, I would prefer continue.
And like, right now I have such
situations when I need even if
it fails, still continue with the
loop.
In this case, I...
Michael: Oh, like the failover
testing.
If you're doing failover testing,
even if 1 of them fails...
Like in Kukushkin's case.
Yes, exactly.
Nikolay: Exactly, exactly.
But \watch behaves like it just
stops.
And if I need this behavior, I
just need to go to like shell
level, bash or any like ZSH and
there I need to bash usually
because I don't run such things
from my laptop.
I'm usually running everything
in tmux right on the server or
very close to the server in cloud.
So if like California internet
is very bad, you know it.
And even Starlink sometimes is
down.
So in this case, I'm not losing
my session.
tmux is great.
And if query fails, if you need
to continue, I'm forced to go
to shell while sleep 1, for example.
Not while true, because if you
do while true, ctrl-c won't work
properly in many cases.
It's very annoying if you write
while...
If you sleep first, then do something.
So while sleep 1, well, how many
seconds you want to have between
running psql.
In this case, Control-C will work
when you need to interrupt
it.
And in this case also you can have
some additional stuff saying
if comment is failed, for example,
vertical bars, a couple of
vertical bars, or report failed
and then continue the loop, right?
Michael: I was reading through
what the, well, you're mentioning
some improvements that have happened
in psql recently, which is
cool, but I think it's not necessarily
obvious that it's continuing
to improve.
And I did notice, I didn't read
into the details, but somebody
improved it recently to make Control-C
work better.
So I think maybe in 1 of the more
recent well I'm not sure it
fixes that
Nikolay: I'm not sure it's related
it's well it's interesting
maybe maybe I need to check it's
it's all it was in Postgres 17 release
notes right yeah I also remember
something, but I didn't connect
dots here.
So let's unwrap my story, because
we have already...
It's amazing, right?
So the idea was we want to be able
to specify how many loops
we need.
And to do that we extended basically
this tiny grammar, right,
or tiny format, and allow to specify
basically any options, named
options.
So we added like the...
We converted this not interval,
how to properly say, the break
between sleep time between 2 commands
executed.
Michael: Sleep time is good.
Nikolay: Yeah, I think we can call
it sleep.
By the way, since I don't see Postgres
16, it was released
in 2016, I don't see 16 too often
in production.
I still have not got used to this,
what we developed, right?
It's interesting.
So I still, mostly all the time,
I use psql.
And I must correct myself, actually.
In many cases, we have psql already
16, even if we work with
old server.
So it's just my mind problem.
I need to adjust my habits.
I need to adjust my habits and
start using what we developed.
But the second option was, and
this is the whole purpose of that
work, is to allow us to specify
the number of loops you need.
If you know you don't need more
than something, that's it.
But the interesting fact is that
the inspiration of that, yeah,
I'm checking, it's interval I,
number of seconds, it's wrong
naming, but it is interval.
With understanding it's not actually
interval, it's interval
not taking into account the duration
of the comment executed,
which can take actually minutes
or hours in extreme cases.
And yes, c or count equals something.
This is the number of loops you
need, right?
And that's great.
But actually, the original idea
why we thought about this was...
Many times I had work, I process
a lot of rows and batches, and
I need to stop.
And to stop I usually used division
by 0.
This is a very old trick, it works.
I know the \watch will stop if there
is an error.
So I just...
When there is nothing to process
anymore I just divide by number
of rows to process.
It's 0 so it stops because of division
by 0.
And interesting, then in Postgres
17, it was implemented, right?
Already not by us, by Greg Sabino Mullane,
I think.
Yeah, allow psql \watch to stop
after minimum number of rows return.
Funny thing, this is exactly what
I needed.
But we implemented some different
parts around this problem.
So you now can say m or min rows,
mir underscore rows equals
some number of rows.
And you can say 0.
If we have 0, that's it.
This is great, right?
Michael: Yeah, kind of a weird
name.
Is it 0 or is it 1?
Nikolay: A number of rows returned.
If you return, for example, if
you delete returning star and
it's returned 0, then you need
to stop.
Oh, you think it should be...
Yeah, well, let's double check
but let's not to let's not to
be like super we are not providing
some like lesson or reference
we are talking what's possible.
There is documentation and also
you can try and learn.
So now it's possible, you don't
need division by 0 anymore.
The only issue with this I have
is that I did like, and I still
do like, my approach reporting,
like, you know, progress bar
and so on, many stuff, many pieces
of information.
When you process a batch, you report
a lot of stuff.
In this case, I cannot use this.
I cannot say...
I always have some rows reported
in the result.
Usually just 1 row, many columns,
like number of rows we processed,
what's left, like progress, percentage,
anything.
So I think will I still use division
by 0 after I got used to
Postgres 17.
I'm not sure, right?
Michael: Should we move on?
Nikolay: Yeah, let's move on.
And I wanted to emphasize there
are a couple of areas.
There are 2 big areas.
First big area where we can use
Psycal.
First big area is interactive mode.
And sometimes we use \watch there
for just observing something.
This is where name plays well.
Or we use like various advanced
stuff.
I got used to \gx instead
of semicolon.
By the way, both \watch and \gx don't
require semicolon.
I know even Andrey learned it from
me, it's not obvious.
They can be your end of command
semicolon, replacement for it.
Michael: So \gx, I looked this up,
it was send the last query,
send that to the server, right?
Nikolay: Right, but it's expanded.
So the idea is it's like semicolon,
but it's expanded.
So it basically, if you have very
wide result set, many, many
columns, but low number of rows,
for example, just 1, \gx is much
better because it's equivalent
to switching to expanded mode
using \x.
So you see 1 column on 1 line,
second column on a different line,
and so on.
You see it transponded, right?
And yeah, I just have a habit to
use it when I just select something,
for example, select star from users
where id or email equals
this.
And I want to see all columns.
I just use \gx, instead
of semicolon.
And I don't need to deal with this
formatting or horizontal scrolling
if you have pspg installed.
pspg I didn't mention, I do like
pspg, which provides much better
output and pagination in psql.
Very great addition to psql.
Michael: Yeah, it's pretty cool.
You can even do like horizontal
scrolling within the terminal.
It's pretty cool.
And did you know pspg is even mentioned
in the psql docs?
That's pretty cool.
I didn't realize that till today.
Nikolay: Unfortunately, no, because
it's third...
I think Postgres docs don't mention
third-party tools almost.
Maybe except pgAdmin.
I don't know if pgAdmin is mentioned
in Postgres docs.
Michael: I have not seen it there.
But it's very rare to see a third-party
tool mentioned in the
documentation.
Nikolay: Maybe never.
Michael: Well, this is third-party
though, pspg.
Nikolay: Yeah, it's third-party.
Michael: So not never
It is?
Michael: Yeah in the docs
Nikolay: It's a surprise for me
Michael: Yeah, same.
That's why I mean, that's why I
said in psql it's mentioned under
paging like it makes sense.
Nikolay: That's great
Nikolay: That's great.
Yeah, it's it's good.
And yeah, I like it a lot.
I use it all the time when I can.
So yeah, also colors, it provides
good colors, menu, interactive
menu, so many cool stuff.
Yeah, so back to \watch and
\gx. \watch and
\gx, semicolon is not
needed, and even more, like if
you use semicolon with \watch, you
will get basically extra call,
which may be not good.
When you start counting already
With new options, new option
count, it's an extra call.
Right.
Okay, what else?
Let's talk about interactive mode
and what tricks are worth knowing,
In addition to \gx.
So
Michael: when you say interactive
mode, do you mean like a user
doing administrative tasks or doing
some ad hoc queries?
Yeah, okay, great.
Nikolay: Yeah, yeah.
What are your favorite things when
you rarely touch psql?
Michael: I think they're not...
Well, mine aren't going to be on
the advanced side of things,
but yeah, some of the alignment
things are quite useful, so like
\a can toggle off alignment
if for any reason you want
it.
So I complained about things at
the beginning.
Nikolay: What's the reason to use
backslash, to turn off alignment?
Michael: If you want, like if you're
doing some stuff with JSON,
for example, the JSON output, you
don't want like a pretty nice...
So it's a nice feature of psql
that you get tables formatted
as tables with characters but you
don't want that if you're dealing
with JSON.
Nikolay: I always use it when I
want to see the body of function
or trigger function or stored procedure.
So if you use \df or \sf,
it was changed, right?
\df plus.
I always used \df plus,
but I think it got...
Like this behavior was duplicated
or removed and you need to
use \sf or something,
so you need to see function using
s.
And in this case, If you don't
run \a before that, the
formatting will skew all the indentation
of the code, and it's
hard to understand.
But this is also my habit,
\a, then \sf plus,
and see the function, understand
what it's doing, and so on.
Then you can even, if it's not
production, you can edit the function
using \ef.
And you know, like this probably
also gotcha.
We have my mind is also, this echo
from last episode.
And those who didn't listen to
it Like it was interesting episode
to me as well So when you use
\ef and I usually use
VI right?
Yeah, I'm a big VI fan You go to
VI, you edit everything, then
you, as usual, like call on WQ,
like save and exit, write and
quit.
And then, you know this or no?
Michael: I'm not a VI user, but
I've read like, \e is
1 of those tips that often comes
up with Chrome.
Like you can just use it to edit
anything.
It doesn't
Nikolay: have to be a function.
Right, but for specifically for
functions, there is a gotcha.
It's not about VI.
You can, I think, use Nano or anything
and still bump into this
problem?
The problem is, you think it's
written and saved, but Postgres
prompt, not psql prompt, shows
something like a transaction is
not closed, right?
You need the semicolon to finalize
it.
And this is not expected.
And I'm still like many years,
like I deal with it.
I know this every time I deal with
it, I'm thinking, why is it
so?
I know like there should be some
explanation why it's so, but
yeah.
So after \ef, always
semicolon.
Michael: Or \gx.
Nikolay: Well, interesting.
Michael: I don't know if that would work.
Nikolay: Maybe.
Okay, maybe.
What else?
I like a \set and I use it from time to time.
There are 2 cases.
I use it when I need some variable.
Of course, this moves us already to programming mode, not interactive
mode.
Michael: There's overlap, right?
Nikolay: Right, There's overlap for sure.
I like to use it sometimes to set something to have short comment,
for example.
For example, you know, we talked about buffers in explain analyze
a lot and you can just define some like colon, colon, colon,
colon, EAB, for example, or just colon something, I don't know.
And this 1 might, yeah.
Michael: In my docs for, so for getting a query plan, because
we recommend explain in parentheses analyze format json buffers
verbose settings while in our example we just do colon ea as
the alias for that and I but I don't personally use it I personally
because I'm always working pretty much always working on the
same machine, I just have a text expansion tool on Mac OS that
does something similar for when I'm in interactive mode.
I can understand how this becomes a little bit more.
Maybe you might use this more when you're in programming mode,
but people, yeah, I can see why some people would use it for
interactive mode.
Nikolay: Yeah, it's interesting.
There's definitely overlapping between interactive and programming
mode, at least for me.
Because when I think about set, I always already think about
\gset, right?
When the result of the output of command is used to set variables,
client-side, psql variables, So you can use them later.
And I do use this sometimes, for example, even in loop, even
with \watch.
It's interesting because, for example, you can monitor progress,
you can, for example, remember previous data, including timestamp
and calculate interval.
It's not always convenient and sometimes I switch to using server-side
variables, which like this set SQL command, right?
So \set is psql commands, set is client-side and set
without backslash is just psql command, it's server-side.
It's very similar to copy, by the way.
Copy when you, for example, export or import data,
\copy is purely client-side.
It defines where your CSV will be saved on your client machine,
if it's a different machine, or
on server.
And permissions needed, of course,
and so on.
So back to \gset, you can do such
stuff, like you remember what
happened, then you can use it in
the next query.
And This already moves us to programming
mode.
Sometimes I remember I needed to
combine client-side variables
and server-side variables.
When I do server-side variables,
usually I don't use set because
It cannot be embedded to other
queries.
It's a separate utility command,
basically.
You cannot put it inside your SELECT
or DELETE or something,
or CTE.
While there are 2 functions, quite
weird functions, actually,
because they have a second parameter
and you always need to check
documentation.
current_setting() and set_config(),
I think.
1 is setting server side, set_config()
is setting server side variable,
and the second parameter, I believe,
defines if it's local to
transaction or to whole session.
Basically global, not global for
all sessions but for two-year
session.
And current_setting() also has second
parameter, it basically reads
it, right?
Second parameter also required,
I don't remember meaning, I always
put true, yeah, like it's, because
naming is weird and that's
why it's impossible to memorize
it even if you have like me,
like 18 years of Postgres experience,
right?
This is where I usually check documentation.
And I wrote a lot of code, like
many thousands, maybe even thousands
of thousands of psql scripts.
So it's so weird I cannot memorize
it.
Yeah.
But second parameter for both these
functions is what like worth
paying attention to.
And also like set_config(),
current_setting(), they don't look like
basically doing the same thing,
but in different directions,
setting and getting, right?
It should be something gets something
said, right?
Michael: I don't know.
I'll include links to them in the
show notes.
Like I
Nikolay: would prefer to have like
GUC set and GUC get or something
like this, right?
And the good thing about server-side
variables, you can define
your own, and usually they always
go with namespace, with prefix,
so something dot something.
Because without it, it's global,
there are only roughly 300 of
them and this is how you control
configuration of your server
right so yeah and the combination
of these things gives you already
opportunities to start coding and
since as both of us know there
is also \if right we are
Michael: well I didn't know I didn't
know this until looking
into your code so it was pretty
cool
Nikolay: yeah it's kind of already
started starting to look like
Turing complete stuff.
So you can start coding in this
language, additional language
on top of SQL.
And I like it actually because
it gives you a lot of possibilities
to automate things.
If-else gives you ability to, for
example, handle different versions
of Postgres.
You can check version quickly,
memorize it in a variable, maybe
server-side, and then just using
current config function, get
it to client-side variable, and
then using if, have different
pieces of your code working with
different Postgres versions.
For example, if you want to have
something dealing with pg_stat_statements,
statements, we know they change
sometimes.
For example, in Postgres 13, more
columns were added and existing
columns were changed, so it's not
backward compatible.
That's why if you need some report,
for example, from pg_stat_statements,
you need several versions of it.
This is where if for these psql
scripts is super useful.
Michael: Yeah, Even things like
some of the really important
columns like total execution time
change name not Not look look
recently enough.
Yeah recently enough that I that
I know that change But also
planning time for example wasn't
always in there and you probably
want to add both together if you
want to look at execution time
plus planning time so when when
that's available you probably
should be summing them so yeah
I can totally see that making
sense yeah well why we like I know
this is super minor compared
to like if else if oh sorry it's
not else if it's e-l-i-f-e-l-i-f-e-l-i-f
Nikolay: yeah it's if, elif, else,
and or backslash and or and
if backslash and if yeah this is
also if you deal with multiple
languages like Python, C, Java,
Ruby and also this it's inevitable
that you will be checking documentation
when you write.
Michael: So 1 of the things that
caught my eye when I was looking
through the postgres_dba was you were just using echo
as almost like a user interface.
And 1 of them, just to show the
menu of what's available, like
what queries you can even run,
The menus, there was some weird
characters, like some weird, it's
almost like, it wasn't Unicode,
but it looked like kind of, they're
probably...
Nikolay: Emojis, you mean?
Michael: No, they weren't emojis,
it's like, it was color, it
was, I didn't know what it was,
so I pasted the echo line into
my psql and it just made the word
menu pink, like bright pink.
Yeah, yeah.
Well, that's a nice touch.
Nikolay: Right, but it's regular
terminal fun.
Yeah.
It's not psql stuff.
It's just...
Michael: I know, I know.
Nikolay: And it won't work, probably it won't work with some
pagers, right?
Some pagination stuff and in some terminals it won't work as
well.
So it's like unfortunately limited.
But yeah, it's for fun and I usually use it to distinguish like
success from from failure like errors from Successful messages
or warnings like different levels notices warnings and so on
by the way Do you know why everyone is using Raise notice or Raise
warning in PL/pgSQL while there is a Raise info and Raise debug.
This is just lack of checking documentation maybe.
Michael: Yeah probably.
Nikolay: And since we touched the \gset it's worth mentioning
also there is gexec, a very powerful thing.
I usually use it together with a format, so we can basically
have a dynamic SQL build from like you have a select query we
format you maybe by the way in some like while or something well
some loop right you have this and You build some new query and
it return it as a string right and then gexec just executes
it This is also quite powerful in some cases
Michael: Yeah, and I've seen the example in the docs But also
I've seen you do this for real when we were just testing something
together once, you can use it to if you return multiple columns,
it can execute multiple things all at once.
So you can programmatically add 100 indexes all to the same column,
I think was the example we were doing, or add the same index
to 100 different tables that all have the similar naming convention,
like that kind of thing.
Nikolay: Yeah, for example, if you want to create 100 indexes
and don't care about naming, or maybe if you care about naming,
You could just have while \watch, not while, \watch command with
specify number of loops and just format it gexes and if you want
to specify naming with some increasing integer number you can
remember what happened before and also memorize it using server-side
in this case.
Because it should be a single query, so you cannot combine G
with gexec.
It's better to have server-side using set_config() function, server-side
variable, and then you can just add 1, plus 1, and have a different
name.
But in this case, I remember we didn't care about naming, I think,
and I just didn't use name specification and create index command
and you just get hundred indexes to check overhead I think right?
Michael: Yes that was when we were checking planning time that
was the planning time episode.
Nikolay: Yeah it's fun thing that we still trying to polish that
experiment and make it more like
you know looking good and publish
blog post about this so I hope
it will be ended soon finish soon
and we will have this blog post
published so what else what else
do you want to discuss
\i or what?
\o, input output, right?
Michael: Yeah, I've seen I used
with A.
Nikolay: Yeah, \i just
import something.
It's good for programming mode
when you want to structure your
code base, psql code base.
And \o is output, outputting,
is moving output from
your like terminal to some file,
right?
And I
Michael: think it's the equivalent
of like hyphen O when you
use it from a script.
And like I've used that for so
query plans can get really big.
If people have like several megabyte
query plans, piping it to
the clipboard or sending it to
a file is way, way better for
like copy and pasting those around
than trying to copy them from
a terminal.
You know, if they're thousands
of lines long.
Nikolay: Yeah.
Right, but there's also ability
to have both, right?
To print it in terminal and save
to file.
With \o and also pipe.
Vertical line.
But important thing is that if
there are errors, they won't go
to file, right?
Unfortunately.
Michael: Interesting.
Nikolay: Yeah, yeah, yeah.
I think so.
Wait, why?
Because output only successful
results like
Michael: a
Nikolay: result set goes to not
results that also system messages
like number of rows this all goes
goes to file because the show
doesn't redirect errors, I think.
Michael: Interesting.
Yeah, good gotcha.
You can't accuse me of always looking
for gotchas and then bring
them all up yourself.
Nikolay: Maybe it's just like,
you know, it's not just developed
or something, right?
And this is also the point when
I usually, again, like go to
shell level again and use T Hyphen
a right and Use SDR as the
SDR is to the out
Michael: What's T So I'm thinking
T
Nikolay: is like.
Tee.
Michael: Oh, so sorry.
Nikolay: Not British.
Yeah.
Michael: No, I was actually thinking
of the, I've used
\t to, you know, there's the beginning
bit.
So sometimes you get stuff around
the rows.
Nikolay: So like, tuples only,
right?
Michael: So T for tuples only,
yeah, exactly.
Nikolay: No, no, this T, like,
when you want to see things and
also to save things, if you're
in programming mode, in this case,
you have your script, dot psql
script, you run it using psql,
you can have pipe T hyphen a, hyphen
n means don't overwrite,
just like append to the file, and
you can also print, t also
prints it, the output, and in this
case not to lose errors, you
can redirect errors, STD air to
STD out, using, you know, like
this, like ampersand and so on.
And in this case, you have both
errors and normal messages going
both to files and to your terminal.
And this is exactly how I like
to see things and not to lose
them with timestamps.
For example, TS from MoralTools,
TS also good thing.
You're prefixing everything with
timestamps, so you work and
you also save everything to file
so if something goes wrong you
can troubleshoot, analyze, to do
post-mortem, root cause analysis,
anything because you have all the
logs of your actions.
Michael: Yeah and the other way
around is useful as well if you
want it in the file for like if
you want the file for reasons
you're getting feedback in the
terminal that it's actually working,
that it's actually doing what you
expect.
Nikolay: Yeah you see what's happening.
Well some people like don't like
this and say let's not leave
it and no hub.
Let's use no hub.
So like detach it and it's running
in like basically in background.
And then you can observe the file
using just tail hyphen F, right?
Also fair.
And this is how you can do a lot
of quite complex coding using
psql.
And this can be building blocks
for very, very, very complex
automation.
For example, if you use Ansible,
basically it's running something
remotely.
And sometimes, like in our case,
sometimes it's a bunch of psql
lines and we'd run at them remotely
and they can be important
pieces of such things as like 0
downtime upgrades.
The only thing I must mention always
based on my mistakes from
the past and my team's mistakes.
When you do automation, don't forget,
hyphen X, capital X.
Super important.
Yeah.
Hyphen A is good.
Hyphen T is good.
Everything, but hyphen X will save
you 1 day.
Because if somebody left .psqlrc
configuration file with timing
on, for example, all your logic
comparing output to something
can be super broken and can be
unnoticed in tests, unfortunately.
Michael: So capital X ignores psqlRC?
Nikolay: Yeah, yeah.
So it switches to default behavior
and it's good for a programming
mode.
psqlRC is good for interactive
mode.
It's bad for a programming mode
because if you have a new server
and you don't have this...
Well, it depends on some organization,
right?
Maybe we have a rule to put some
specific psqlRC everywhere.
In this case, it's okay.
But if you don't know what will
happen in the future, it's better
to just ignore those adjustments.
Michael: I like doing it with X.
If it's a script, it's a script
already, why not set any config
you want in the script and then
use hyphen X like it that makes
way more sense to me than relying
on something else
Nikolay: maybe maybe there are
options here.
It's good to develop something
here, not just to forget about
this.
This happens all the time, people,
and I did it as well.
We forget it, and then on staging
we don't have psqlRC, on production
we have, and boom, it's not working.
Or even worse it's working in the
wrong way right
Michael: yeah good point yeah nasty
Nikolay: that's why I like hyphen
X hyphen capital X okay yeah
there are like we maybe touched
like couple of percent of what's
possible as usual right We wanted
to call it advanced psql, right?
But there are many more things.
Michael: So we can point people
out, I'll include a link to the
psql docs in the show notes, and
also to, there's a site by Lætitia
Avrot, we mentioned last time
as well, called psql tips.
And if you go to it, it's a website
that will just give you a
random tip about psql.
Every, you know, if you could set
your homepage to it, or your
new tab page to it, and you might
learn the odd.
That's like a way of discovering
new things that you might not
know it can do, which is quite
cool.
Nikolay: Yeah, let me mention a
few more things I wanted to mention.
If you find yourself spending too
much time inside psql like
I do, you will probably like
\! mark because
it can run anything, right?
You can, for example, run LS, PS,
stop, anything you want, SSH.
So it's basically running something,
some shell right from psql.
And also I like the fact that I
think in Postgres 16 it started
to be possible to work with extended
protocol and to debug some
stuff from there.
And also I like, I don't use it,
but it's so quite powerful.
You can run multiple, you can send
multiple statements in 1 shot.
If you, instead of semicolon use
\:.
Right, in this case, you can combine
many different queries and
send them at once.
Sometimes it's also...
Michael: Are these documented?
I don't remember reading about
these.
Nikolay: Yeah, it should be documented.
Michael: That's quite cool.
Nikolay: Yeah, so I don't use it
often but it's quite powerful,
it also shows like you can do so
many things right inside psql.
Michael: Yeah I just did
\? which by the
way is like at least 50 lines of
information straight away, and
then \!
is listed even in that.
Nikolay: \!
I use all the time, Because
I just don't want to quit from
psql, right?
Michael: And
Nikolay: we don't need to mention
that quitting from psql is
\q because you can right
now write exit or quit Since
I don't know I don't from version
14 15.
I don't remember when it was added
maybe earlier So this is not
Michael: to be the easiest to quit
command-line tool ever
Nikolay: It's not VI style anymore,
right?
So yeah, a lot of stuff is possible
and it's good that Again,
this is the only 1 single official
client in Postgres project.
So it's worth learning it and using
it more.
We mentioned in the episode about
comparing it to graphical interfaces.
We mentioned that terminal is good
for expected automation.
Like, basically for automation,
you have expected behavior, you
just program something and you
can put it to CI-CD pipelines
or anywhere.
Right?
Unlike if you have some graphic
interface, what else?
Like, it's not good.
Of course, you can use cloud computer
use which was released
yesterday I'm joking I'm trying
to like insult you already so
yeah
Michael: well I don't think it's
a great argument in terms of
the, like, you can use a graphical
tool to come up with the query
you want to run, like, via the
shell.
But yeah, some of the interactive,
I'm saying interactive when
I mean the opposite some of the
stuff we've been talking about
to programmatically use psql
it makes sense like obviously
you're going to use psql eventually
so you might as well
do it but I still find it some
sometimes easier to play around
with queries in a graphical interface,
personally.
I understand that.
Like editing them and things.
Nikolay: Yeah, I understand that, of course.
If it's a huge Query and you are not using a VI by default, I
can understand this.
Michael: Yeah, yeah, good point.
Cool.
Nikolay: Good, yeah, I like this thing and hopefully we will
see development of this further and further.
I mean, psql features.
I feel potential to have more and more.
Good, thank you.
Michael: That's all, Nikolay.
Thanks so much.
Nikolay: Thank you.