Postgres FM - Advanced psql

Episode Date: October 25, 2024

Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres. Here are some links to things they mentioned:psql docs https://www.postgresql.org/docs/current/app-psql.html Our episode on psql vs GUIs https://postgres.fm/episodes/psql-vs-guispostgres_dba https://github.com/NikolayS/postgres_dbaOur episode on massive deletes https://postgres.fm/episodes/massive-deletesPostgres hacking session on \watch with limited number of loops https://www.youtube.com/watch?v=vTV8XhWf3mo pspg https://github.com/okbob/pspg Our episode on Postgres gotchas https://postgres.fm/episodes/postgres-gotchascurrent_setting() and set_config() docs https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SETpsql tips (site by Lætitia Avrot) https://psql-tips.org~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith special thanks to:Jessie Draws for the elephant artwork 

hello hello this is posgus fm i'm nikolai from posgus ai and as usual with me here michael from pgmaster hi michael how are you nikolai i am good how are you okay uh no how are you i'm very good i'm very good okay so we are going to discuss PSQL or 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 maybe some tips and tricks right

yeah looking forward to this one you say last time we talked about it but it i do you know it was like 18 months ago yeah well i i i was trying to say we discussed it last time i mean in this area when we discussed psql particularly right i just couldn't believe it when I looked it up how long ago that was. 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 well yeah so i don't feel like i'm an advanced user of psql personally i have to use well i i try to use it sometimes i try to be keep familiar with it so that if i need to um 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 for my mobile or i like yeah so if i like a console i

can do that it's 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 though 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 one 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 the what was the program called that you said is it postgres dba postgres underscore dba ah postgres dba yeah 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. Or maybe a user of, I don't know, Supabase. PSQL maybe it's not that natural because there

is editor in, or Neon for example, there is an editor right in browser and maybe it's not that natural because there is editor in or neon, for example, there is an editor in written browser and maybe it's closer for this particular packaging. I don't know. Like there's no PC call by default there. Right. 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. That's good. Yeah. Yeah. Some you can there's a console there's a cloud console that you can have you can get access to that's good yeah yeah some you can 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 but I assume so yeah yeah this is good but others don't do this unfortunately for me I would prefer everyone to provide a I assume so, yeah. type of 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 client, but it's very specific but it's for 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 uh it's not regular client because it's the only like it's 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 one. There is no graphical tools, graphical interfaces officially supported by Postgres itself. Postgres is shipped with Postgres and so on. And pgAdmin is also a third-party tool. Yeah. Right? So, which means the release cycle, quality, features, you can see changes, new stuff added to PSQL, right? And release notes of Postgres itself, this is good, right?

Quality and also trust, right? So, when you say explain plans plants formatting can be very strange. Well, from PSQL, you actually said it's from PSQL can be very strange. But yeah, and I'm going on purpose to mix PSQL and PSQL as we discussed. Because I don't care about this. Same. Yeah. So this is a specific case, plans, right?

I know this pain of bad formatting, shitty formatting, and you need to deal with it, and it's a lot of pain. But in all other areas, when you do something, you expect behavior from PCQL, 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 like many million rows

or convert some table to something. I don't remember. Maybe it was integer 4 to integer 8, 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.psql. So it emphasizes that it's not only pure SQL, but they can have some comments-only PSQL supports,

like backslash if, for example, and we will talk about it in a few minutes. And something was interesting. 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 IPO a few months after we started working with them. It was great. And they coded similar stuff, but in their system. It was great. And they coded similar stuff, but in their system, it was Java, actually. And the same queries didn't work well. I mean, they didn't work at all. And when I started troubleshooting, I noticed that in one of 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 P-SQL. And this discrepancy in behavior, it can be a big deal. If you want to have something reliable and supported for many years, it's good to deal with P-SQL. That's a great point. I use it for debugging purposes as well.

