1 00:00:00,060 --> 00:00:01,980 Nikolay: Hello, hello, this is Postgres.FM. 2 00:00:02,080 --> 00:00:07,200 I'm Nikolay from Postgres.AI, and as usual with me here Michael 3 00:00:07,200 --> 00:00:08,080 from pgMustard. 4 00:00:08,320 --> 00:00:09,020 Hi, Michael. 5 00:00:09,720 --> 00:00:10,780 Michael: How are you, Nikolay? 6 00:00:11,320 --> 00:00:12,760 I'm good, how are you? 7 00:00:14,060 --> 00:00:14,560 Okay. 8 00:00:15,480 --> 00:00:16,660 No, how are you? 9 00:00:17,020 --> 00:00:18,740 Nikolay: I'm very good, I'm very good. 10 00:00:20,380 --> 00:00:26,680 Okay, so we are going to discuss psql once again. 11 00:00:26,980 --> 00:00:31,720 Last time we compared it to graphical UIs, graphical tools. 12 00:00:32,780 --> 00:00:37,680 And this, like, it was my idea to discuss it once again, but 13 00:00:37,680 --> 00:00:39,660 just purely just psql. 14 00:00:39,720 --> 00:00:44,580 How to use it in various cases, why use it, and and maybe some 15 00:00:44,580 --> 00:00:45,980 tips and tricks, right? 16 00:00:47,680 --> 00:00:49,640 Michael: Yeah, looking forward to this 1. 17 00:00:49,640 --> 00:00:53,300 You say last time we talked about it but it was like 18 months 18 00:00:53,300 --> 00:00:53,800 ago 19 00:00:54,060 --> 00:00:59,060 Nikolay: yeah I was trying to say we discussed it last time I 20 00:00:59,060 --> 00:01:03,820 mean in this area yeah and we discussed psql particularly right 21 00:01:03,820 --> 00:01:03,960 I 22 00:01:03,960 --> 00:01:06,180 Michael: just couldn't believe it when I looked it up how long 23 00:01:06,180 --> 00:01:06,820 ago that was. 24 00:01:06,820 --> 00:01:07,480 So yeah, 25 00:01:08,300 --> 00:01:13,200 Nikolay: your mind is focused, is targeted to find gotchas everywhere 26 00:01:13,220 --> 00:01:17,480 since last episode and You try to find it in my speech now. 27 00:01:17,480 --> 00:01:18,660 Okay, this is good. 28 00:01:19,080 --> 00:01:22,000 But just for today, let's focus on psql. 29 00:01:22,360 --> 00:01:26,260 And I have some experience, I like it, which is good, right, 30 00:01:26,540 --> 00:01:27,320 for conversation. 31 00:01:28,080 --> 00:01:31,500 You said you don't have a lot of experience with it, but you 32 00:01:31,500 --> 00:01:36,340 may be better prepared as usual so maybe you know some recent 33 00:01:36,340 --> 00:01:37,500 changes or something. 34 00:01:38,080 --> 00:01:39,220 Where can we start? 35 00:01:39,220 --> 00:01:42,100 Let's maybe discuss why, right? 36 00:01:42,440 --> 00:01:45,280 Because we have Python, we have various tools. 37 00:01:46,300 --> 00:01:47,120 Why psql? 38 00:01:48,500 --> 00:01:50,240 What's your answer to this? 39 00:01:51,380 --> 00:01:55,020 Michael: Well, yeah, so I don't feel like I'm an advanced user 40 00:01:55,020 --> 00:01:56,620 of psql, personally. 41 00:01:56,760 --> 00:02:00,880 I have to use, well, I try to use it sometimes, I try to keep 42 00:02:00,880 --> 00:02:03,380 familiar with it so that if I need to. 43 00:02:03,960 --> 00:02:06,360 The nice thing about psql is it's always available. 44 00:02:06,360 --> 00:02:07,860 We talked about this last time. 45 00:02:08,100 --> 00:02:11,940 But if it's somebody else's database or it's my own, it's always 46 00:02:11,940 --> 00:02:15,620 available if I need to do it from my mobile, via like, yeah, 47 00:02:15,620 --> 00:02:18,480 so if I like a console, I can do that. 48 00:02:18,520 --> 00:02:19,620 It's very accessible. 49 00:02:20,320 --> 00:02:23,140 But I don't feel like I'm an advanced user the reason I've had 50 00:02:23,140 --> 00:02:26,140 to get more familiar with it as well there is like helping diagnose 51 00:02:26,160 --> 00:02:30,720 issues, so I see a lot of query plans and query plan formatting 52 00:02:30,720 --> 00:02:35,720 can vary quite a lot depending on the editor and psql or psql 53 00:02:35,900 --> 00:02:39,780 has a bunch of formatting and alignment options and different 54 00:02:39,960 --> 00:02:44,720 pages and each 1 has its own formatting quirks so supporting 55 00:02:44,800 --> 00:02:48,320 all those different formats for people copy and pasting, EXPLAIN 56 00:02:48,320 --> 00:02:50,540 plans has given me some familiarity as well. 57 00:02:50,540 --> 00:02:53,720 But yeah, on the coding side of things, I don't have much experience 58 00:02:53,720 --> 00:02:54,360 at all. 59 00:02:54,480 --> 00:02:57,100 But I did check out what was the program called? 60 00:02:57,260 --> 00:02:58,380 Is it postgres_dba? 61 00:02:58,380 --> 00:02:59,720 postgres_dba? 62 00:03:00,280 --> 00:03:01,560 Nikolay: postgres_dba, yeah. 63 00:03:01,560 --> 00:03:06,420 This is a bunch of tools and interactive menu I've built using 64 00:03:06,420 --> 00:03:08,360 some tricks in psql. 65 00:03:08,800 --> 00:03:13,140 It was long ago, but yeah, I still use it sometimes and some 66 00:03:13,140 --> 00:03:13,860 people use it. 67 00:03:13,860 --> 00:03:14,840 It's quite interesting. 68 00:03:15,620 --> 00:03:16,120 Right. 69 00:03:16,320 --> 00:03:21,440 So it sounds like you say it's always available and I partially 70 00:03:21,560 --> 00:03:22,060 agree. 71 00:03:23,400 --> 00:03:26,760 It's available if you install Postgres with client packages. 72 00:03:28,580 --> 00:03:32,960 If you install only server packages or if you are a user of RDS, 73 00:03:33,260 --> 00:03:35,740 it's not available, right? 74 00:03:35,740 --> 00:03:38,600 Or maybe user of, I don't know, Supabase. 75 00:03:39,480 --> 00:03:43,440 psql, maybe it's not that natural because there is editor in, 76 00:03:43,440 --> 00:03:47,440 or new 1, for example, there is an editor right in browser and 77 00:03:47,440 --> 00:03:52,360 maybe it's closer for this particular packaging, I don't know, 78 00:03:52,360 --> 00:03:54,940 like, there's no psql by default there, right? 79 00:03:55,200 --> 00:03:58,520 Michael: Some cloud providers you can, so I know on Google Cloud, 80 00:03:58,660 --> 00:04:02,300 which is what I use myself, you can, there's a console, there's 81 00:04:02,300 --> 00:04:06,060 a cloud console that you can have that you can get access to 82 00:04:06,560 --> 00:04:07,760 Nikolay: that's good yeah 83 00:04:08,040 --> 00:04:09,180 Michael: yeah some you can 84 00:04:09,400 --> 00:04:13,940 Nikolay: and I think this is not they do it not bringing psql 85 00:04:13,980 --> 00:04:19,300 to browser but maybe like emulating console running on server. 86 00:04:19,300 --> 00:04:20,460 This is what I suspect. 87 00:04:20,680 --> 00:04:21,840 Michael: I assume so, yeah. 88 00:04:21,880 --> 00:04:25,020 Nikolay: Yeah, this is good, but others don't do this, unfortunately, 89 00:04:25,440 --> 00:04:26,000 for me. 90 00:04:26,000 --> 00:04:30,920 I would prefer everyone to provide a psql and make it available 91 00:04:31,360 --> 00:04:34,780 like it is when you deal with pure open source Postgres. 92 00:04:35,540 --> 00:04:36,040 Right? 93 00:04:37,580 --> 00:04:42,780 So, to me, the reason why psql, there's a bunch of reasons. 94 00:04:44,320 --> 00:04:49,900 First of all, it's the only official and well-maintained client. 95 00:04:51,060 --> 00:04:54,220 If we consider all the clients regardless, like terminal-based 96 00:04:54,520 --> 00:04:56,580 or graphical, doesn't matter. 97 00:04:56,580 --> 00:04:58,940 This is the only officially maintained client. 98 00:04:59,200 --> 00:05:03,100 Well, if we don't count pgbench as client, right? 99 00:05:03,100 --> 00:05:05,700 Because it's also a client but it's very specific. 100 00:05:06,100 --> 00:05:10,120 It's very specific, it's also official, well-maintained, somehow 101 00:05:10,340 --> 00:05:13,740 included to server packaging for Ubuntu and Debian, I don't know 102 00:05:13,740 --> 00:05:15,560 why, unlike psql. 103 00:05:16,080 --> 00:05:21,940 But it's not a regular client because it's needed for different 104 00:05:21,940 --> 00:05:26,980 tasks, for benchmarks, for research of performance and so on. 105 00:05:27,180 --> 00:05:31,400 psql is a universal client, terminal-based, and this is the only 106 00:05:31,400 --> 00:05:31,560 1. 107 00:05:31,560 --> 00:05:35,240 There is no graphical tools, graphical interfaces officially 108 00:05:35,740 --> 00:05:37,940 supported by Postgres itself. 109 00:05:38,560 --> 00:05:41,320 Postgres shipped with Postgres and so on. 110 00:05:41,320 --> 00:05:44,780 And pgAdmin is also a third-party tool. 111 00:05:46,620 --> 00:05:52,860 Which means the release cycle, quality, features, you can see 112 00:05:52,960 --> 00:05:58,940 changes, new stuff added to psql, in release notes of Postgres 113 00:05:58,940 --> 00:06:00,140 itself, this is good. 114 00:06:00,380 --> 00:06:03,380 Quality and also trust. 115 00:06:04,340 --> 00:06:09,940 So when you say explain plans formatting can be very strange. 116 00:06:10,800 --> 00:06:14,340 Well, from psql, you actually said it's from psql can be very 117 00:06:14,340 --> 00:06:19,760 strange, but I'm going on purpose to mix psql and psql as we 118 00:06:19,760 --> 00:06:20,660 discussed, right? 119 00:06:20,900 --> 00:06:23,040 Because I don't care about this. 120 00:06:23,200 --> 00:06:23,700 Michael: Same. 121 00:06:24,240 --> 00:06:28,520 Nikolay: Yeah, so this is specific case plans, right? 122 00:06:29,060 --> 00:06:33,580 I know this pain of bad formatting, shitty formatting, and you 123 00:06:33,580 --> 00:06:36,600 need to deal with it, and it's like, it's a lot of pain. 124 00:06:36,740 --> 00:06:42,100 But in all other areas, when you do something, you expect behavior 125 00:06:42,380 --> 00:06:45,580 from psql, like the true behavior, right? 126 00:06:45,660 --> 00:06:47,020 I can give you an example. 127 00:06:47,320 --> 00:06:52,580 I had some small project to convert something. 128 00:06:52,580 --> 00:06:53,300 I don't remember. 129 00:06:53,300 --> 00:06:57,700 Maybe to DELETE many million rows or convert some table to something. 130 00:06:57,700 --> 00:06:58,260 I don't remember. 131 00:06:58,260 --> 00:07:01,940 Maybe it was int4 (primary key conversion). 132 00:07:02,540 --> 00:07:07,540 So we decided to code it purely in psql and SQL. 133 00:07:07,640 --> 00:07:09,220 So you just combine files. 134 00:07:09,220 --> 00:07:11,920 I like to name such files dot psql. 135 00:07:12,160 --> 00:07:16,320 So it emphasizes that it's not only pure SQL, but they can have 136 00:07:16,320 --> 00:07:21,180 some commons only psql supports, like \if, for example, 137 00:07:21,180 --> 00:07:24,020 and we will talk about it in a few minutes. 138 00:07:24,620 --> 00:07:29,980 And something was interesting, like some query was working there, 139 00:07:30,200 --> 00:07:31,220 everything as expected. 140 00:07:31,220 --> 00:07:32,800 We had tests, all good. 141 00:07:33,820 --> 00:07:36,820 But attempt by some other developers in that company, it was 142 00:07:36,820 --> 00:07:37,960 a huge client. 143 00:07:37,960 --> 00:07:42,340 They went to IPR like a few months after we started working with 144 00:07:42,340 --> 00:07:42,840 them. 145 00:07:43,040 --> 00:07:43,880 It was great. 146 00:07:44,100 --> 00:07:47,860 And they coded like similar stuff, but in their system. 147 00:07:47,860 --> 00:07:49,120 It was Java, actually. 148 00:07:49,980 --> 00:07:52,260 And same queries didn't work well. 149 00:07:52,260 --> 00:07:53,980 Like, I mean, they didn't work at all. 150 00:07:54,520 --> 00:07:59,020 And when I started troubleshooting, I noticed that in 1 of the 151 00:07:59,020 --> 00:08:04,340 graphical UIs, it's absolutely the same behavior as in their 152 00:08:04,340 --> 00:08:04,840 code. 153 00:08:05,640 --> 00:08:09,860 And that graphical program was also built using Java. 154 00:08:10,320 --> 00:08:12,840 So I realized it's JDBC. 155 00:08:13,660 --> 00:08:17,960 It was something with float, rounding, rules or something. 156 00:08:17,960 --> 00:08:21,720 It was very different behavior compared to what I saw in psql 157 00:08:22,060 --> 00:08:28,440 and this discrepancy in behavior it can be big deal if you want 158 00:08:28,440 --> 00:08:33,560 to like to have something reliable and supported for many years, 159 00:08:33,560 --> 00:08:35,100 it's good to deal with psql. 160 00:08:35,660 --> 00:08:36,680 Michael: That's a great point. 161 00:08:36,680 --> 00:08:38,860 I use it for debugging purposes as well. 162 00:08:38,860 --> 00:08:40,840 Like, it helps rule something out. 163 00:08:40,840 --> 00:08:45,560 If I can, if I am seeing some behavior from it via my application 164 00:08:45,760 --> 00:08:51,240 or via a graphical user interface, and it behaves the same in 165 00:08:51,340 --> 00:08:54,480 psql, I'm gaining confidence that there's a problem. 166 00:08:54,620 --> 00:08:56,060 It helps rule out. 167 00:08:56,320 --> 00:08:58,520 I assume there's not a bug in psql. 168 00:08:58,780 --> 00:09:00,720 That's a default assumption of mine. 169 00:09:00,720 --> 00:09:04,160 I'm probably wrong, probably gonna trip me up 1 day, but it hasn't 170 00:09:04,160 --> 00:09:04,900 so far. 171 00:09:04,940 --> 00:09:08,640 Nikolay: Yeah, sitting in psql, talking to Postgres, you're basically 172 00:09:08,680 --> 00:09:09,840 closer to Postgres, right? 173 00:09:09,840 --> 00:09:10,580 There is no... 174 00:09:11,780 --> 00:09:16,600 JDBC adds something and if it's something, a third-party tool, 175 00:09:16,600 --> 00:09:19,520 it also adds something and this something, like these layers, 176 00:09:19,540 --> 00:09:23,260 can change what you see. 177 00:09:23,560 --> 00:09:28,760 And this can be a bug or intentional, but this I always like, 178 00:09:28,860 --> 00:09:32,800 it's like with monitoring, if I see some graph, like I don't 179 00:09:32,800 --> 00:09:33,300 understand. 180 00:09:34,160 --> 00:09:37,520 Unless I see the code, how the information was gathered and processed, 181 00:09:37,800 --> 00:09:40,220 I don't understand what is presented. 182 00:09:41,400 --> 00:09:44,940 Trust is not high in this area. 183 00:09:45,100 --> 00:09:46,220 There's always doubt. 184 00:09:46,400 --> 00:09:47,540 Am I seeing this? 185 00:09:47,540 --> 00:09:51,240 This is spike really like what I'm thinking it is, right? 186 00:09:51,420 --> 00:09:55,400 So this is about rust and so on, and also features, like features 187 00:09:55,400 --> 00:09:56,100 are great. 188 00:09:56,540 --> 00:09:59,940 And I'm sure, I don't know all of them. 189 00:10:00,060 --> 00:10:04,220 I'm constantly learning and finding new stuff which I was not 190 00:10:04,220 --> 00:10:06,000 aware of for many years. 191 00:10:06,420 --> 00:10:10,940 So these are my basic ideas why I use it a lot. 192 00:10:11,260 --> 00:10:13,760 Michael: Yeah, so actually I had a question for you on that front. 193 00:10:14,080 --> 00:10:18,760 When I was looking through the docs page for psql, like the number 194 00:10:18,760 --> 00:10:24,160 of options and flags and it's just so many when you're working 195 00:10:24,160 --> 00:10:27,280 with it how do you learn about new features do you think I wonder 196 00:10:27,280 --> 00:10:32,320 if it can do X and then you look up can it do X or is it more 197 00:10:32,320 --> 00:10:35,240 a case of every now and again you'll flick through and think 198 00:10:35,660 --> 00:10:37,760 how yeah how do you learn more about it 199 00:10:37,900 --> 00:10:41,360 Nikolay: yeah it's a good question I think it's a mix so sometimes 200 00:10:42,040 --> 00:10:46,760 I just know what is possible and I don't remember I my favorite 201 00:10:46,760 --> 00:10:51,900 comment is \? and \H for help with 202 00:10:51,900 --> 00:10:55,600 SQL and there's a lot of documentation, it's right there. 203 00:10:55,600 --> 00:11:00,200 So help and the grammar of SQL itself, it's there. 204 00:11:00,560 --> 00:11:05,740 But sometimes I see something which I'm thinking, is it possible? 205 00:11:05,740 --> 00:11:09,640 For example, if we want to process a lot of rows and batches, 206 00:11:09,900 --> 00:11:15,380 of course, it's good to write some full-fledged program using 207 00:11:15,380 --> 00:11:20,700 Python or Go or anything, with monitoring, logging, but if you 208 00:11:20,700 --> 00:11:26,280 need it quickly, my usual approach, like ad hoc processing, it's 209 00:11:26,280 --> 00:11:27,600 needed really soon. 210 00:11:28,260 --> 00:11:29,540 We need the result right now. 211 00:11:29,540 --> 00:11:33,780 I just write a psql code using \watch. 212 00:11:34,160 --> 00:11:34,660 Right? 213 00:11:34,960 --> 00:11:38,040 And I was always like, okay, \watch is good. 214 00:11:38,040 --> 00:11:39,740 Like it's for looping, right? 215 00:11:39,840 --> 00:11:43,520 You run some query which takes a batch and process it. 216 00:11:43,520 --> 00:11:44,700 For example, deleting, right? 217 00:11:44,700 --> 00:11:46,360 We need to clean up some data. 218 00:11:46,920 --> 00:11:48,580 And we cannot delete in Postgres. 219 00:11:49,020 --> 00:11:54,520 It's a very bad idea to delete many millions of rows in a single 220 00:11:54,960 --> 00:11:55,460 transaction. 221 00:11:56,200 --> 00:11:57,260 Michael: We did an episode. 222 00:11:57,740 --> 00:11:58,320 Nikolay: Yeah, yeah. 223 00:11:58,320 --> 00:12:00,280 So massive delete can hit you. 224 00:12:00,280 --> 00:12:04,440 If you need to delete like millions of rows in a huge table, 225 00:12:04,440 --> 00:12:05,940 it's better to do it in batches. 226 00:12:06,280 --> 00:12:09,120 And it's easy to write some CTE. 227 00:12:09,440 --> 00:12:10,220 Quite easy. 228 00:12:10,420 --> 00:12:13,820 To write some CTE, finding the scope of work right now, like 229 00:12:13,820 --> 00:12:18,660 a thousand rows or something, delete them and report in a nice 230 00:12:18,660 --> 00:12:21,820 way maybe with even with progress bar which wishes like this 231 00:12:21,820 --> 00:12:25,780 what I like and not not to forget about vacuum of course and 232 00:12:25,840 --> 00:12:27,320 dead tuples and processing 233 00:12:28,080 --> 00:12:29,540 Michael: we took yeah 234 00:12:29,540 --> 00:12:30,420 Nikolay: yeah yeah yeah 235 00:12:30,420 --> 00:12:33,060 Michael: but but okay back to so we're talking about \watch back 236 00:12:33,060 --> 00:12:36,660 to us \watch right badly named probably What do you think of the 237 00:12:36,660 --> 00:12:37,160 name? 238 00:12:37,840 --> 00:12:38,340 Nikolay: \watch? 239 00:12:39,060 --> 00:12:39,560 Michael: Yeah. 240 00:12:39,720 --> 00:12:40,580 Well, yeah. 241 00:12:40,600 --> 00:12:44,060 It was created, I think, to, you know, like you have a select 242 00:12:44,060 --> 00:12:45,120 from just activity. 243 00:12:46,560 --> 00:12:50,420 You, for example, aggregate queries by state, understand how 244 00:12:50,420 --> 00:12:54,380 many active backends we have, maybe wait events as well, and 245 00:12:54,380 --> 00:12:55,660 you just observe them. 246 00:12:56,540 --> 00:12:58,280 And that's why \watch, I think. 247 00:12:58,660 --> 00:12:59,340 Michael: I get it. 248 00:12:59,340 --> 00:13:03,160 But repeat or something that gives it a little bit more of like 249 00:13:03,160 --> 00:13:05,200 an idea of what it's actually going to do. 250 00:13:05,200 --> 00:13:08,580 Nikolay: Your mind definitely is targeted to find gotchas. 251 00:13:09,000 --> 00:13:10,240 I'm with you here. 252 00:13:10,240 --> 00:13:12,040 It's not perfect naming at all. 253 00:13:12,040 --> 00:13:12,540 Yeah. 254 00:13:12,620 --> 00:13:13,120 100%. 255 00:13:13,260 --> 00:13:14,440 Michael: So yeah, super cool feature. 256 00:13:14,440 --> 00:13:17,780 I've even seen it used, the time I saw it used most recently 257 00:13:17,960 --> 00:13:22,280 was I was watching a Patroni demo for when I was preparing for 258 00:13:22,280 --> 00:13:23,800 a few episodes ago. 259 00:13:24,060 --> 00:13:29,580 Alexander Kukushkin used \watch in 1 session to keep querying 260 00:13:29,580 --> 00:13:35,080 a database like while trying to fail over to show latencies and 261 00:13:35,080 --> 00:13:38,440 to show what happened like did any queries actually fail did 262 00:13:38,440 --> 00:13:41,680 it like what was the what was the latency of the failover that 263 00:13:41,680 --> 00:13:46,160 kind of thing it was a really cool use case to continually query 264 00:13:46,340 --> 00:13:47,480 a database 265 00:13:48,140 --> 00:13:49,580 Nikolay: right right exactly. 266 00:13:50,020 --> 00:13:56,120 \watch is useful, but it's until, I think it was not until Postgres 267 00:13:56,120 --> 00:14:03,940 16 when we had, with Andrei and Kirk, we had Postgres hacking 268 00:14:03,940 --> 00:14:09,220 sessions, and we extended it to support the number of loops you 269 00:14:09,220 --> 00:14:09,720 want. 270 00:14:10,260 --> 00:14:13,580 Because the only option is the sleep time between your queries. 271 00:14:13,860 --> 00:14:17,940 In the documentation of another gotcha, I think it was described 272 00:14:18,180 --> 00:14:21,180 as interval time. 273 00:14:21,760 --> 00:14:25,700 But if you take into consideration the duration of query itself, 274 00:14:26,200 --> 00:14:27,100 it's not counted. 275 00:14:27,100 --> 00:14:30,920 So it's only sleep time after 1 comment finished before running 276 00:14:30,920 --> 00:14:31,980 the next 1. 277 00:14:33,340 --> 00:14:38,760 And the idea was, before Postgres 16, the only option was to 278 00:14:38,760 --> 00:14:39,760 specify time. 279 00:14:39,760 --> 00:14:41,460 By default, I think it's 2 seconds. 280 00:14:41,460 --> 00:14:42,840 I don't know why 2 seconds. 281 00:14:43,440 --> 00:14:44,340 Specify some time. 282 00:14:44,340 --> 00:14:50,700 You can specify 0.1, 100 milliseconds slip time, like very quickly 283 00:14:50,920 --> 00:14:55,080 running queries in a loop, and it's infinite, unless it fails. 284 00:14:55,080 --> 00:14:56,340 If it fails, it stops. 285 00:14:56,460 --> 00:14:58,080 By the way, it could be an option again. 286 00:14:58,080 --> 00:15:01,820 I'm just thinking right now, maybe this should be an option saying, 287 00:15:02,160 --> 00:15:03,860 if it fails, still continue. 288 00:15:04,540 --> 00:15:04,960 Right? 289 00:15:04,960 --> 00:15:05,460 Because... 290 00:15:05,740 --> 00:15:06,240 Michael: Interesting. 291 00:15:06,500 --> 00:15:08,520 Nikolay: Yeah, default behavior is just to stop. 292 00:15:08,520 --> 00:15:10,740 In some cases, I would prefer continue. 293 00:15:11,180 --> 00:15:15,620 And like, right now I have such situations when I need even if 294 00:15:15,620 --> 00:15:17,640 it fails, still continue with the loop. 295 00:15:17,640 --> 00:15:18,340 In this case, I... 296 00:15:18,340 --> 00:15:19,740 Michael: Oh, like the failover testing. 297 00:15:20,580 --> 00:15:23,420 If you're doing failover testing, even if 1 of them fails... 298 00:15:23,420 --> 00:15:24,140 Like in Kukushkin's case. 299 00:15:24,140 --> 00:15:24,840 Yes, exactly. 300 00:15:24,960 --> 00:15:26,020 Nikolay: Exactly, exactly. 301 00:15:26,120 --> 00:15:28,240 But \watch behaves like it just stops. 302 00:15:28,780 --> 00:15:34,820 And if I need this behavior, I just need to go to like shell 303 00:15:34,820 --> 00:15:39,440 level, bash or any like ZSH and there I need to bash usually 304 00:15:39,440 --> 00:15:42,340 because I don't run such things from my laptop. 305 00:15:42,340 --> 00:15:47,220 I'm usually running everything in tmux right on the server or 306 00:15:47,260 --> 00:15:49,200 very close to the server in cloud. 307 00:15:49,340 --> 00:15:54,720 So if like California internet is very bad, you know it. 308 00:15:54,720 --> 00:15:57,240 And even Starlink sometimes is down. 309 00:15:57,560 --> 00:16:00,900 So in this case, I'm not losing my session. 310 00:16:01,100 --> 00:16:02,220 tmux is great. 311 00:16:02,780 --> 00:16:06,900 And if query fails, if you need to continue, I'm forced to go 312 00:16:06,900 --> 00:16:10,100 to shell while sleep 1, for example. 313 00:16:11,500 --> 00:16:15,700 Not while true, because if you do while true, ctrl-c won't work 314 00:16:15,700 --> 00:16:17,060 properly in many cases. 315 00:16:17,060 --> 00:16:19,360 It's very annoying if you write while... 316 00:16:19,360 --> 00:16:21,220 If you sleep first, then do something. 317 00:16:21,220 --> 00:16:25,740 So while sleep 1, well, how many seconds you want to have between 318 00:16:26,180 --> 00:16:27,080 running psql. 319 00:16:27,380 --> 00:16:30,920 In this case, Control-C will work when you need to interrupt 320 00:16:30,920 --> 00:16:31,160 it. 321 00:16:31,160 --> 00:16:35,740 And in this case also you can have some additional stuff saying 322 00:16:35,740 --> 00:16:40,380 if comment is failed, for example, vertical bars, a couple of 323 00:16:40,380 --> 00:16:44,480 vertical bars, or report failed and then continue the loop, right? 324 00:16:44,820 --> 00:16:47,760 Michael: I was reading through what the, well, you're mentioning 325 00:16:47,760 --> 00:16:50,900 some improvements that have happened in psql recently, which is 326 00:16:50,900 --> 00:16:54,480 cool, but I think it's not necessarily obvious that it's continuing 327 00:16:54,620 --> 00:16:55,320 to improve. 328 00:16:55,580 --> 00:16:59,480 And I did notice, I didn't read into the details, but somebody 329 00:16:59,540 --> 00:17:04,640 improved it recently to make Control-C work better. 330 00:17:04,640 --> 00:17:09,800 So I think maybe in 1 of the more recent well I'm not sure it 331 00:17:09,800 --> 00:17:10,740 fixes that 332 00:17:10,760 --> 00:17:14,060 Nikolay: I'm not sure it's related it's well it's interesting 333 00:17:14,060 --> 00:17:17,760 maybe maybe I need to check it's it's all it was in Postgres 17 release 334 00:17:17,760 --> 00:17:21,000 notes right yeah I also remember something, but I didn't connect 335 00:17:21,000 --> 00:17:21,840 dots here. 336 00:17:21,960 --> 00:17:25,580 So let's unwrap my story, because we have already... 337 00:17:26,040 --> 00:17:27,180 It's amazing, right? 338 00:17:28,180 --> 00:17:33,500 So the idea was we want to be able to specify how many loops 339 00:17:33,540 --> 00:17:34,260 we need. 340 00:17:34,600 --> 00:17:39,320 And to do that we extended basically this tiny grammar, right, 341 00:17:39,320 --> 00:17:46,480 or tiny format, and allow to specify basically any options, named 342 00:17:46,580 --> 00:17:47,060 options. 343 00:17:47,060 --> 00:17:48,560 So we added like the... 344 00:17:48,760 --> 00:17:53,800 We converted this not interval, how to properly say, the break 345 00:17:54,280 --> 00:17:58,740 between sleep time between 2 commands executed. 346 00:17:58,860 --> 00:17:59,940 Michael: Sleep time is good. 347 00:18:00,140 --> 00:18:01,900 Nikolay: Yeah, I think we can call it sleep. 348 00:18:03,340 --> 00:18:06,740 By the way, since I don't see Postgres 16, it was released 349 00:18:06,740 --> 00:18:09,740 in 2016, I don't see 16 too often in production. 350 00:18:09,920 --> 00:18:13,580 I still have not got used to this, what we developed, right? 351 00:18:13,660 --> 00:18:14,320 It's interesting. 352 00:18:14,340 --> 00:18:18,440 So I still, mostly all the time, I use psql. 353 00:18:18,460 --> 00:18:20,420 And I must correct myself, actually. 354 00:18:20,600 --> 00:18:25,240 In many cases, we have psql already 16, even if we work with 355 00:18:25,240 --> 00:18:26,000 old server. 356 00:18:26,000 --> 00:18:27,840 So it's just my mind problem. 357 00:18:27,840 --> 00:18:29,580 I need to adjust my habits. 358 00:18:30,040 --> 00:18:32,540 I need to adjust my habits and start using what we developed. 359 00:18:32,720 --> 00:18:36,260 But the second option was, and this is the whole purpose of that 360 00:18:36,260 --> 00:18:40,460 work, is to allow us to specify the number of loops you need. 361 00:18:40,460 --> 00:18:44,160 If you know you don't need more than something, that's it. 362 00:18:44,680 --> 00:18:48,940 But the interesting fact is that the inspiration of that, yeah, 363 00:18:48,940 --> 00:18:52,640 I'm checking, it's interval I, number of seconds, it's wrong 364 00:18:52,640 --> 00:18:55,740 naming, but it is interval. 365 00:18:56,760 --> 00:19:00,200 With understanding it's not actually interval, it's interval 366 00:19:00,260 --> 00:19:03,620 not taking into account the duration of the comment executed, 367 00:19:03,620 --> 00:19:07,580 which can take actually minutes or hours in extreme cases. 368 00:19:07,780 --> 00:19:10,580 And yes, c or count equals something. 369 00:19:10,580 --> 00:19:13,680 This is the number of loops you need, right? 370 00:19:15,060 --> 00:19:16,100 And that's great. 371 00:19:16,400 --> 00:19:20,780 But actually, the original idea why we thought about this was... 372 00:19:21,820 --> 00:19:27,180 Many times I had work, I process a lot of rows and batches, and 373 00:19:27,180 --> 00:19:28,180 I need to stop. 374 00:19:28,180 --> 00:19:31,160 And to stop I usually used division by 0. 375 00:19:31,420 --> 00:19:34,220 This is a very old trick, it works. 376 00:19:34,360 --> 00:19:37,480 I know the \watch will stop if there is an error. 377 00:19:37,740 --> 00:19:38,560 So I just... 378 00:19:38,660 --> 00:19:42,240 When there is nothing to process anymore I just divide by number 379 00:19:42,240 --> 00:19:43,380 of rows to process. 380 00:19:43,380 --> 00:19:46,380 It's 0 so it stops because of division by 0. 381 00:19:47,200 --> 00:19:51,220 And interesting, then in Postgres 17, it was implemented, right? 382 00:19:51,220 --> 00:19:54,600 Already not by us, by Greg Sabino Mullane, I think. 383 00:19:55,240 --> 00:20:00,560 Yeah, allow psql \watch to stop after minimum number of rows return. 384 00:20:00,940 --> 00:20:03,140 Funny thing, this is exactly what I needed. 385 00:20:03,540 --> 00:20:06,840 But we implemented some different parts around this problem. 386 00:20:06,900 --> 00:20:11,380 So you now can say m or min rows, mir underscore rows equals 387 00:20:11,380 --> 00:20:12,440 some number of rows. 388 00:20:12,800 --> 00:20:14,600 And you can say 0. 389 00:20:15,160 --> 00:20:17,820 If we have 0, that's it. 390 00:20:18,260 --> 00:20:19,340 This is great, right? 391 00:20:20,060 --> 00:20:21,540 Michael: Yeah, kind of a weird name. 392 00:20:21,600 --> 00:20:23,340 Is it 0 or is it 1? 393 00:20:23,480 --> 00:20:24,880 Nikolay: A number of rows returned. 394 00:20:25,240 --> 00:20:31,700 If you return, for example, if you delete returning star and 395 00:20:31,700 --> 00:20:35,480 it's returned 0, then you need to stop. 396 00:20:36,000 --> 00:20:37,860 Oh, you think it should be... 397 00:20:37,860 --> 00:20:42,100 Yeah, well, let's double check but let's not to let's not to 398 00:20:42,100 --> 00:20:45,840 be like super we are not providing some like lesson or reference 399 00:20:45,920 --> 00:20:47,580 we are talking what's possible. 400 00:20:48,260 --> 00:20:51,560 There is documentation and also you can try and learn. 401 00:20:51,820 --> 00:20:54,860 So now it's possible, you don't need division by 0 anymore. 402 00:20:55,080 --> 00:21:00,200 The only issue with this I have is that I did like, and I still 403 00:21:00,200 --> 00:21:03,340 do like, my approach reporting, like, you know, progress bar 404 00:21:03,340 --> 00:21:06,920 and so on, many stuff, many pieces of information. 405 00:21:07,540 --> 00:21:09,840 When you process a batch, you report a lot of stuff. 406 00:21:09,840 --> 00:21:11,520 In this case, I cannot use this. 407 00:21:11,520 --> 00:21:12,540 I cannot say... 408 00:21:13,040 --> 00:21:17,180 I always have some rows reported in the result. 409 00:21:18,100 --> 00:21:23,700 Usually just 1 row, many columns, like number of rows we processed, 410 00:21:23,800 --> 00:21:26,620 what's left, like progress, percentage, anything. 411 00:21:27,060 --> 00:21:33,080 So I think will I still use division by 0 after I got used to 412 00:21:33,080 --> 00:21:33,980 Postgres 17. 413 00:21:34,020 --> 00:21:35,760 I'm not sure, right? 414 00:21:36,820 --> 00:21:37,780 Michael: Should we move on? 415 00:21:37,900 --> 00:21:39,000 Nikolay: Yeah, let's move on. 416 00:21:39,000 --> 00:21:42,540 And I wanted to emphasize there are a couple of areas. 417 00:21:42,720 --> 00:21:44,000 There are 2 big areas. 418 00:21:44,440 --> 00:21:46,740 First big area where we can use Psycal. 419 00:21:46,740 --> 00:21:48,640 First big area is interactive mode. 420 00:21:49,860 --> 00:21:54,520 And sometimes we use \watch there for just observing something. 421 00:21:54,520 --> 00:21:56,360 This is where name plays well. 422 00:21:56,920 --> 00:22:00,680 Or we use like various advanced stuff. 423 00:22:00,680 --> 00:22:04,720 I got used to \gx instead of semicolon. 424 00:22:05,640 --> 00:22:10,160 By the way, both \watch and \gx don't require semicolon. 425 00:22:10,520 --> 00:22:13,440 I know even Andrey learned it from me, it's not obvious. 426 00:22:14,100 --> 00:22:20,800 They can be your end of command semicolon, replacement for it. 427 00:22:20,800 --> 00:22:24,440 Michael: So \gx, I looked this up, it was send the last query, 428 00:22:24,920 --> 00:22:26,620 send that to the server, right? 429 00:22:26,880 --> 00:22:30,560 Nikolay: Right, but it's expanded. 430 00:22:30,780 --> 00:22:35,280 So the idea is it's like semicolon, but it's expanded. 431 00:22:35,320 --> 00:22:39,760 So it basically, if you have very wide result set, many, many 432 00:22:39,760 --> 00:22:45,480 columns, but low number of rows, for example, just 1, \gx is much 433 00:22:45,480 --> 00:22:49,840 better because it's equivalent to switching to expanded mode 434 00:22:49,840 --> 00:22:51,240 using \x. 435 00:22:51,960 --> 00:22:56,120 So you see 1 column on 1 line, second column on a different line, 436 00:22:56,120 --> 00:22:56,680 and so on. 437 00:22:56,680 --> 00:22:58,940 You see it transponded, right? 438 00:22:59,440 --> 00:23:04,140 And yeah, I just have a habit to use it when I just select something, 439 00:23:04,460 --> 00:23:08,560 for example, select star from users where id or email equals 440 00:23:08,560 --> 00:23:09,060 this. 441 00:23:09,780 --> 00:23:11,520 And I want to see all columns. 442 00:23:11,540 --> 00:23:15,360 I just use \gx, instead of semicolon. 443 00:23:15,720 --> 00:23:20,380 And I don't need to deal with this formatting or horizontal scrolling 444 00:23:20,380 --> 00:23:22,400 if you have pspg installed. 445 00:23:22,760 --> 00:23:28,840 pspg I didn't mention, I do like pspg, which provides much better 446 00:23:29,640 --> 00:23:32,000 output and pagination in psql. 447 00:23:32,220 --> 00:23:34,200 Very great addition to psql. 448 00:23:34,860 --> 00:23:35,800 Michael: Yeah, it's pretty cool. 449 00:23:35,800 --> 00:23:40,520 You can even do like horizontal scrolling within the terminal. 450 00:23:40,520 --> 00:23:41,880 It's pretty cool. 451 00:23:41,880 --> 00:23:46,680 And did you know pspg is even mentioned in the psql docs? 452 00:23:47,660 --> 00:23:48,660 That's pretty cool. 453 00:23:48,820 --> 00:23:50,440 I didn't realize that till today. 454 00:23:50,580 --> 00:23:52,760 Nikolay: Unfortunately, no, because it's third... 455 00:23:53,860 --> 00:23:58,360 I think Postgres docs don't mention third-party tools almost. 456 00:23:58,840 --> 00:24:00,920 Maybe except pgAdmin. 457 00:24:01,220 --> 00:24:04,660 I don't know if pgAdmin is mentioned in Postgres docs. 458 00:24:05,240 --> 00:24:07,700 Michael: I have not seen it there. 459 00:24:07,700 --> 00:24:11,040 But it's very rare to see a third-party tool mentioned in the 460 00:24:11,040 --> 00:24:11,540 documentation. 461 00:24:13,180 --> 00:24:13,880 Nikolay: Maybe never. 462 00:24:14,640 --> 00:24:17,440 Michael: Well, this is third-party though, pspg. 463 00:24:18,200 --> 00:24:19,320 Nikolay: Yeah, it's third-party. 464 00:24:20,740 --> 00:24:21,780 Michael: So not never 465 00:24:23,200 --> 00:24:27,040 It is? 466 00:24:27,800 --> 00:24:28,780 Michael: Yeah in the docs 467 00:24:28,780 --> 00:24:30,120 Nikolay: It's a surprise for me 468 00:24:30,620 --> 00:24:31,160 Michael: Yeah, same. 469 00:24:31,160 --> 00:24:37,460 That's why I mean, that's why I said in psql it's mentioned under 470 00:24:37,460 --> 00:24:39,120 paging like it makes sense. 471 00:24:39,120 --> 00:24:39,680 Nikolay: That's great 472 00:24:40,360 --> 00:24:40,860 Nikolay: That's great. 473 00:24:40,860 --> 00:24:42,260 Yeah, it's it's good. 474 00:24:42,260 --> 00:24:44,240 And yeah, I like it a lot. 475 00:24:44,320 --> 00:24:46,220 I use it all the time when I can. 476 00:24:48,820 --> 00:24:52,780 So yeah, also colors, it provides good colors, menu, interactive 477 00:24:52,800 --> 00:24:54,740 menu, so many cool stuff. 478 00:24:55,680 --> 00:25:00,920 Yeah, so back to \watch and \gx. \watch and 479 00:25:00,920 --> 00:25:05,140 \gx, semicolon is not needed, and even more, like if 480 00:25:05,140 --> 00:25:10,200 you use semicolon with \watch, you will get basically extra call, 481 00:25:10,200 --> 00:25:12,100 which may be not good. 482 00:25:13,200 --> 00:25:17,840 When you start counting already With new options, new option 483 00:25:17,840 --> 00:25:20,640 count, it's an extra call. 484 00:25:21,740 --> 00:25:22,240 Right. 485 00:25:22,240 --> 00:25:23,320 Okay, what else? 486 00:25:23,320 --> 00:25:29,860 Let's talk about interactive mode and what tricks are worth knowing, 487 00:25:31,320 --> 00:25:33,300 In addition to \gx. 488 00:25:34,300 --> 00:25:34,540 So 489 00:25:34,540 --> 00:25:37,540 Michael: when you say interactive mode, do you mean like a user 490 00:25:39,400 --> 00:25:43,160 doing administrative tasks or doing some ad hoc queries? 491 00:25:43,260 --> 00:25:44,240 Yeah, okay, great. 492 00:25:44,240 --> 00:25:44,940 Nikolay: Yeah, yeah. 493 00:25:45,600 --> 00:25:50,420 What are your favorite things when you rarely touch psql? 494 00:25:51,880 --> 00:25:52,800 Michael: I think they're not... 495 00:25:52,800 --> 00:25:56,440 Well, mine aren't going to be on the advanced side of things, 496 00:25:56,540 --> 00:26:00,660 but yeah, some of the alignment things are quite useful, so like 497 00:26:00,660 --> 00:26:05,360 \a can toggle off alignment if for any reason you want 498 00:26:05,360 --> 00:26:05,600 it. 499 00:26:05,600 --> 00:26:08,220 So I complained about things at the beginning. 500 00:26:08,220 --> 00:26:10,960 Nikolay: What's the reason to use backslash, to turn off alignment? 501 00:26:12,400 --> 00:26:15,180 Michael: If you want, like if you're doing some stuff with JSON, 502 00:26:15,180 --> 00:26:20,280 for example, the JSON output, you don't want like a pretty nice... 503 00:26:20,280 --> 00:26:24,120 So it's a nice feature of psql that you get tables formatted 504 00:26:24,240 --> 00:26:28,260 as tables with characters but you don't want that if you're dealing 505 00:26:28,260 --> 00:26:28,940 with JSON. 506 00:26:28,940 --> 00:26:33,820 Nikolay: I always use it when I want to see the body of function 507 00:26:33,820 --> 00:26:37,280 or trigger function or stored procedure. 508 00:26:38,240 --> 00:26:43,880 So if you use \df or \sf, it was changed, right? 509 00:26:44,060 --> 00:26:44,940 \df plus. 510 00:26:46,380 --> 00:26:49,740 I always used \df plus, but I think it got... 511 00:26:50,340 --> 00:26:53,560 Like this behavior was duplicated or removed and you need to 512 00:26:53,560 --> 00:26:56,980 use \sf or something, so you need to see function using 513 00:26:57,160 --> 00:26:57,660 s. 514 00:26:58,520 --> 00:27:04,120 And in this case, If you don't run \a before that, the 515 00:27:04,120 --> 00:27:08,260 formatting will skew all the indentation of the code, and it's 516 00:27:08,260 --> 00:27:09,180 hard to understand. 517 00:27:10,120 --> 00:27:15,120 But this is also my habit, \a, then \sf plus, 518 00:27:15,240 --> 00:27:18,360 and see the function, understand what it's doing, and so on. 519 00:27:18,840 --> 00:27:22,520 Then you can even, if it's not production, you can edit the function 520 00:27:22,540 --> 00:27:24,060 using \ef. 521 00:27:25,260 --> 00:27:28,660 And you know, like this probably also gotcha. 522 00:27:30,420 --> 00:27:33,760 We have my mind is also, this echo from last episode. 523 00:27:33,840 --> 00:27:37,700 And those who didn't listen to it Like it was interesting episode 524 00:27:37,700 --> 00:27:43,740 to me as well So when you use \ef and I usually use 525 00:27:43,740 --> 00:27:44,340 VI right? 526 00:27:44,340 --> 00:27:50,580 Yeah, I'm a big VI fan You go to VI, you edit everything, then 527 00:27:50,580 --> 00:27:58,440 you, as usual, like call on WQ, like save and exit, write and 528 00:27:58,440 --> 00:27:58,940 quit. 529 00:28:00,060 --> 00:28:03,300 And then, you know this or no? 530 00:28:04,140 --> 00:28:07,800 Michael: I'm not a VI user, but I've read like, \e is 531 00:28:07,800 --> 00:28:09,960 1 of those tips that often comes up with Chrome. 532 00:28:09,960 --> 00:28:11,370 Like you can just use it to edit anything. 533 00:28:11,370 --> 00:28:11,920 It doesn't 534 00:28:11,920 --> 00:28:12,440 Nikolay: have to be a function. 535 00:28:12,440 --> 00:28:15,100 Right, but for specifically for functions, there is a gotcha. 536 00:28:15,140 --> 00:28:16,360 It's not about VI. 537 00:28:16,360 --> 00:28:20,980 You can, I think, use Nano or anything and still bump into this 538 00:28:20,980 --> 00:28:21,480 problem? 539 00:28:21,740 --> 00:28:27,760 The problem is, you think it's written and saved, but Postgres 540 00:28:27,880 --> 00:28:32,440 prompt, not psql prompt, shows something like a transaction is 541 00:28:32,440 --> 00:28:33,340 not closed, right? 542 00:28:33,340 --> 00:28:36,160 You need the semicolon to finalize it. 543 00:28:36,220 --> 00:28:37,620 And this is not expected. 544 00:28:37,660 --> 00:28:40,600 And I'm still like many years, like I deal with it. 545 00:28:40,600 --> 00:28:43,980 I know this every time I deal with it, I'm thinking, why is it 546 00:28:43,980 --> 00:28:44,480 so? 547 00:28:45,400 --> 00:28:48,740 I know like there should be some explanation why it's so, but 548 00:28:48,740 --> 00:28:49,080 yeah. 549 00:28:49,080 --> 00:28:52,060 So after \ef, always semicolon. 550 00:28:53,400 --> 00:28:55,020 Michael: Or \gx. 551 00:28:56,720 --> 00:28:57,660 Nikolay: Well, interesting. 552 00:28:57,780 --> 00:28:59,360 Michael: I don't know if that would work. 553 00:29:00,020 --> 00:29:00,520 Nikolay: Maybe. 554 00:29:00,600 --> 00:29:01,560 Okay, maybe. 555 00:29:01,840 --> 00:29:02,420 What else? 556 00:29:02,420 --> 00:29:05,780 I like a \set and I use it from time to time. 557 00:29:06,300 --> 00:29:07,740 There are 2 cases. 558 00:29:07,900 --> 00:29:09,980 I use it when I need some variable. 559 00:29:10,120 --> 00:29:14,240 Of course, this moves us already to programming mode, not interactive 560 00:29:14,240 --> 00:29:14,700 mode. 561 00:29:14,700 --> 00:29:15,860 Michael: There's overlap, right? 562 00:29:15,860 --> 00:29:17,540 Nikolay: Right, There's overlap for sure. 563 00:29:18,080 --> 00:29:24,840 I like to use it sometimes to set something to have short comment, 564 00:29:24,840 --> 00:29:25,520 for example. 565 00:29:25,600 --> 00:29:29,880 For example, you know, we talked about buffers in explain analyze 566 00:29:30,480 --> 00:29:36,040 a lot and you can just define some like colon, colon, colon, 567 00:29:36,040 --> 00:29:41,480 colon, EAB, for example, or just colon something, I don't know. 568 00:29:41,480 --> 00:29:43,340 And this 1 might, yeah. 569 00:29:44,100 --> 00:29:47,120 Michael: In my docs for, so for getting a query plan, because 570 00:29:47,120 --> 00:29:51,780 we recommend explain in parentheses analyze format json buffers 571 00:29:51,780 --> 00:29:56,740 verbose settings while in our example we just do colon ea as 572 00:29:56,740 --> 00:30:02,380 the alias for that and I but I don't personally use it I personally 573 00:30:02,720 --> 00:30:05,140 because I'm always working pretty much always working on the 574 00:30:05,140 --> 00:30:08,480 same machine, I just have a text expansion tool on Mac OS that 575 00:30:08,480 --> 00:30:11,540 does something similar for when I'm in interactive mode. 576 00:30:11,540 --> 00:30:14,080 I can understand how this becomes a little bit more. 577 00:30:14,600 --> 00:30:18,540 Maybe you might use this more when you're in programming mode, 578 00:30:18,940 --> 00:30:22,580 but people, yeah, I can see why some people would use it for 579 00:30:22,580 --> 00:30:23,620 interactive mode. 580 00:30:23,920 --> 00:30:24,940 Nikolay: Yeah, it's interesting. 581 00:30:25,680 --> 00:30:29,120 There's definitely overlapping between interactive and programming 582 00:30:29,120 --> 00:30:30,520 mode, at least for me. 583 00:30:30,780 --> 00:30:34,540 Because when I think about set, I always already think about 584 00:30:34,540 --> 00:30:35,720 \gset, right? 585 00:30:35,900 --> 00:30:42,240 When the result of the output of command is used to set variables, 586 00:30:42,720 --> 00:30:46,780 client-side, psql variables, So you can use them later. 587 00:30:47,240 --> 00:30:52,320 And I do use this sometimes, for example, even in loop, even 588 00:30:53,040 --> 00:30:53,820 with \watch. 589 00:30:54,380 --> 00:30:57,340 It's interesting because, for example, you can monitor progress, 590 00:30:57,340 --> 00:31:03,840 you can, for example, remember previous data, including timestamp 591 00:31:04,440 --> 00:31:05,940 and calculate interval. 592 00:31:06,220 --> 00:31:10,460 It's not always convenient and sometimes I switch to using server-side 593 00:31:11,500 --> 00:31:15,460 variables, which like this set SQL command, right? 594 00:31:15,640 --> 00:31:22,080 So \set is psql commands, set is client-side and set 595 00:31:22,080 --> 00:31:24,880 without backslash is just psql command, it's server-side. 596 00:31:26,000 --> 00:31:28,160 It's very similar to copy, by the way. 597 00:31:28,280 --> 00:31:33,740 Copy when you, for example, export or import data, 598 00:31:33,980 --> 00:31:35,520 \copy is purely client-side. 599 00:31:35,740 --> 00:31:41,540 It defines where your CSV will be saved on your client machine, 600 00:31:42,180 --> 00:31:44,840 if it's a different machine, or on server. 601 00:31:45,040 --> 00:31:47,220 And permissions needed, of course, and so on. 602 00:31:47,280 --> 00:31:54,720 So back to \gset, you can do such stuff, like you remember what 603 00:31:54,720 --> 00:31:58,220 happened, then you can use it in the next query. 604 00:31:59,060 --> 00:32:01,980 And This already moves us to programming mode. 605 00:32:02,860 --> 00:32:06,520 Sometimes I remember I needed to combine client-side variables 606 00:32:06,540 --> 00:32:07,760 and server-side variables. 607 00:32:08,100 --> 00:32:13,940 When I do server-side variables, usually I don't use set because 608 00:32:15,060 --> 00:32:18,120 It cannot be embedded to other queries. 609 00:32:18,740 --> 00:32:22,000 It's a separate utility command, basically. 610 00:32:23,240 --> 00:32:27,140 You cannot put it inside your SELECT or DELETE or something, 611 00:32:27,380 --> 00:32:28,120 or CTE. 612 00:32:28,840 --> 00:32:32,420 While there are 2 functions, quite weird functions, actually, 613 00:32:32,420 --> 00:32:36,480 because they have a second parameter and you always need to check 614 00:32:36,480 --> 00:32:36,980 documentation. 615 00:32:37,260 --> 00:32:40,740 current_setting() and set_config(), I think. 616 00:32:41,400 --> 00:32:46,720 1 is setting server side, set_config() is setting server side variable, 617 00:32:46,820 --> 00:32:49,960 and the second parameter, I believe, defines if it's local to 618 00:32:49,960 --> 00:32:53,600 transaction or to whole session. 619 00:32:54,380 --> 00:32:56,960 Basically global, not global for all sessions but for two-year 620 00:32:56,960 --> 00:32:57,460 session. 621 00:32:58,140 --> 00:33:02,440 And current_setting() also has second parameter, it basically reads 622 00:33:02,440 --> 00:33:03,260 it, right? 623 00:33:03,740 --> 00:33:07,440 Second parameter also required, I don't remember meaning, I always 624 00:33:07,440 --> 00:33:13,240 put true, yeah, like it's, because naming is weird and that's 625 00:33:13,320 --> 00:33:16,700 why it's impossible to memorize it even if you have like me, 626 00:33:16,700 --> 00:33:19,320 like 18 years of Postgres experience, right? 627 00:33:19,640 --> 00:33:21,540 This is where I usually check documentation. 628 00:33:21,940 --> 00:33:27,320 And I wrote a lot of code, like many thousands, maybe even thousands 629 00:33:27,320 --> 00:33:29,400 of thousands of psql scripts. 630 00:33:30,060 --> 00:33:32,940 So it's so weird I cannot memorize it. 631 00:33:33,240 --> 00:33:33,740 Yeah. 632 00:33:34,160 --> 00:33:39,120 But second parameter for both these functions is what like worth 633 00:33:39,360 --> 00:33:40,600 paying attention to. 634 00:33:40,600 --> 00:33:45,560 And also like set_config(), current_setting(), they don't look like 635 00:33:45,600 --> 00:33:48,180 basically doing the same thing, but in different directions, 636 00:33:48,340 --> 00:33:49,640 setting and getting, right? 637 00:33:49,640 --> 00:33:51,720 It should be something gets something said, right? 638 00:33:52,540 --> 00:33:53,152 Michael: I don't know. 639 00:33:53,152 --> 00:33:55,740 I'll include links to them in the show notes. 640 00:33:56,140 --> 00:33:56,420 Like I 641 00:33:56,420 --> 00:33:59,860 Nikolay: would prefer to have like GUC set and GUC get or something 642 00:33:59,860 --> 00:34:01,600 like this, right? 643 00:34:01,860 --> 00:34:06,380 And the good thing about server-side variables, you can define 644 00:34:06,380 --> 00:34:13,740 your own, and usually they always go with namespace, with prefix, 645 00:34:13,740 --> 00:34:15,320 so something dot something. 646 00:34:15,560 --> 00:34:20,520 Because without it, it's global, there are only roughly 300 of 647 00:34:20,520 --> 00:34:24,620 them and this is how you control configuration of your server 648 00:34:24,920 --> 00:34:29,700 right so yeah and the combination of these things gives you already 649 00:34:29,700 --> 00:34:34,680 opportunities to start coding and since as both of us know there 650 00:34:34,680 --> 00:34:38,140 is also \if right we are 651 00:34:38,140 --> 00:34:40,900 Michael: well I didn't know I didn't know this until looking 652 00:34:40,900 --> 00:34:42,780 into your code so it was pretty cool 653 00:34:42,980 --> 00:34:47,580 Nikolay: yeah it's kind of already started starting to look like 654 00:34:47,580 --> 00:34:48,920 Turing complete stuff. 655 00:34:49,060 --> 00:34:54,060 So you can start coding in this language, additional language 656 00:34:54,560 --> 00:34:56,020 on top of SQL. 657 00:34:56,820 --> 00:35:01,720 And I like it actually because it gives you a lot of possibilities 658 00:35:01,820 --> 00:35:03,840 to automate things. 659 00:35:04,960 --> 00:35:09,820 If-else gives you ability to, for example, handle different versions 660 00:35:09,820 --> 00:35:10,380 of Postgres. 661 00:35:10,380 --> 00:35:15,660 You can check version quickly, memorize it in a variable, maybe 662 00:35:15,660 --> 00:35:21,040 server-side, and then just using current config function, get 663 00:35:21,040 --> 00:35:25,820 it to client-side variable, and then using if, have different 664 00:35:26,680 --> 00:35:30,260 pieces of your code working with different Postgres versions. 665 00:35:30,480 --> 00:35:34,440 For example, if you want to have something dealing with pg_stat_statements, 666 00:35:34,440 --> 00:35:37,820 statements, we know they change sometimes. 667 00:35:37,820 --> 00:35:41,820 For example, in Postgres 13, more columns were added and existing 668 00:35:41,820 --> 00:35:44,680 columns were changed, so it's not backward compatible. 669 00:35:45,100 --> 00:35:49,700 That's why if you need some report, for example, from pg_stat_statements, 670 00:35:50,080 --> 00:35:52,040 you need several versions of it. 671 00:35:53,200 --> 00:35:59,240 This is where if for these psql scripts is super useful. 672 00:35:59,760 --> 00:36:02,680 Michael: Yeah, Even things like some of the really important 673 00:36:02,720 --> 00:36:08,920 columns like total execution time change name not Not look look 674 00:36:09,280 --> 00:36:09,960 recently enough. 675 00:36:09,960 --> 00:36:13,480 Yeah recently enough that I that I know that change But also 676 00:36:13,480 --> 00:36:16,780 planning time for example wasn't always in there and you probably 677 00:36:16,780 --> 00:36:19,700 want to add both together if you want to look at execution time 678 00:36:19,700 --> 00:36:22,900 plus planning time so when when that's available you probably 679 00:36:22,900 --> 00:36:26,180 should be summing them so yeah I can totally see that making 680 00:36:26,180 --> 00:36:30,360 sense yeah well why we like I know this is super minor compared 681 00:36:30,360 --> 00:36:34,840 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 682 00:36:36,980 --> 00:36:41,880 Nikolay: yeah it's if, elif, else, and or backslash and or and 683 00:36:41,880 --> 00:36:47,220 if backslash and if yeah this is also if you deal with multiple 684 00:36:47,220 --> 00:36:55,160 languages like Python, C, Java, Ruby and also this it's inevitable 685 00:36:55,360 --> 00:36:58,060 that you will be checking documentation when you write. 686 00:36:58,920 --> 00:37:03,420 Michael: So 1 of the things that caught my eye when I was looking 687 00:37:03,420 --> 00:37:08,660 through the postgres_dba was you were just using echo 688 00:37:08,740 --> 00:37:10,540 as almost like a user interface. 689 00:37:10,960 --> 00:37:15,000 And 1 of them, just to show the menu of what's available, like 690 00:37:15,020 --> 00:37:19,300 what queries you can even run, The menus, there was some weird 691 00:37:19,300 --> 00:37:22,120 characters, like some weird, it's almost like, it wasn't Unicode, 692 00:37:22,120 --> 00:37:24,640 but it looked like kind of, they're probably... 693 00:37:24,960 --> 00:37:26,180 Nikolay: Emojis, you mean? 694 00:37:26,800 --> 00:37:29,760 Michael: No, they weren't emojis, it's like, it was color, it 695 00:37:29,760 --> 00:37:33,940 was, I didn't know what it was, so I pasted the echo line into 696 00:37:33,940 --> 00:37:38,460 my psql and it just made the word menu pink, like bright pink. 697 00:37:38,460 --> 00:37:39,380 Yeah, yeah. 698 00:37:39,380 --> 00:37:41,360 Well, that's a nice touch. 699 00:37:41,400 --> 00:37:44,440 Nikolay: Right, but it's regular terminal fun. 700 00:37:44,540 --> 00:37:45,040 Yeah. 701 00:37:45,620 --> 00:37:47,640 It's not psql stuff. 702 00:37:47,640 --> 00:37:48,220 It's just... 703 00:37:48,220 --> 00:37:48,900 Michael: I know, I know. 704 00:37:48,900 --> 00:37:51,980 Nikolay: And it won't work, probably it won't work with some 705 00:37:52,340 --> 00:37:53,800 pagers, right? 706 00:37:54,900 --> 00:37:58,180 Some pagination stuff and in some terminals it won't work as 707 00:37:58,180 --> 00:37:58,680 well. 708 00:37:59,020 --> 00:38:01,480 So it's like unfortunately limited. 709 00:38:01,980 --> 00:38:05,920 But yeah, it's for fun and I usually use it to distinguish like 710 00:38:05,920 --> 00:38:10,020 success from from failure like errors from Successful messages 711 00:38:10,020 --> 00:38:13,820 or warnings like different levels notices warnings and so on 712 00:38:13,820 --> 00:38:17,420 by the way Do you know why everyone is using Raise notice or Raise 713 00:38:17,420 --> 00:38:24,060 warning in PL/pgSQL while there is a Raise info and Raise debug. 714 00:38:24,920 --> 00:38:30,040 This is just lack of checking documentation maybe. 715 00:38:31,420 --> 00:38:32,120 Michael: Yeah probably. 716 00:38:32,980 --> 00:38:36,600 Nikolay: And since we touched the \gset it's worth mentioning 717 00:38:36,600 --> 00:38:39,500 also there is gexec, a very powerful thing. 718 00:38:39,840 --> 00:38:45,180 I usually use it together with a format, so we can basically 719 00:38:45,180 --> 00:38:51,480 have a dynamic SQL build from like you have a select query we 720 00:38:51,480 --> 00:38:57,100 format you maybe by the way in some like while or something well 721 00:38:57,100 --> 00:39:02,540 some loop right you have this and You build some new query and 722 00:39:02,540 --> 00:39:06,100 it return it as a string right and then gexec just executes 723 00:39:06,100 --> 00:39:09,980 it This is also quite powerful in some cases 724 00:39:11,380 --> 00:39:14,440 Michael: Yeah, and I've seen the example in the docs But also 725 00:39:14,440 --> 00:39:18,120 I've seen you do this for real when we were just testing something 726 00:39:18,120 --> 00:39:22,760 together once, you can use it to if you return multiple columns, 727 00:39:23,360 --> 00:39:25,640 it can execute multiple things all at once. 728 00:39:25,640 --> 00:39:30,200 So you can programmatically add 100 indexes all to the same column, 729 00:39:30,200 --> 00:39:33,640 I think was the example we were doing, or add the same index 730 00:39:33,640 --> 00:39:36,660 to 100 different tables that all have the similar naming convention, 731 00:39:36,740 --> 00:39:37,900 like that kind of thing. 732 00:39:37,900 --> 00:39:40,920 Nikolay: Yeah, for example, if you want to create 100 indexes 733 00:39:40,920 --> 00:39:44,680 and don't care about naming, or maybe if you care about naming, 734 00:39:45,300 --> 00:39:50,040 You could just have while \watch, not while, \watch command with 735 00:39:50,380 --> 00:39:55,780 specify number of loops and just format it gexes and if you want 736 00:39:55,880 --> 00:40:00,860 to specify naming with some increasing integer number you can 737 00:40:01,120 --> 00:40:05,720 remember what happened before and also memorize it using server-side 738 00:40:05,720 --> 00:40:06,540 in this case. 739 00:40:06,740 --> 00:40:10,960 Because it should be a single query, so you cannot combine G 740 00:40:10,960 --> 00:40:11,700 with gexec. 741 00:40:14,340 --> 00:40:20,200 It's better to have server-side using set_config() function, server-side 742 00:40:20,220 --> 00:40:26,120 variable, and then you can just add 1, plus 1, and have a different 743 00:40:26,120 --> 00:40:26,540 name. 744 00:40:26,540 --> 00:40:30,420 But in this case, I remember we didn't care about naming, I think, 745 00:40:30,600 --> 00:40:34,540 and I just didn't use name specification and create index command 746 00:40:34,540 --> 00:40:38,300 and you just get hundred indexes to check overhead I think right? 747 00:40:38,760 --> 00:40:41,480 Michael: Yes that was when we were checking planning time that 748 00:40:41,480 --> 00:40:43,120 was the planning time episode. 749 00:40:43,300 --> 00:40:46,560 Nikolay: Yeah it's fun thing that we still trying to polish that 750 00:40:46,560 --> 00:40:51,060 experiment and make it more like you know looking good and publish 751 00:40:51,060 --> 00:40:55,080 blog post about this so I hope it will be ended soon finish soon 752 00:40:55,080 --> 00:41:00,260 and we will have this blog post published so what else what else 753 00:41:00,360 --> 00:41:03,200 do you want to discuss \i or what? 754 00:41:03,480 --> 00:41:06,360 \o, input output, right? 755 00:41:07,440 --> 00:41:09,940 Michael: Yeah, I've seen I used with A. 756 00:41:10,080 --> 00:41:12,220 Nikolay: Yeah, \i just import something. 757 00:41:12,280 --> 00:41:16,720 It's good for programming mode when you want to structure your 758 00:41:16,720 --> 00:41:19,660 code base, psql code base. 759 00:41:19,860 --> 00:41:26,360 And \o is output, outputting, is moving output from 760 00:41:26,380 --> 00:41:29,400 your like terminal to some file, right? 761 00:41:29,760 --> 00:41:30,160 And I 762 00:41:30,160 --> 00:41:32,560 Michael: think it's the equivalent of like hyphen O when you 763 00:41:32,560 --> 00:41:34,540 use it from a script. 764 00:41:34,920 --> 00:41:39,180 And like I've used that for so query plans can get really big. 765 00:41:39,180 --> 00:41:44,140 If people have like several megabyte query plans, piping it to 766 00:41:44,140 --> 00:41:48,120 the clipboard or sending it to a file is way, way better for 767 00:41:48,120 --> 00:41:51,260 like copy and pasting those around than trying to copy them from 768 00:41:51,260 --> 00:41:51,920 a terminal. 769 00:41:52,200 --> 00:41:54,280 You know, if they're thousands of lines long. 770 00:41:54,280 --> 00:41:54,780 Nikolay: Yeah. 771 00:41:54,780 --> 00:41:58,940 Right, but there's also ability to have both, right? 772 00:41:59,180 --> 00:42:01,740 To print it in terminal and save to file. 773 00:42:01,980 --> 00:42:05,020 With \o and also pipe. 774 00:42:05,540 --> 00:42:07,260 Vertical line. 775 00:42:07,960 --> 00:42:12,880 But important thing is that if there are errors, they won't go 776 00:42:12,880 --> 00:42:13,880 to file, right? 777 00:42:14,380 --> 00:42:14,880 Unfortunately. 778 00:42:16,740 --> 00:42:17,240 Michael: Interesting. 779 00:42:17,600 --> 00:42:19,140 Nikolay: Yeah, yeah, yeah. 780 00:42:19,140 --> 00:42:19,700 I think so. 781 00:42:19,700 --> 00:42:20,480 Wait, why? 782 00:42:20,900 --> 00:42:25,080 Because output only successful results like 783 00:42:25,080 --> 00:42:25,440 Michael: a 784 00:42:25,440 --> 00:42:30,460 Nikolay: result set goes to not results that also system messages 785 00:42:30,460 --> 00:42:34,540 like number of rows this all goes goes to file because the show 786 00:42:34,540 --> 00:42:37,280 doesn't redirect errors, I think. 787 00:42:38,260 --> 00:42:38,760 Michael: Interesting. 788 00:42:39,140 --> 00:42:40,300 Yeah, good gotcha. 789 00:42:41,140 --> 00:42:44,100 You can't accuse me of always looking for gotchas and then bring 790 00:42:44,100 --> 00:42:45,120 them all up yourself. 791 00:42:45,740 --> 00:42:50,800 Nikolay: Maybe it's just like, you know, it's not just developed 792 00:42:50,820 --> 00:42:51,880 or something, right? 793 00:42:52,520 --> 00:42:57,660 And this is also the point when I usually, again, like go to 794 00:42:57,840 --> 00:43:07,420 shell level again and use T Hyphen a right and Use SDR as the 795 00:43:07,420 --> 00:43:09,120 SDR is to the out 796 00:43:10,680 --> 00:43:12,440 Michael: What's T So I'm thinking T 797 00:43:12,440 --> 00:43:13,200 Nikolay: is like. 798 00:43:13,780 --> 00:43:14,280 Tee. 799 00:43:14,720 --> 00:43:16,000 Michael: Oh, so sorry. 800 00:43:16,520 --> 00:43:17,300 Nikolay: Not British. 801 00:43:17,640 --> 00:43:18,140 Yeah. 802 00:43:19,300 --> 00:43:22,720 Michael: No, I was actually thinking of the, I've used 803 00:43:22,820 --> 00:43:25,920 \t to, you know, there's the beginning bit. 804 00:43:25,920 --> 00:43:28,920 So sometimes you get stuff around the rows. 805 00:43:30,040 --> 00:43:31,580 Nikolay: So like, tuples only, right? 806 00:43:31,780 --> 00:43:33,720 Michael: So T for tuples only, yeah, exactly. 807 00:43:33,760 --> 00:43:37,940 Nikolay: No, no, this T, like, when you want to see things and 808 00:43:37,940 --> 00:43:42,560 also to save things, if you're in programming mode, in this case, 809 00:43:43,660 --> 00:43:48,920 you have your script, dot psql script, you run it using psql, 810 00:43:49,360 --> 00:43:55,140 you can have pipe T hyphen a, hyphen n means don't overwrite, 811 00:43:55,400 --> 00:44:00,480 just like append to the file, and you can also print, t also 812 00:44:00,480 --> 00:44:04,920 prints it, the output, and in this case not to lose errors, you 813 00:44:04,920 --> 00:44:10,080 can redirect errors, STD air to STD out, using, you know, like 814 00:44:10,080 --> 00:44:13,260 this, like ampersand and so on. 815 00:44:13,260 --> 00:44:17,560 And in this case, you have both errors and normal messages going 816 00:44:17,560 --> 00:44:21,240 both to files and to your terminal. 817 00:44:21,420 --> 00:44:28,040 And this is exactly how I like to see things and not to lose 818 00:44:28,040 --> 00:44:29,000 them with timestamps. 819 00:44:29,380 --> 00:44:33,200 For example, TS from MoralTools, TS also good thing. 820 00:44:33,820 --> 00:44:37,660 You're prefixing everything with timestamps, so you work and 821 00:44:37,660 --> 00:44:41,940 you also save everything to file so if something goes wrong you 822 00:44:41,940 --> 00:44:45,640 can troubleshoot, analyze, to do post-mortem, root cause analysis, 823 00:44:45,660 --> 00:44:49,640 anything because you have all the logs of your actions. 824 00:44:49,640 --> 00:44:51,760 Michael: Yeah and the other way around is useful as well if you 825 00:44:51,760 --> 00:44:55,580 want it in the file for like if you want the file for reasons 826 00:44:56,100 --> 00:44:59,580 you're getting feedback in the terminal that it's actually working, 827 00:44:59,580 --> 00:45:00,540 that it's actually doing what you expect. 828 00:45:00,540 --> 00:45:02,120 Nikolay: Yeah you see what's happening. 829 00:45:02,140 --> 00:45:06,160 Well some people like don't like this and say let's not leave 830 00:45:06,160 --> 00:45:07,860 it and no hub. 831 00:45:08,360 --> 00:45:09,780 Let's use no hub. 832 00:45:09,860 --> 00:45:13,820 So like detach it and it's running in like basically in background. 833 00:45:13,940 --> 00:45:18,020 And then you can observe the file using just tail hyphen F, right? 834 00:45:18,160 --> 00:45:19,100 Also fair. 835 00:45:20,220 --> 00:45:25,900 And this is how you can do a lot of quite complex coding using 836 00:45:25,900 --> 00:45:26,400 psql. 837 00:45:27,180 --> 00:45:32,760 And this can be building blocks for very, very, very complex 838 00:45:32,760 --> 00:45:33,260 automation. 839 00:45:33,380 --> 00:45:37,360 For example, if you use Ansible, basically it's running something 840 00:45:37,540 --> 00:45:38,040 remotely. 841 00:45:39,060 --> 00:45:45,180 And sometimes, like in our case, sometimes it's a bunch of psql 842 00:45:45,240 --> 00:45:49,300 lines and we'd run at them remotely and they can be important 843 00:45:49,300 --> 00:45:53,240 pieces of such things as like 0 downtime upgrades. 844 00:45:56,800 --> 00:46:02,160 The only thing I must mention always based on my mistakes from 845 00:46:02,160 --> 00:46:04,140 the past and my team's mistakes. 846 00:46:05,860 --> 00:46:10,620 When you do automation, don't forget, hyphen X, capital X. 847 00:46:11,520 --> 00:46:12,480 Super important. 848 00:46:12,720 --> 00:46:13,220 Yeah. 849 00:46:13,780 --> 00:46:15,040 Hyphen A is good. 850 00:46:15,040 --> 00:46:16,500 Hyphen T is good. 851 00:46:16,500 --> 00:46:21,180 Everything, but hyphen X will save you 1 day. 852 00:46:21,300 --> 00:46:29,860 Because if somebody left .psqlrc configuration file with timing 853 00:46:29,860 --> 00:46:34,040 on, for example, all your logic comparing output to something 854 00:46:35,080 --> 00:46:38,960 can be super broken and can be unnoticed in tests, unfortunately. 855 00:46:40,200 --> 00:46:43,820 Michael: So capital X ignores psqlRC? 856 00:46:44,760 --> 00:46:45,420 Nikolay: Yeah, yeah. 857 00:46:45,440 --> 00:46:48,640 So it switches to default behavior and it's good for a programming 858 00:46:48,640 --> 00:46:49,140 mode. 859 00:46:49,700 --> 00:46:52,700 psqlRC is good for interactive mode. 860 00:46:53,360 --> 00:46:56,760 It's bad for a programming mode because if you have a new server 861 00:46:56,760 --> 00:46:57,980 and you don't have this... 862 00:46:57,980 --> 00:47:00,060 Well, it depends on some organization, right? 863 00:47:00,060 --> 00:47:04,140 Maybe we have a rule to put some specific psqlRC everywhere. 864 00:47:04,440 --> 00:47:05,820 In this case, it's okay. 865 00:47:06,420 --> 00:47:11,000 But if you don't know what will happen in the future, it's better 866 00:47:11,000 --> 00:47:13,300 to just ignore those adjustments. 867 00:47:14,580 --> 00:47:16,940 Michael: I like doing it with X. 868 00:47:16,940 --> 00:47:21,540 If it's a script, it's a script already, why not set any config 869 00:47:21,540 --> 00:47:25,860 you want in the script and then use hyphen X like it that makes 870 00:47:25,860 --> 00:47:28,640 way more sense to me than relying on something else 871 00:47:29,440 --> 00:47:32,560 Nikolay: maybe maybe there are options here. 872 00:47:32,560 --> 00:47:37,360 It's good to develop something here, not just to forget about 873 00:47:37,360 --> 00:47:37,800 this. 874 00:47:37,800 --> 00:47:40,760 This happens all the time, people, and I did it as well. 875 00:47:40,760 --> 00:47:45,820 We forget it, and then on staging we don't have psqlRC, on production 876 00:47:45,820 --> 00:47:48,360 we have, and boom, it's not working. 877 00:47:48,360 --> 00:47:52,860 Or even worse it's working in the wrong way right 878 00:47:53,940 --> 00:47:55,740 Michael: yeah good point yeah nasty 879 00:47:56,400 --> 00:48:01,460 Nikolay: that's why I like hyphen X hyphen capital X okay yeah 880 00:48:01,460 --> 00:48:05,020 there are like we maybe touched like couple of percent of what's 881 00:48:05,020 --> 00:48:09,940 possible as usual right We wanted to call it advanced psql, right? 882 00:48:09,940 --> 00:48:12,100 But there are many more things. 883 00:48:12,900 --> 00:48:15,400 Michael: So we can point people out, I'll include a link to the 884 00:48:15,400 --> 00:48:20,060 psql docs in the show notes, and also to, there's a site by Lætitia 885 00:48:20,060 --> 00:48:23,500 Avrot, we mentioned last time as well, called psql tips. 886 00:48:23,540 --> 00:48:26,280 And if you go to it, it's a website that will just give you a 887 00:48:26,280 --> 00:48:27,780 random tip about psql. 888 00:48:28,240 --> 00:48:33,040 Every, you know, if you could set your homepage to it, or your 889 00:48:33,040 --> 00:48:35,580 new tab page to it, and you might learn the odd. 890 00:48:35,580 --> 00:48:38,480 That's like a way of discovering new things that you might not 891 00:48:38,480 --> 00:48:40,160 know it can do, which is quite cool. 892 00:48:40,160 --> 00:48:43,220 Nikolay: Yeah, let me mention a few more things I wanted to mention. 893 00:48:43,860 --> 00:48:48,420 If you find yourself spending too much time inside psql like 894 00:48:48,420 --> 00:48:53,040 I do, you will probably like \! mark because 895 00:48:53,040 --> 00:48:54,900 it can run anything, right? 896 00:48:54,900 --> 00:49:00,180 You can, for example, run LS, PS, stop, anything you want, SSH. 897 00:49:01,360 --> 00:49:06,140 So it's basically running something, some shell right from psql. 898 00:49:07,200 --> 00:49:12,160 And also I like the fact that I think in Postgres 16 it started 899 00:49:12,160 --> 00:49:16,480 to be possible to work with extended protocol and to debug some 900 00:49:16,480 --> 00:49:17,940 stuff from there. 901 00:49:18,540 --> 00:49:22,580 And also I like, I don't use it, but it's so quite powerful. 902 00:49:22,800 --> 00:49:27,420 You can run multiple, you can send multiple statements in 1 shot. 903 00:49:28,180 --> 00:49:32,780 If you, instead of semicolon use \:. 904 00:49:34,140 --> 00:49:39,000 Right, in this case, you can combine many different queries and 905 00:49:39,000 --> 00:49:40,120 send them at once. 906 00:49:40,330 --> 00:49:41,400 Sometimes it's also... 907 00:49:41,400 --> 00:49:42,320 Michael: Are these documented? 908 00:49:42,520 --> 00:49:44,480 I don't remember reading about these. 909 00:49:44,600 --> 00:49:45,860 Nikolay: Yeah, it should be documented. 910 00:49:47,540 --> 00:49:48,620 Michael: That's quite cool. 911 00:49:48,680 --> 00:49:52,440 Nikolay: Yeah, so I don't use it often but it's quite powerful, 912 00:49:52,440 --> 00:49:59,440 it also shows like you can do so many things right inside psql. 913 00:50:00,140 --> 00:50:04,000 Michael: Yeah I just did \? which by the 914 00:50:04,000 --> 00:50:09,060 way is like at least 50 lines of information straight away, and 915 00:50:09,060 --> 00:50:13,140 then \! is listed even in that. 916 00:50:13,140 --> 00:50:15,600 Nikolay: \! I use all the time, Because 917 00:50:15,600 --> 00:50:18,660 I just don't want to quit from psql, right? 918 00:50:19,820 --> 00:50:19,940 Michael: And 919 00:50:19,940 --> 00:50:22,680 Nikolay: we don't need to mention that quitting from psql is 920 00:50:22,680 --> 00:50:26,940 \q because you can right now write exit or quit Since 921 00:50:26,940 --> 00:50:29,700 I don't know I don't from version 14 15. 922 00:50:29,700 --> 00:50:33,720 I don't remember when it was added maybe earlier So this is not 923 00:50:33,720 --> 00:50:37,320 Michael: to be the easiest to quit command-line tool ever 924 00:50:38,040 --> 00:50:40,480 Nikolay: It's not VI style anymore, right? 925 00:50:40,920 --> 00:50:45,320 So yeah, a lot of stuff is possible and it's good that Again, 926 00:50:45,320 --> 00:50:50,100 this is the only 1 single official client in Postgres project. 927 00:50:50,920 --> 00:50:54,560 So it's worth learning it and using it more. 928 00:50:55,340 --> 00:50:59,480 We mentioned in the episode about comparing it to graphical interfaces. 929 00:51:00,140 --> 00:51:04,520 We mentioned that terminal is good for expected automation. 930 00:51:05,540 --> 00:51:08,860 Like, basically for automation, you have expected behavior, you 931 00:51:09,060 --> 00:51:12,440 just program something and you can put it to CI-CD pipelines 932 00:51:12,540 --> 00:51:13,400 or anywhere. 933 00:51:13,880 --> 00:51:14,380 Right? 934 00:51:14,640 --> 00:51:18,180 Unlike if you have some graphic interface, what else? 935 00:51:18,180 --> 00:51:19,320 Like, it's not good. 936 00:51:19,320 --> 00:51:25,440 Of course, you can use cloud computer use which was released 937 00:51:25,440 --> 00:51:31,160 yesterday I'm joking I'm trying to like insult you already so 938 00:51:31,160 --> 00:51:31,460 yeah 939 00:51:31,460 --> 00:51:34,440 Michael: well I don't think it's a great argument in terms of 940 00:51:34,440 --> 00:51:39,640 the, like, you can use a graphical tool to come up with the query 941 00:51:39,640 --> 00:51:43,340 you want to run, like, via the shell. 942 00:51:44,020 --> 00:51:47,360 But yeah, some of the interactive, I'm saying interactive when 943 00:51:47,360 --> 00:51:49,640 I mean the opposite some of the stuff we've been talking about 944 00:51:49,640 --> 00:51:53,480 to programmatically use psql it makes sense like obviously 945 00:51:53,480 --> 00:51:55,920 you're going to use psql eventually so you might as well 946 00:51:55,920 --> 00:52:00,920 do it but I still find it some sometimes easier to play around 947 00:52:00,920 --> 00:52:04,700 with queries in a graphical interface, personally. 948 00:52:04,940 --> 00:52:05,280 I understand that. 949 00:52:05,280 --> 00:52:06,600 Like editing them and things. 950 00:52:06,600 --> 00:52:08,180 Nikolay: Yeah, I understand that, of course. 951 00:52:08,200 --> 00:52:14,580 If it's a huge Query and you are not using a VI by default, I 952 00:52:14,580 --> 00:52:15,560 can understand this. 953 00:52:16,080 --> 00:52:17,580 Michael: Yeah, yeah, good point. 954 00:52:18,700 --> 00:52:19,200 Cool. 955 00:52:19,300 --> 00:52:23,940 Nikolay: Good, yeah, I like this thing and hopefully we will 956 00:52:23,940 --> 00:52:26,200 see development of this further and further. 957 00:52:26,200 --> 00:52:27,840 I mean, psql features. 958 00:52:28,660 --> 00:52:31,740 I feel potential to have more and more. 959 00:52:32,140 --> 00:52:33,080 Good, thank you. 960 00:52:33,080 --> 00:52:34,060 Michael: That's all, Nikolay. 961 00:52:34,240 --> 00:52:35,220 Thanks so much. 962 00:52:35,220 --> 00:52:35,740 Nikolay: Thank you.