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.