It helps rule something out. If I'm seeing some application via my application or via a graphical user interface and it behaves the same in ps p sql i'm gaining confidence that there's a prop like it helps rule out i assume there's not a bug in p sql like that's a default assumption of mine i'm probably wrong probably going to trip me up one day but it hasn't so far yeah sitting in psql talking to postgres you're basically closer to postgres right there is no there's there's like jdbc adds something and if it's something third-party tool it also adds something and this something like these layers can can change uh 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. And trust is not like high in this area. And there's always doubt. Am I seeing this?

This is spike really like what I'm thinking it is. So this is about trust and so on. And also features. 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.

Yeah, so actually I had a question for you on that front. When I was looking through the docs page for P-SQL, the number of options and flags, there's just so many. When you're working with it 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 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 backslash question mark yeah and backslash h for help for with sql and there is like a lot of documentation

it's right there so like help and the grammar of sql itself it's there but sometimes i see something which like i'm, is it possible? For example, if we want to process a lot of rows and batches, of course it's good to write something, some full-fledged program using Python or Go or anything with monitoring, logging, but if you need it quickly, my usual approach, ad hoc processing, it's needed really soon. We need the result right now. I just write a PC code using backslash watch.

And I was always like, okay, watch is good. It's for looping. You run some query which takes a batch and process it. For example, deleting. 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.

We did an episode. Yeah, yeah. So massive delete can hit you. If you need to delete 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 1,000 rows or something. Delete them and report in a nice way, maybe even with progress bar, which is what I like. And not to forget about vacuum, of course, and that tuples and processing. We talked about it. Yeah, yeah, yeah.

But, okay, back to, so we're talking about watch, back-to-last watch. Right. Badly named, probably. What do you think of the name? Watch? Yeah. Well, yeah, it was created, I think, to, you know, like you have a select from Puget's activity. You, for example, aggregate queries by state, understand like how many active backends we have, maybe wait events as well. And you just observe them.

And that's why watch, I think. I get it. But like repeat or something that gives it a little bit more of like an idea of what it's actually going to do. Your mind definitely is targeted to find gotchas. I'm with you here. It's not perfect naming at all. Yeah. 100%. 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 when I was preparing for a few episodes ago. Alexander Kokushkin used watch in one 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 really cool use case to continually query a database.

Right, right. Exactly. Watch is useful, but it's until, I think it was not until Postgres 16 when we had, with Andrej and Kirk, we had Postgres 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 in other 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 one comment finished before running the next one.

Right. And the idea was like, before Postgres 16, the only option was like specified time, by default, I think it's two seconds, I don't know why two seconds specify some time you can specify like 0.1 100 milliseconds slip time like very like 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... Interesting. 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 query fails, still continue with the loop. In this case... Oh, like the failover testing. If you're doing failover testing, even if one of them fails... Yes, exactly.

Exactly, exactly exactly but watch behaves like it just stops and if if i need this uh behavior i'm i'm 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 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,

control C won't work properly in many cases. It's very annoying. If you sleep first, then do something. So while sleep 1, well well how many seconds you want to have between running psql in this case if ctrl 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? I was reading through what the, well, you're mentioning some improvements that have happened in PC SQL recently, which is cool. 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 one of the more recent... I'm not sure it fixes that. I'm not sure it's related. Well, it's interesting. Maybe, maybe. I need to check. 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? Yes, yeah. 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, the named options. So we added like we converted this not interval, how to properly say, like the break between sleep time between two comments executed. Yeah, sleep time is good.

Yeah, I think we call it sleep. By the way, since I don't see Postgres 16, it was released in 16, I don't see 16 too often in production. I've still not got used to this, what we developed, right? This is 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 servers. 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 second option was, and this is the whole purpose of that work, is to allow us to specify a number of loops you need. If you know you don't need more than something, that's it. But interesting fact that the inspiration of that yeah i'm checking it's interval i number of seconds it's wrong naming but it is it is interval uh yeah with understanding it's not actually interval because it's interval not taking into account the duration of the comment executed, which can take actually minutes or like hours in extreme cases. And yes, C or count equals something. This is a number of loops you need, right?

And that's great. But actually, a reason 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 zero. This is a very old trick. It works.

