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.