WEBVTT 1 00:00:00.080 --> 00:00:02.720 Okay, let's unpack this. If you think of Postgres School 2 00:00:02.720 --> 00:00:05.040 server is just you know, place to store data like 3 00:00:05.080 --> 00:00:07.879 a big passive bucket, you're missing out on a tremendous 4 00:00:07.919 --> 00:00:12.439 amount of power. Today we're diving into Postgres School server programming. 5 00:00:12.880 --> 00:00:15.839 We're drawing from some really insightful excerpts from the technical 6 00:00:15.880 --> 00:00:19.600 book Postgreschool Server Programming. Our mission really is to show 7 00:00:19.640 --> 00:00:21.760 you how Postgres School can actually be seen as an 8 00:00:21.800 --> 00:00:23.359 application development framework. 9 00:00:23.839 --> 00:00:23.960 Now. 10 00:00:24.039 --> 00:00:28.079 Pushing logic directly to the database layer offers well shortcuts 11 00:00:28.120 --> 00:00:33.359 to building faster, more secure, and highly maintainable applications. Yeah. 12 00:00:33.359 --> 00:00:36.240 And what's fascinating here is we're not just talking about 13 00:00:36.320 --> 00:00:39.679 database administration and the traditional sense, not at all. We're 14 00:00:39.719 --> 00:00:44.000 exploring how the database itself can become like a proactive, 15 00:00:44.039 --> 00:00:46.200 intelligent part of your application's core logic. 16 00:00:46.359 --> 00:00:46.560 Right. 17 00:00:46.640 --> 00:00:48.960 It stops being just passive storage and starts being an 18 00:00:48.960 --> 00:00:52.079 active participant, your single source of truth for business rules. 19 00:00:52.079 --> 00:00:55.039 You know, hashtag tag the transformative power of server programming. 20 00:00:55.119 --> 00:00:59.000 So let's challenge that whole data bucket philosophy. Yeah, imagine 21 00:00:59.000 --> 00:01:02.439 a common situation like a bank transfer. Say Bob wants 22 00:01:02.479 --> 00:01:04.920 to send I don't know fourteen dollars to Mary h 23 00:01:05.000 --> 00:01:07.439 The typical way you might write separate sequl commands from 24 00:01:07.439 --> 00:01:10.719 your application, right, want to take money from Bob a debit, 25 00:01:10.799 --> 00:01:13.280 another to give to Mary a credit? But I mean 26 00:01:13.319 --> 00:01:16.000 think about the problems there at first security, Do your 27 00:01:16.000 --> 00:01:19.519 app really need to know Bob's balance or just can't 28 00:01:19.519 --> 00:01:20.239 he send the money? 29 00:01:20.319 --> 00:01:21.000 That's a big one. 30 00:01:21.120 --> 00:01:23.959 Then speed, you've got multiple round trips to the database 31 00:01:24.120 --> 00:01:27.879 slows things down. And the big one integrity. What if 32 00:01:28.439 --> 00:01:31.159 the system crashes halfway through, Bob's money is gone, Mary 33 00:01:31.200 --> 00:01:31.799 never gets it. 34 00:01:32.280 --> 00:01:35.840 Nightmare scenario exactly, And that's precisely why server programming is 35 00:01:35.840 --> 00:01:39.280 such a well a game changer. By executing that transfer 36 00:01:39.359 --> 00:01:42.680 logic directly on the database server, you're centralizing it. It's 37 00:01:42.719 --> 00:01:46.439 not just tidy code. It's a fundamental shift. You're putting 38 00:01:46.439 --> 00:01:50.480 the rules in the most robust place, the database itself. 39 00:01:50.680 --> 00:01:52.560 It becomes this active. 40 00:01:52.120 --> 00:01:53.200 Partner active partner. 41 00:01:53.239 --> 00:01:55.280 I like that, and it cuts out all those network 42 00:01:55.359 --> 00:01:58.599 round trips, so it's way faster. You write a function. 43 00:01:59.040 --> 00:02:01.959 Maybe in plpg's it does all the checks, does Bob 44 00:02:02.040 --> 00:02:05.719 exist enough funds, is Merry valid? And it just returns okay, 45 00:02:06.719 --> 00:02:09.919 or you know, not enough funds. The client app doesn't 46 00:02:09.919 --> 00:02:12.759 need the details, just the success or failure. No sensitive 47 00:02:12.840 --> 00:02:13.520 data leakage. 48 00:02:13.639 --> 00:02:18.960 That is incredibly powerful for security, for consistency, yeah yeah, 49 00:02:19.000 --> 00:02:21.439 But what if you want the database to react automatically 50 00:02:21.639 --> 00:02:26.439 like inventory management, stock levels, change offers, need updating instantly, 51 00:02:26.840 --> 00:02:28.319 no app involved triggers. 52 00:02:28.360 --> 00:02:31.680 That's exactly what triggers are for magic pretty much. They 53 00:02:31.759 --> 00:02:35.439 let operations in the database automatically cause other things to happen. 54 00:02:35.520 --> 00:02:37.520 So say you've got your fruit sinstock and your fruit 55 00:02:37.520 --> 00:02:40.159 offer tables, A trigger can make sure that when you 56 00:02:40.240 --> 00:02:43.120 update an offer amount for apples, uh huh, the reserved 57 00:02:43.159 --> 00:02:46.639 count in fruits in stock just adjusts automatically. It stops 58 00:02:46.639 --> 00:02:49.360 you over reserving, stops you selling stock that's already spoken for, 59 00:02:49.759 --> 00:02:52.319 even across different tables. It enforces that link. 60 00:02:52.360 --> 00:02:53.159 Okay, that's clever. 61 00:02:53.439 --> 00:02:58.000 And building on that automatic reaction idea, triggers are fantastic 62 00:02:58.080 --> 00:03:01.919 for auditing, indispensable. You can set one up to log 63 00:03:02.039 --> 00:03:05.680 every single action on important tables like imagine a salary's table. 64 00:03:06.080 --> 00:03:09.080 An audit trigger can grab the username, the timestamp, the 65 00:03:09.280 --> 00:03:13.080 operation type, insert, update, delete, and even capture the old 66 00:03:13.159 --> 00:03:15.840 salary and the new salary. Maybe store the whole old 67 00:03:15.879 --> 00:03:18.639 and new row as JSON for a complete record. It's 68 00:03:18.639 --> 00:03:22.319 a rock solid audit trail. It's automatic, totally automatic. Plus 69 00:03:22.360 --> 00:03:27.759 triggers can enforce data consistency, simple things like automatically converting 70 00:03:27.800 --> 00:03:30.919 an employee name to uppercase whenever it's inserted or updated. 71 00:03:31.039 --> 00:03:34.400 Ensures uniformity. You know, no need to rely on every 72 00:03:34.439 --> 00:03:36.000 client app remembering the rule. 73 00:03:36.400 --> 00:03:41.280 So functions, centralized logic, triggers automate reactions. But what about 74 00:03:41.360 --> 00:03:45.879 teaching postcress new tricks entirely? What does extensibility really mean here? 75 00:03:46.000 --> 00:03:47.240 Is it just more functions? 76 00:03:47.319 --> 00:03:50.280 Oh, It's much more profound than just functions. Extensibility means 77 00:03:50.280 --> 00:03:53.919 you can actually extend postgres school itself. It's language, it's capabilities. 78 00:03:54.159 --> 00:03:57.759 You can define your own custom data types like uh 79 00:03:58.000 --> 00:04:01.240 fruity maybe that understands bushels. And you can teach it 80 00:04:01.280 --> 00:04:05.400 custom operators like how to compare apples and oranges directly 81 00:04:05.439 --> 00:04:10.319 in a query? H seriously, well metaphorically maybe, But you 82 00:04:10.319 --> 00:04:14.199 can define operators for your custom types. And yes, user 83 00:04:14.240 --> 00:04:17.879 define functions. UDFs are a huge part, and these functions 84 00:04:17.920 --> 00:04:21.360 can get sophisticated. They can handle complex arguments like XML 85 00:04:21.439 --> 00:04:25.240 or JSON or return results in those formats too great 86 00:04:25.279 --> 00:04:28.480 for modern architectures like SOA or micro services. 87 00:04:28.680 --> 00:04:32.160 Right makes sense, and you mentioned performance earlier. Extensibility ties 88 00:04:32.160 --> 00:04:34.560 into that too, especially server side caching. 89 00:04:34.720 --> 00:04:38.240 Absolutely, server side casing is a classic pattern enabled by functions. 90 00:04:38.560 --> 00:04:42.399 The logic is simple. Really, inside your function, first check 91 00:04:42.439 --> 00:04:44.560 if the value you need is already cashed, maybe in 92 00:04:44.600 --> 00:04:47.920 a table. Is it there, is it recent enough? If yes, great, 93 00:04:47.959 --> 00:04:50.519 return it. If not, compute it, store it in the cash, 94 00:04:50.560 --> 00:04:51.319 then retun it. 95 00:04:51.240 --> 00:04:52.279 So next time it's fast. 96 00:04:52.519 --> 00:04:56.319 Exactly. Think about calculating total company sales that might involve 97 00:04:56.399 --> 00:05:00.920 querying thousands of rows cash the result. There's this great 98 00:05:00.959 --> 00:05:04.560 story about a Java e email system. It was sending 99 00:05:04.560 --> 00:05:08.000 maybe a few hundred emails a second, okay, but not amazing. 100 00:05:08.600 --> 00:05:12.079 Then someone rewrote the email generation part as ap peel 101 00:05:12.120 --> 00:05:14.199 Peril function inside. 102 00:05:13.720 --> 00:05:15.639 Postgres school, inside the database. 103 00:05:15.720 --> 00:05:18.600 Inside the database, suddenly it wasn't sending hundreds, It was 104 00:05:18.639 --> 00:05:21.199 spewing out tens of thousands of emails per second. 105 00:05:21.360 --> 00:05:23.839 Wow, that's that's incredible. 106 00:05:23.879 --> 00:05:27.680 It was all because the computation happened near data minimal 107 00:05:27.720 --> 00:05:30.480 network latency. It went from like a two point two 108 00:05:30.519 --> 00:05:33.079 millisecond query from the client to zero point one to 109 00:05:33.160 --> 00:05:36.279 two milliseconds inside the database. Huge difference. 110 00:05:36.319 --> 00:05:38.319 That is a massive jump, and you can see how 111 00:05:38.360 --> 00:05:41.639 that ties into maintenance and security too. If all your 112 00:05:41.639 --> 00:05:45.879 core data logic is in database functions or views, upgrading 113 00:05:45.959 --> 00:05:49.040 is just running a script, a detail. 114 00:05:48.720 --> 00:05:51.720 Script, YEP, data definition language. You update the function in 115 00:05:51.759 --> 00:05:52.839 the database. 116 00:05:52.519 --> 00:05:55.560 No downtime, no complex roll out across all your different. 117 00:05:55.279 --> 00:05:57.839 Client apps, exactly. All clients just start using the new 118 00:05:57.879 --> 00:06:01.839 logic immediately. It simplifies maintenance enormously. It's security big win 119 00:06:01.879 --> 00:06:05.319 there too. If you grant database users permission to execute 120 00:06:05.480 --> 00:06:09.319 only specific functions, not direct access to tables, then even 121 00:06:09.319 --> 00:06:12.360 if an attacker compromises your web server, say, they can't 122 00:06:12.399 --> 00:06:15.920 just run select from users and dump all your data. 123 00:06:16.000 --> 00:06:17.839 They can only run the functions you've a looked. 124 00:06:17.720 --> 00:06:20.519 What you've designed to be safe right to only return 125 00:06:20.639 --> 00:06:25.720 specific non sensitive info or perform validated actions. It drastically 126 00:06:25.759 --> 00:06:28.839 reduces the attack surface hashtag tag tech trag y. Postgress 127 00:06:28.839 --> 00:06:30.839 School the environment advantage. 128 00:06:31.360 --> 00:06:34.240 So, okay, putting lodging in the database is powerful, we 129 00:06:34.279 --> 00:06:37.639 get that, but why postgres school specifically, what makes it 130 00:06:38.160 --> 00:06:42.040 the go to platform for this kind of server programming. 131 00:06:42.879 --> 00:06:45.240 Well, first off, the cost of acquisition. 132 00:06:44.800 --> 00:06:46.600 It's zero. Can't be free, right. 133 00:06:46.720 --> 00:06:51.079 Compare that to commercial databases SEQL server Oracle. You're talking 134 00:06:51.199 --> 00:06:55.240 potentially thousands, even tens of thousands in licensing fees. Postgress 135 00:06:55.240 --> 00:06:57.959 will costs nothing to download, nothing to use, and this 136 00:06:58.079 --> 00:07:00.680 directly leads to a low cost of failure. When you're 137 00:07:00.680 --> 00:07:03.959 starting a project things are uncertain, you might experiment change direction. 138 00:07:04.600 --> 00:07:06.879 Being able to try things out, even scrap an idea 139 00:07:07.240 --> 00:07:10.600 without being locked into expensive long term licenses, that's a 140 00:07:10.720 --> 00:07:12.160 huge advantage. It frees you up to. 141 00:07:12.079 --> 00:07:16.120 Innovate definitely, and that ties into licensing more broadly, doesn't it. 142 00:07:16.199 --> 00:07:20.120 I remember the unease when Oracle bought my sequel that 143 00:07:20.439 --> 00:07:22.240 vast and thunderous sucking sound. 144 00:07:22.240 --> 00:07:25.279 Does someone put it huh, yes, exactly. There is a 145 00:07:25.319 --> 00:07:28.040 lot of uncertainty about the future of my squels licensing. 146 00:07:28.399 --> 00:07:33.480 Postgres School's license, though, is incredibly liberal bs D style. Basically, 147 00:07:33.800 --> 00:07:36.600 you can have the source code, do anything with it 148 00:07:36.639 --> 00:07:40.240 that you want, redistribute it however you jolly well please, 149 00:07:40.279 --> 00:07:42.560 and those rights extend indefinitely. 150 00:07:42.800 --> 00:07:43.160 Wow. 151 00:07:43.319 --> 00:07:46.759 For companies, that removes massive legal risk. You know, the 152 00:07:46.879 --> 00:07:49.279 terms won't suddenly change. You know, you can build products 153 00:07:49.279 --> 00:07:51.319 on it without unexpected fees popping up. 154 00:07:51.399 --> 00:07:52.639 That's huge peace of mind. 155 00:07:52.759 --> 00:07:56.040 Then there's predictability. Postgreds School is known for sticking really 156 00:07:56.079 --> 00:07:59.000 closely to SQL standards. Now, sometimes that might feel a 157 00:07:59.000 --> 00:08:01.920 bit stricter, maybe less forgiving than other databases if you 158 00:08:01.959 --> 00:08:07.319 write slightly nonstandard sequel, but the upside is fewer unintended behaviors, 159 00:08:07.639 --> 00:08:10.199 fewer surprises. You know it's going to work the way 160 00:08:10.240 --> 00:08:13.399 the documentation says it works. That means less chance of 161 00:08:13.439 --> 00:08:16.079 your application breaking in subtle ways down the road because 162 00:08:16.120 --> 00:08:19.800 of some weird database quirk. It provides a really stable foundation. 163 00:08:19.920 --> 00:08:20.600 You can trust it. 164 00:08:21.079 --> 00:08:26.120 Okay, stability, predictability, Yeah, what else? You mentioned something about 165 00:08:26.120 --> 00:08:27.240 the community earlier. 166 00:08:27.319 --> 00:08:30.040 Ah, yeah, the community aspect is actually really interesting and 167 00:08:30.120 --> 00:08:33.759 often overlooked. With commercial databases, trying to talk to the 168 00:08:33.799 --> 00:08:38.159 actual core developers, good luck, you're usually dealing with layers of. 169 00:08:38.080 --> 00:08:39.879 Support stat right ticketing systems. 170 00:08:40.000 --> 00:08:43.759 Exactly with postgres School, many of the core developers hang 171 00:08:43.799 --> 00:08:46.360 out on IRC channels, they go to conferences, you can 172 00:08:46.399 --> 00:08:47.399 actually talk to them. 173 00:08:47.320 --> 00:08:48.440 And they like beer apparently. 174 00:08:48.879 --> 00:08:51.279 Well the book mentions that, But the point is there's 175 00:08:51.320 --> 00:08:55.080 this direct connection. It fosters a real sense of shared 176 00:08:55.120 --> 00:08:58.759 ownership and concern for the project's health. Expert help is 177 00:08:58.840 --> 00:09:00.600 often just a question of a way. It's a very 178 00:09:00.720 --> 00:09:01.799 vibrant ecosystem. 179 00:09:02.000 --> 00:09:03.200 That's a really different feel. 180 00:09:03.480 --> 00:09:07.039 Definitely, and technically a huge advantage is its support for 181 00:09:07.240 --> 00:09:11.200 multiple procedural languages. It's not just plpgcquall the built in one. 182 00:09:11.519 --> 00:09:15.440 You can write server side functions in Python, Perl, TCL, 183 00:09:15.639 --> 00:09:16.039 even C. 184 00:09:16.519 --> 00:09:18.360 And you can add or remove these languages on. 185 00:09:18.360 --> 00:09:21.799 A running server, no downtime needed to install, Say peel 186 00:09:21.799 --> 00:09:25.159 Python support and think back to our earlier example. The 187 00:09:25.240 --> 00:09:28.799 bad way of doing things maybe with PHP connecting, pulling data, 188 00:09:29.240 --> 00:09:32.279 figuring things out client side, then sending an update back. 189 00:09:32.200 --> 00:09:35.600 Right, the multiple roundtrips, the potential integrity issues exactly. 190 00:09:35.639 --> 00:09:39.720 That leads to terrible scalability. The right way is pushing 191 00:09:39.759 --> 00:09:42.720 that logic into a database function using one of these 192 00:09:42.759 --> 00:09:45.600 powerful languages. A single efficient. 193 00:09:45.200 --> 00:09:48.720 Call makes sense. What about transactions? How does post grace 194 00:09:48.720 --> 00:09:49.200 handle those? 195 00:09:49.279 --> 00:09:52.559 It's very sensible, very safe. By default. The standard isolation 196 00:09:52.679 --> 00:09:56.360 level is read committed me, meaning your transaction will only 197 00:09:56.399 --> 00:09:58.960 ever see data that has been fully committed, fully saved 198 00:09:59.000 --> 00:10:03.000 by other transactions. You don't get dirty reads seeing incomplete 199 00:10:03.080 --> 00:10:05.279 changes that might get rolled back later. That avoids a 200 00:10:05.279 --> 00:10:06.279 whole class of bugs. 201 00:10:06.399 --> 00:10:08.120 That sounds safer it is. 202 00:10:08.360 --> 00:10:12.120 And by default, each individual seql statement you send acts 203 00:10:12.159 --> 00:10:15.399 as its own little transaction. It commits immediately, though client 204 00:10:15.440 --> 00:10:19.159 tools like fusequal often wrap things in a transaction block automatically. 205 00:10:19.200 --> 00:10:22.039 If you're typing multiple commands okay, and one more thing 206 00:10:22.039 --> 00:10:26.120 on control postgrescool gives you more control over how your 207 00:10:26.120 --> 00:10:31.120 functions execute, specifically around security, their security and voker. That's 208 00:10:31.159 --> 00:10:34.039 the default. The function runs with the permissions of the 209 00:10:34.159 --> 00:10:37.960 user calling it, but there's also security definer. This is 210 00:10:38.000 --> 00:10:40.159 really useful. The function runs with the permissions of the 211 00:10:40.279 --> 00:10:41.200 user who created it. 212 00:10:41.440 --> 00:10:45.159 Ah, so you could let a less privileged user run 213 00:10:45.200 --> 00:10:49.720 a function that needs higher privileges for a very specific controlled. 214 00:10:49.240 --> 00:10:54.919 Task, precisely temporary safe privilege escalation. They can't access the 215 00:10:55.000 --> 00:10:57.720 underlying table directly, but they can call the function which 216 00:10:57.759 --> 00:11:00.639 performs a specific safe action on their behalf. And you 217 00:11:00.639 --> 00:11:03.120 can also define a cost for a function. This is 218 00:11:03.159 --> 00:11:05.320 like giving a hint to the query planner, the. 219 00:11:05.200 --> 00:11:07.519 Part of the database that figures out how to run queries. 220 00:11:07.840 --> 00:11:09.799 Yeah, you can tell it. Hey, this function is really 221 00:11:09.840 --> 00:11:12.000 expensive to run, so the planner will try to avoid 222 00:11:12.039 --> 00:11:14.360 calling it too often if it can find a cheaper 223 00:11:14.399 --> 00:11:18.240 way to get the result. Helps optimize overall performance. Hashtag 224 00:11:18.480 --> 00:11:21.799 hashtag deep dive into language capabilities and advanced techniques. 225 00:11:22.039 --> 00:11:23.960 Okay, let's dive into some of the actual coding. Then 226 00:11:24.080 --> 00:11:27.240 the warkhorse so PLPG SQUL you called it the workhoorse. 227 00:11:27.279 --> 00:11:30.279 It's built in, no extra setup needed. What makes it 228 00:11:30.320 --> 00:11:34.000 so fundamental for server side logic and postgress. 229 00:11:34.039 --> 00:11:39.120 It's just incredibly well suited for combining SQL with procedural logic, 230 00:11:39.200 --> 00:11:43.360 you know, if statements, loops, variables. It's great for encapsulating 231 00:11:43.399 --> 00:11:46.399 those multi step operations we talked about, like the bank transfer. 232 00:11:46.720 --> 00:11:49.399 You can access function arguments easily, either by their position 233 00:11:49.519 --> 00:11:51.720 or by a name. You've got your standard of penols, 234 00:11:51.799 --> 00:11:54.559 your case. Statements for conditional logic loops are there too. 235 00:11:54.799 --> 00:11:59.279 But a really important warning. Looping through query results inside 236 00:11:59.320 --> 00:12:04.919 plpg'sl Generally that's considered doing it wrong. It's usually very inefficient, 237 00:12:05.159 --> 00:12:08.960 high processor cost, high memory use. SQL is designed for 238 00:12:09.080 --> 00:12:12.840 set based operations. You should almost always try to find 239 00:12:12.879 --> 00:12:16.279 a way to express your logic using SQL sets rather 240 00:12:16.360 --> 00:12:20.399 than row biro looping in plpg sql let the database 241 00:12:20.440 --> 00:12:22.279 engine optimize the set operation. 242 00:12:22.519 --> 00:12:25.519 Good tip. Okay, what about getting results out of functions? 243 00:12:25.840 --> 00:12:29.279 Right? You use perform if you just want to execute 244 00:12:29.279 --> 00:12:31.600 a query for its side effects like an insert or update, 245 00:12:31.639 --> 00:12:33.440 and you don't care about the result. If you do 246 00:12:33.519 --> 00:12:35.840 need the result, you use select into to put it 247 00:12:35.919 --> 00:12:36.720 into a variable. 248 00:12:36.919 --> 00:12:39.360 Makes sense. And you can return more than just single 249 00:12:39.440 --> 00:12:41.720 values right like lists or tables? 250 00:12:41.799 --> 00:12:44.279 Oh yeah, absolutely, you can return a set of integer 251 00:12:44.639 --> 00:12:48.240 like generating the Fibonacci sequence. You can return set of 252 00:12:48.399 --> 00:12:51.919 table name, which effectively returns rows matching that table structure. 253 00:12:52.399 --> 00:12:55.559 You can define functions with out parameters which become part 254 00:12:55.559 --> 00:12:58.639 of the result row, or use the very clear returns 255 00:12:58.679 --> 00:13:01.360 table syntax to define the output columns right there. 256 00:13:01.440 --> 00:13:03.279 What if the structure isn't known beforehand? 257 00:13:03.440 --> 00:13:06.720 Good question. You can use return set off record, but 258 00:13:06.759 --> 00:13:09.039 then you have to provide a column definition list when 259 00:13:09.080 --> 00:13:12.399 you call the function. More flexibly, you can use polymorphic 260 00:13:12.440 --> 00:13:14.840 types like set off an element or set off any 261 00:13:14.879 --> 00:13:18.360 compatible for functions that can work with or return different 262 00:13:18.480 --> 00:13:22.440 but related types. And there's variatic arguments, which lets a 263 00:13:22.480 --> 00:13:25.320 function accept an arbitrary number of arguments of a certain 264 00:13:25.360 --> 00:13:27.639 type past as an array super flexible. 265 00:13:27.720 --> 00:13:32.039 You also mentioned cursors briefly before for large results. 266 00:13:31.799 --> 00:13:35.320 Right reef Cursors specifically, cursors are internal structures that let 267 00:13:35.320 --> 00:13:39.519 you fetch query results incrementally lazily, instead of the database 268 00:13:39.519 --> 00:13:42.080 computing the entire results set and sending it all at once, 269 00:13:42.120 --> 00:13:45.279 which could take ages and lots of memory. For huge queries, 270 00:13:45.519 --> 00:13:48.919 you get a pointer kinda yeah, a reference. Then your 271 00:13:48.960 --> 00:13:52.440 application can fetch rows from that cursor as needed. It's 272 00:13:52.480 --> 00:13:54.679 great if you only need the first few rows quickly, 273 00:13:54.960 --> 00:13:57.600 or if you want to return multiple independent result sets 274 00:13:57.600 --> 00:14:02.919 from a single function. Call PLPGCQ trigger functions Automated guardians no. 275 00:14:03.039 --> 00:14:05.639 Get back to triggers. These automatic guardians you said, they 276 00:14:05.679 --> 00:14:08.720 get old and NW records. How does that actually work 277 00:14:08.759 --> 00:14:09.360 in the code. 278 00:14:09.519 --> 00:14:12.519 So when you write a trigger function one that returns 279 00:14:12.559 --> 00:14:17.519 trigger postgresscool automatically makes special variables available inside it. Old 280 00:14:17.759 --> 00:14:20.000 is a record variable holding the row values before the 281 00:14:20.039 --> 00:14:23.559 operation for update and delete. New holds the values after 282 00:14:23.559 --> 00:14:26.639 the operation for insert and update. And you also get 283 00:14:26.720 --> 00:14:29.720 tg variables like TGP tells you if it was an insert, 284 00:14:29.799 --> 00:14:32.440 update or delete tg table name gives you the table 285 00:14:32.480 --> 00:14:34.559 name and so on. Lots of context, so. 286 00:14:34.559 --> 00:14:37.759 That audit trigger example, it uses those exactly. 287 00:14:37.840 --> 00:14:40.360 It would check TGOP. If it's an insert, it logs 288 00:14:40.360 --> 00:14:42.960 the new record maybe converted to Jason. If delete, it 289 00:14:43.000 --> 00:14:45.840 logs old. If update, it logs both old and new, 290 00:14:46.440 --> 00:14:51.159 along with the username from current user timestamp now table 291 00:14:51.240 --> 00:14:54.120 name and operation type creates that perfect audit law. 292 00:14:54.480 --> 00:14:56.399 Very cool, and you mentioned preventing actions. 293 00:14:56.840 --> 00:14:58.879 Yeah, you could have a simple trigger function that just 294 00:14:59.000 --> 00:15:02.080 raises an exception if t GOP is delete or truncate 295 00:15:02.159 --> 00:15:04.960 on certain critical tables, just stops the operation cold or 296 00:15:05.240 --> 00:15:08.360 more subtly. In a before trigger one that runs before 297 00:15:08.399 --> 00:15:11.360 the change is actually made, you can modify the new w. 298 00:15:11.480 --> 00:15:14.320 Record uh like the upper case name example. 299 00:15:14.080 --> 00:15:17.559 Precisely we're setting timestamps. A common pattern is a function 300 00:15:17.600 --> 00:15:20.039 that sets create at and created by only on insert, 301 00:15:20.399 --> 00:15:23.639 and updated at and updated by on update, ensuring those 302 00:15:23.679 --> 00:15:27.519 created fields are immutable after the initial insert. But again, 303 00:15:27.559 --> 00:15:31.320 the reminder triggers are powerful, but use them judiciously. For 304 00:15:31.399 --> 00:15:35.120 complex application logic, they can make debugging harder because the 305 00:15:35.159 --> 00:15:38.559 logic isn't always obvious in the main application code. Keep 306 00:15:38.559 --> 00:15:39.200 them focused. 307 00:15:39.240 --> 00:15:41.480 If you can seeing inside. 308 00:15:41.039 --> 00:15:44.159 That debugging point is key. If you've got complex logic 309 00:15:44.240 --> 00:15:47.039 hidden away and functions and triggers, how do you troubleshoot 310 00:15:47.039 --> 00:15:48.879 when things go wrong. It's not like setting a break 311 00:15:48.919 --> 00:15:50.919 point in your Java or Python code. 312 00:15:50.679 --> 00:15:53.559 Is it. It's definitely different. The simplest approach is what you 313 00:15:53.639 --> 00:15:56.720 might call manual debugging. Using raise notice, you just sprinkle 314 00:15:56.799 --> 00:16:02.440 raise notice reached point a with value my variable throughout 315 00:16:02.480 --> 00:16:05.519 your code. The messages show up in your client like 316 00:16:05.679 --> 00:16:07.799 psdes col like print statements. 317 00:16:07.919 --> 00:16:08.200 Basic. 318 00:16:08.360 --> 00:16:13.120 It's exactly simple, no installation needed. Great for checking values 319 00:16:13.120 --> 00:16:16.320 in regression test scripts. The downside is it can clutter 320 00:16:16.360 --> 00:16:19.919 your output if you leave them in. For actual error handling, 321 00:16:19.960 --> 00:16:22.919 you use reez exception. You can provide a custom error 322 00:16:23.000 --> 00:16:26.480 message and even a specific SQL state code which client 323 00:16:26.519 --> 00:16:30.519 applications can catch and react to programmatically. Okay, And if 324 00:16:30.559 --> 00:16:32.679 you want messages to go to the server's log file 325 00:16:32.759 --> 00:16:35.600 instead of the client maybe for background tasks or just 326 00:16:35.679 --> 00:16:37.240 less noise, you use. 327 00:16:37.159 --> 00:16:40.799 Raise luck right, So print debugging essentially, what about stepping 328 00:16:40.799 --> 00:16:41.440 through code? 329 00:16:41.720 --> 00:16:45.440 For that? You need the visual PLPG SQL debugger usually 330 00:16:45.440 --> 00:16:49.320 integrates with GUI tools like PG admin once it's set up, 331 00:16:49.320 --> 00:16:50.720 and the setup can be a bit of a pain. 332 00:16:50.799 --> 00:16:53.600 Fair warning is pretty powerful. You can set break points 333 00:16:53.600 --> 00:16:54.919 in your plpg sql. 334 00:16:54.720 --> 00:16:57.759 Code like in a normal id YEP, step. 335 00:16:57.440 --> 00:17:00.279 Through line by line, step into, step over, inspective values 336 00:17:00.320 --> 00:17:02.879 of variables, even change variable values on the fly to 337 00:17:02.919 --> 00:17:06.519 test different scenarios. You can also set global breakpoints like 338 00:17:06.759 --> 00:17:10.000 stop whenever any code calls. This specific function really handy 339 00:17:10.039 --> 00:17:11.839 for tracking down unexpected. 340 00:17:11.319 --> 00:17:12.759 Calls and the advantages. 341 00:17:13.240 --> 00:17:15.400 The big advantage over raised notice is it doesn't use 342 00:17:15.400 --> 00:17:18.440 server resources when you're not actively debugging, and you don't 343 00:17:18.440 --> 00:17:21.440 need to modify your function code with print statements. The 344 00:17:21.519 --> 00:17:26.119 disadvantage that installation process can be fiddly. Using unrestricted languages 345 00:17:26.720 --> 00:17:29.160 expanding horizons PL Python. 346 00:17:28.880 --> 00:17:32.640 Okay, let's shift gears beyond PLTG squel. You mentioned unrestricted 347 00:17:32.720 --> 00:17:37.160 or untrusted languages like pl python. That untrusted label, why 348 00:17:37.200 --> 00:17:38.599 is it there? What's the deal? Right? 349 00:17:38.640 --> 00:17:43.079 The untrusted language is pl python, PEEL, perileu, PLTCLU, peel, JAVAU. 350 00:17:43.279 --> 00:17:45.799 They're called that because they break out of the database sandbox. 351 00:17:46.359 --> 00:17:50.680 They can by default access the server's file system, make 352 00:17:50.799 --> 00:17:55.640 network connections, send signals to other processes, basically do anything 353 00:17:55.680 --> 00:17:58.240 the underlying language, Python, Perol, etc. Could do on the 354 00:17:58.279 --> 00:17:59.279 host operating system. 355 00:17:59.359 --> 00:18:01.079 Ahotential security risk. 356 00:18:01.119 --> 00:18:03.680 Huge potential security risk. That's why you absolutely should not 357 00:18:03.720 --> 00:18:06.839 grant permission to create functions in these languages to arbitrary 358 00:18:06.920 --> 00:18:09.799 database users. You don't fully trust. They could potentially read 359 00:18:09.839 --> 00:18:11.920 sensitive files or disrupt the server. 360 00:18:12.079 --> 00:18:13.279 Got it, so why use them? 361 00:18:13.319 --> 00:18:16.920 Power and convenience, Python, for example, has vast libraries for 362 00:18:16.960 --> 00:18:21.200 everything imaginable. It's often much easier and requires less boilerplate 363 00:18:21.240 --> 00:18:24.799 code to do complex text processing, interact with web services, 364 00:18:25.200 --> 00:18:27.680 or use external libraries and peel Python that it would 365 00:18:27.720 --> 00:18:32.359 be in plpg sql or especially C plus. Many developers 366 00:18:32.400 --> 00:18:35.440 are already comfortable with Python and peel Python handles the 367 00:18:35.559 --> 00:18:39.319 data type conversions between postcrescool types and Python types pretty 368 00:18:39.319 --> 00:18:40.160 transparently for you. 369 00:18:40.480 --> 00:18:42.720 Okay, so if I'm writing a pel Python function, how 370 00:18:42.720 --> 00:18:45.559 do I run SQL queries from within that Python code. 371 00:18:45.640 --> 00:18:49.519 Peel python provides a dedicated module usually accessed as plea high. 372 00:18:49.920 --> 00:18:53.039 It has functions like peelpi dot execute to run arbitrary 373 00:18:53.039 --> 00:18:56.440 SQL queries. It also has peelpi dot prepare, which lets 374 00:18:56.480 --> 00:18:59.440 you create a prepared statement plan. This is really important 375 00:18:59.440 --> 00:19:01.240 for performing. So if you're running the same kind of 376 00:19:01.279 --> 00:19:04.319 query multiple times, maybe just with different parameters. 377 00:19:03.880 --> 00:19:06.559 Prepare ones execute many times exactly. 378 00:19:06.880 --> 00:19:10.200 Preparing can be costly, so caching that plan makes subsequent 379 00:19:10.240 --> 00:19:13.000 executions much faster. And when you're writing a trigger function 380 00:19:13.000 --> 00:19:16.440 in peel Python, instead of old and new to W variables, 381 00:19:16.519 --> 00:19:19.559 you get a Python dictionary usually called TD, which contains 382 00:19:19.559 --> 00:19:22.599 all the trigger context td ol, td new, tdvn td, win, 383 00:19:22.680 --> 00:19:25.880 et cetera. Right, what about dynamic sqel building queris on 384 00:19:25.880 --> 00:19:29.799 the fly? Huge risk of seql injection There absolutely critical point. 385 00:19:29.920 --> 00:19:32.960 You should never just concatenate strings together with user input 386 00:19:33.119 --> 00:19:36.240 to build SQL queries in any language, including PL Python. 387 00:19:36.599 --> 00:19:40.359 Plupy provides functions specifically for safe quoting Peelpi dot quote 388 00:19:40.400 --> 00:19:43.359 item for table or column names, peelpi dot quote literal 389 00:19:43.480 --> 00:19:46.839 for string values, and peelpi dot quote nullable which handles 390 00:19:46.920 --> 00:19:51.200 nls correctly. Always use these when incorporating external data into 391 00:19:51.279 --> 00:19:54.480 dynamic queries. It's your primary defense. Good advice. How does 392 00:19:54.559 --> 00:19:57.160 error handling work within PL Python? Does it interact with 393 00:19:57.240 --> 00:20:01.240 database transactions? You use standard Python t dot accept blocks 394 00:20:01.480 --> 00:20:04.160 to catch errors that might occur within your Python code 395 00:20:04.480 --> 00:20:08.839 or from SQL queries executed via PLI. For transactional control 396 00:20:08.880 --> 00:20:13.480 within your function, pl Python provides PLPI dot subtransaction. This 397 00:20:13.559 --> 00:20:16.119 lets you create save points. You can wrap a block 398 00:20:16.119 --> 00:20:18.960 of code and a subtransaction context manager, and if an 399 00:20:18.960 --> 00:20:22.559 exception occurs inside that block, only the database changes made 400 00:20:22.640 --> 00:20:24.680 within that block are rolled back ah. 401 00:20:24.599 --> 00:20:26.559