I know watch will stop if there is an error. So I just, when nothing to process anymore, I just divide by number of rows to process. It's zero, so it stops because of division by zero. And interesting that 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 zero. If we have zero, that's it. This is great, right? Yeah, again, a weird name, like, is it it zero or is it one a number of rows returned if you return for example if you delete returning star and it's returned zero then it's it's not you need to stop ah you think uh it should be yeah well let's double check but let's not let's not to be like super we are not providing some like lesson or reference we are talking what's possible yeah there is documentation and you also you can try and learn but so now it's possible you don't need division by zero 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 things 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 i always have some rows reported in result like usually just one row many columns like number of rows like we, what's left, like progress, percentage, anything. So I think, will I still use division by zero after I got used to Postgres 17? I'm not sure, right? Should we move on?

Yeah, let's move on. And I wanted to emphasize there are a couple of areas. There are two big areas. First big area where you can use physical. 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 backslash GX instead of semicolon. By the way, both watch and GX don't require semicolon. I know even Andrea learned it from me. It's not obvious. They can be your end of command semicolon, replacement for it. So GX, I looked this up. It was send the last query. Send that to the server, right? 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 one,

GX is much better because it's equivalent to switching to expanded mode using backslash X. So you see one column on one line, second column on 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, backslash 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 installed. PSPG, I didn't mention. I do like PSPG, which provides much better output and pagination in PSQL. Very great addition to PSQL. Yeah, it's pretty cool. It even does horizontal scrolling within the terminal.

It's pretty cool. And did you know PSPG is even mentioned in the PSQL docs? That's pretty 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 unfortunately no because it's third i think postgres docs don't mention third-party tools almost maybe except pg admin i don't know if pg admin is mentioned in postgres docs i have not seen it there but i it's very rare to see a third-party tool mentioned in the documentation in my experience at least maybe never well this is this is third party though pspg yeah it's third party so not never. It's not mentioned. PSPG isn't mentioned?

It is. Yeah. It's a surprise for me. Yeah, that's why I said. In PSQL, it's mentioned under paging. Like it makes sense. That's great. It's under pager.

That's great. 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 backslash gx, backslash watch and backslash 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 extra extra call right okay what else let's talk about interactive mode and what like tricks are worth

knowing additionally in addition to backslash gx what's so when you say interactive mode do you mean like a user um like an doing administrative tasks or doing some ad hoc queries yeah okay great yeah yeah what what are your favorite things when you rarely touch psql i think they're not well mine aren't going to be mine aren't going to be on the advanced side of things but yeah some of the alignment things are quite useful so like backslash a can toggle off alignment if for any reason you want it. So I complained about things at the beginning. What's the reason to turn off alignment?

If you're doing some stuff with JSON, for example, the JSON output, you don't want a pretty nice... 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 data.

I always use it when I want to see the body of function or trigger function or stored procedure. Yeah. Yeah. So if you use backslash DF or SF, it was changed, right? DF plus. df or sf it was changed right df plus i you i always used backslash df plus but i think it got like this behavior was duplicated or removed and you need to use backslash sf or something so you need to see function using s and in this case if you don't run backslash a before that the formatting will screw all the indentation of the code, and it's hard to understand.

But this is also my habit. Backslash A, then backslash SF+, 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 backslash EF. And you know, this is probably also gotcha. It's not production. You can edit the function using backslash EF. And you know, this is probably also gotcha. We have my mind is also this echo from last episode. And those who didn't listen to it, it was an interesting episode to me as well. So when you use backslash EF, and I usually use VI, right? VI, I'm a big VI fan.

You go to VI, you edit everything. Then you, as usual, like colon W Q, like save and exit, write and quit. And then, you know this or no? I'm not a VI user, but I've read it. Backslash E is one of those tips that often comes up. You can just use it to edit anything. Right, but 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 it's... Transaction is not closed, right? You need a 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 thinking why is it so i i know i know like there should be some explanation why it's so but yeah so after backslash ef always semicolon yeah backslash gx uh well interesting i don't know if that would work yeah maybe okay maybe what else i like a backslash set and I use it from time to time.

There are two cases. I use it when I need some variable. Of course, this moves us already to programming mode, not interactive mode. There's overlap, right? Right, there's overlap for sure. I like to use it sometimes to set something to have short comment, for example.

For example, we talked about buffers in explain analyze a lot, and you can just define some like colon EAB, for example, or just colon something. I don't know. In my docs, 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 macOS 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. Yeah, it's interesting. There's definitely overlapping between interactive and programming mode, see why some people would use it for yeah interactive mode yeah it's interesting there's definitely definitely overlapping between interactive and programming mode at least for me

because when i think about set i always already think about g set right when the result of the output of comment 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 comment right so backslash set is psql comments set is its client side and set without backslash is just SQL commented server-side. It's very similar to copy, by the way. Copy, for example, export or import data, backslash copy is purely client-side.

It defines where your sysview 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 G-set, 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. And sometimes I remember I needed to combine client side variables and server side variables and when i do server side variables i usually i don't use set

because it's like it's it cannot be embedded to to other queries right it's a separate like utility command basically right and like? And you cannot put it inside your select or delete or something, or CTE, right? While there are two functions, quite weird functions, actually, because they have second parameter, and you always need to check documentation. Current setting and setConfig, I think. Yeah, one is setting server-side, setConfig is setting server side, set config is setting server side variable. And second parameter, I believe, defines if it's local to transaction or like to whole session, basically global, not global for all sessions, but to your 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 of, maybe even dozens 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 i don't i'll include links to them in the show notes like so we can all i 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 like they usually like they always go with namespace with prefix so something dot something because without it it's global you you like there are only roughly 300 of them and this is how you control configuration of your server right so yeah and combination

of these things gives you already opportunity to start coding and since as both of us know there is also backslash if right we are well I didn't know I didn't know this until looking into your code so it was pretty cool 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 PGSA 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 PGSR statements, you need several versions of it. This is where if for these PSQL scripts is super useful. Yeah, even things like some of the really important columns like total execution time, change name, not, not recently enough. Yeah, recently enough 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 that's available, you probably should be summing them. So, yeah, I can totally see that making sense yeah why would like i know this is super minor compared to like if else if oh sorry it's not else if it's eli elif yeah it's if elif else and or backslash and or and if backslash and if yep 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. So one of the things that caught my eye when when i was looking through postgres underscore dba was you you're just using echo as like almost like a user interface and one of them uh just to show the menu of what's available like what 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 emojis i mean oh no they weren't emojis they were it's like it was color it was i didn't know what it was so i i pasted the the echo line into my psql and it just made the the word menu pink like bright pink yeah yeah well that's a nice touch right but it's regular terminal fun. Yeah. It's not PSQL stuff. I know, I know. 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 pg scale while there is a raise info and raise debug. This is just lack of checking documentation, maybe. Yeah, probably. Yeah, and since we touched G set,

it's worth mentioning also there is G exec, very powerful thing. I usually use it together with a format, so we can basically have a dynamic SQL build. You have a select query with format. Maybe by the way in some while or something, or some loop, right? You have this and you build some new query and you return it as a string, right? And then JExec just executes it.

This is also quite powerful in some cases. 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. 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 specified number of loops and just format it 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 single query, so you cannot combine GX with GXX, right? And it's better to have server-side using setConfig function, server-side variable. And then you can just add one, plus one,

and have different name 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 create 100 indexes to check overhead i think right yes that was when we were checking planning time that was the planning time episode yeah it's fun thing that we're still trying to polish that experiment and make it more like, you know, looking good and publish a blog post about this. So I hope it will be ended soon, finished soon,

and we will have this blog post published. So what else do you want to discuss? Backslash I or what? Backslash O, input-output, right? Yeah, I've seen I used with A. Yeah, backslash I just imports something. It's good for programming mode when you want to structure your code base,

PSQL code base. And backslash O is output, is moving output from your terminal to some file, right? I think it's the equivalent of hyphen O when you use it from a script. I've used that so query plans can get really big. If people have 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 yeah right but uh there's also ability to have both right to print it in terminal and save to file with backslash O and also pipe vertical yeah roll up line but important thing is that if there are errors they won't go to file right unfortunately interesting yeah yeah yeah I think so wait why because output only successful results like like result set goes to, not result set, also system messages like number of rows, this all goes to file.

Backslash show doesn't redirect errors, I think. Interesting. Yeah. Good gotcha. You can't accuse me of always looking for gotchas and then bring them all up yourself. Yeah. Maybe it's just like you know it's it's not just developed or something

right and I this is also the point when I usually again like go to shell level again and use T hyphen A right and use SDR SDR out hyphen a right and uh use uh sdr sd sdr sd out uh and what's t so i'm thinking t is like

oh so sorry not not yeah no i was actually thinking of the i've used backslash t to you know there's the beginning bit and so sometimes you get stuff around the rows so like tuples only right so t for tuples only yeah exactly no no this t like when you want to see things and also to say things if you're in programming mode, in this case, you have your script,.psql script. You run it using psql. You can have pipe t-a. Hyphen n means don't overwrite, just append to the file. And you can also print, t also prints the output.

And in this case, not to lose errors, you can redirect errors, std error to std out, 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 more hotels 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. 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 it to do. 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 tailed 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 PC SQL lines and we run them remotely and they can be important pieces of such things as zero 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. Hyphen A is good, hyphen T is good, everything. But hyphen capital X will save you one 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. So capital X ignores psqlrc. 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. I like doing it with X. If it's a script, like it's a script already, why not set any config you want in the script and then use hyphen x like that makes way more sense to me than relying on something else maybe maybe there are options here it's good to develop something here not just to 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 a PSQLRC, on production we have

and boom it's not working. Even worse it's working in the wrong way. Yeah good point. Yeah nasty. That's why I like hyphen capital X. Okay yeah there are like we maybe touched a couple of percent of what's possible, as usual, right? We wanted to call it advanced PSQL, right? But there are many more things. So we can point people out. I'll include a link to the PSQL docs in the show notes. And also, there's a site by Leticia Avro, we mentioned last time as well, called PSQL Tips.

And if you go to it, it as a website that will just give you a random tip about psql every you know if you if you could set your home page to it or your net like new tab page to it and you might learn the odd that's like some a way of discovering new things that you might not know it can do which is quite cool yeah let me let me mention a few more things i wanted to mention if you like find yourself spending too much time inside PSQL like I do, you will probably like backslash exclamation mark because it can run anything. You can, for example, run LS, PS, stop, anything you want, SSH.

So it's basically running some some 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 one shot if you instead of semicolon use backslash semicolon. In this case, you can combine many different queries and send them at once. Sometimes it's also good. Are these documented?

I don't remember reading about these. Yeah, it should be documented that's quite cool yeah so i don't use it often but it's quite powerful it also shows like you can do so many things in right inside psql yeah i just did backslash backslash question mark which by the way is like at least 50 lines of information straight away. And then, yeah, backslash exclamation mark is listed even in that. Backslash exclamation mark I use all the time because I just don't want to quit from PSQL, right?

And we don't need to mention that quitting from PSQL is backslash Q because you can right now write exit or quit since I don't need to mention that quitting from PSQL is backslash queue 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... Possibly the easiest to quit command line tool ever. It's not VI style anymore, right?

So, yeah. A lot of stuff is possible and it's good. Again, this is the only one 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 cicd pipelines or anywhere right unlike like if you have some graphic interface what else like it's not good of course

you can use uh cloud computer use which was released yesterday i'm joking i'm trying to like insult you already so yeah 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 it uh via the shell like it's but yeah some of the interactives so no 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 editing them interface personally. I understand that.

Editing them and things. Yeah, I understand that, of course. If it's a huge query and you are not using VI by default, I can understand this. Yeah, good point. Cool. Good, yeah, I like this thing. And hopefully we will see development of these further and

further. I mean, PSQL features, I feel potential to, to have more and more. Yeah. Good. Thank you.

Nice one, Nikolai. Thanks so much. Thank you. Catch you next week.