Building and Testing Database extensions with Nix
Children, John
When building extensions for databases, it can become burdensome to make sure you build and deploy matching versions. This often involves changing the version number in multiple places or custom scripting, in particular when deploying extensions to other environments. In this talk I will cover two cases of building database extensions for PostgreSQL and Redis, as well as how to use Nix to manage Python environments to run tests against temporary instances of the database. Finally I will briefly discuss building docker images for the database with the extension installed. - Bio: John is a software engineer at InfoSum, where he works on the core components of their customer data platform. At InfoSum, he uses Nix to build several C projects as well as managing python dependencies, and has been using NixOS as his primary operating system for over a year. In his free time, John enjoys experimenting with Haskell, playing board games and going bouldering.
 We are ready for John who's gonna like I said be talking to us about building and testing database extensions with next Mike They go give him a round of applause, please Hello, thanks for having me This is kind of a presentation about how I've been using next at work It's not really recommendations. I guess more of a description of what I've done. Maybe things that I think could improve or from my side or from maybe things I could contribute to next packages and Just sort of a perspective from that angle I've been using next for Probably about a year and a half. I initially started it for this project which is I know described in the talk and Switched to using it as Nick so as to my main operating system of choice for work at home Generally pretty proud of it. Well, please with it So let's just begin the talk So I'm just going to describe I have a couple of postgres extensions and a redis extension that I build and maintain as part of my job and I'm just going to cover how that's done and as I said, it's more of a description of the description Maybe there are some good ideas. I don't know So background on database extensions particularly in the context of postgres They've been around since about nine point one exactly since nine point one And they're actually pretty flexible in what you can do postgres has always had The ability to for custom types and that was its original selling point, but extensions can load in complicated Functionalities that you might want to have in your database some examples Citus or post-gis timescale DB Citus is sort of a Distributed database product it The private company that does a SAS product based on it But you kind of a bit wild when it comes to changing what postgres actually does post-gis is for geographical data if you want to have a whole bunch of locations and Find out a query on which locations are in a certain area You can do really cool stuff with that timescale DB turns postgres into a time series database Which and I mean these are pretty radical departures from What you usually do, but Still within the postgres Framework and protocol when you use SQL so quite handy for a development perspective There is a caveat though if you're gonna write an extension They only work with the major release that you can parlay on some This is quite handy for making sure everything is compatible when you deploy it but So you want to update your database you need to make sure your extension is the correct version otherwise they won't load So we were developing MySQL UDF which works on a very simple kind of system and Basically one of my co-workers didn't really understand how I'd laid out the project We were deploying into a doc file and the doc file had the major release of particularly my SQL version he updated the build dependencies and It just meant that Yeah, if you forget to Match the versions it's just not gonna work and it's gonna be difficult to figure out exactly why it doesn't work until you actually try and use the image So he'd heard about nix so It was like well, why don't we try that? And we did So here's kind of how My workflow would work before I started using nix have to Install the dev package with a bunch of package matter or apt or something And still my other dependencies Build the project locally Build a Docker image with that output matching the right server version and then start it as a local container and Then run a white test against the service It's nice it's a lot of steps to remember how to do if you want to Maybe write a bash script that does all this it's nice Problem is if I want to intervene in something or do something differently. It's a bit more complicated If I want to run want to run benchmarks instead of the regular tests if I want to run a specific test or Yeah, if I want to disconnect to the database itself I Could just write a complicated bash script, but I'm really lazy so Can I just push it to my CI pipeline that I've got because we're hip and trendy Yeah, okay, but now It's got a download of entities wait for the extension to build wait for the Docker image to build wait for the Docker I'm just be able to just a registry It's got a pull the image again start as a service and then I've got to wait for my test fail to find out the science gone wrong This is just motivated by my laziness so Let's say I want to update which Major release I'm using for my base database I've got to update the documentation Gotta get a new Docker image got update my local packages and I've got update the remote build environment So okay digression into how Postgres works I'm just gonna talk briefly about how it stops you from loading modules are incompatible and that's called PG module magic Which I like to think of as a hashtag rather than macro So essentially as a magic struct that it uses you just add if and death PG module magic Add it to your build Sorry to your C extension And basically this just stops you from loading your extension dynamically, so this is what the magic struct looks like if you're interested so If you were wanted to build something usually For the postgres extension system, they provide PG excess Which is kind of just a big make file thing include that you can add and it will pull some module definitions and basically use that to Hide a lot of the complexity from you This is an example from the documentation for postgres Basically it's definition of this header and C file where you've got your code for your extension some version desk well for telling you how to load and Unload the module and then this tool called PG config which basically tells you how postgres was set up And then this is an example of what the sequel might look like So it's just a make file turns out next packages is very good at handling make files So it's actually pretty easy win If you want to add an extra dependency you can just use the LD flags Customizing the C flags a bit of a pain with PG config though. I'll show you why Essentially PG config will Have a bunch of C flags from when postgres is compiled and LD flags as well and she had them So when I am building an extension I'll Do this kind of bad practicing where I'm adding the LD flags here I have since writing this had a better idea about how I could do this but Essentially you just have to add a pre-configure phase and you can integrate directly with PG access because Seems to work pretty well that way So let's say you wanted to standard make derivation for postgres extension and you've got xx hash pretty straightforward everyone here is familiar with next. I don't know why I'm telling you this and Same pre-configure stuff So let's look at extensions in next packages There's actually quite a few already. I was not super familiar with where they were or How to discover them I knew post-gis was there but it's kind of in its own Libraries directory whereas these are in the postgres directory So I think maybe discoverability first thing like it could be discovered it could be improved a bit more But the Next OS postgres module can just have these added to it and it works Which is something I would want for my extension as well But I'm not really sure how you do that because how does postgres find my extension So yeah, how do we how do we use it? Well, I just tried to copy the way next up next was does it? if you look into The ways Nick s s loads extensions Basically just has a thing that checks if you've asked for any extra plugins to be enabled and if you have just add some into sub paths and sim links them into the Build output, but it needs to do a bit extra stuff with Post-build wrapping to Basically just set this next PG Libdear Which Yeah, it turns out is quite important Basically postgres will look in a couple of places for where your extensions might be and it's not It's not in the next door, so You need to give it a bit more advice on where that would be so that's I think is why next PG Libdear is set Which comes from a patch that's made to postgres? When it's built So this is where that comes from So that wrapper will allow you to provide the Libdear And to find your extensions So can we just set it to our build output from our extensions and We're good to go right well not quite because there are some extensions from postgres that you might want to use anyway So that's why you need to take the same approach and Wrap it as well Here I've pretty much copied the same thing Very similar idea. I'm just calling a package with some extensions in Just some more paths, but I've also Exposed in it to be in createGB. We'll see why in a moment because I'm using them to test But yeah, this just defines my custom postgres and I'm using a particular package from next packages I don't know if this is clear Okay There'll be plenty of time questions, hopefully so I Can build this package it's all very nice What do I do with it? I can Probably deploy it somewhere and test it but Can I just maybe start on my system and give it a go Yeah, okay, but then I need to remember to delete that database. I'm still kind of At the same place I was initially maybe I can add it to my script, but then I've got to manage a whole bunch of other things to control that Essentially what I found is There's some tools that basically provide you with sort of a femoral databases that It would basically just kick off a database process with a empty database sorry Empty tables and things and I'll just use that there's a few piping packages for example that would provide that so I'm using testing dot postgres, cool and testing common and Essentially they are Things I provide in next shell so the idea is to get a pretty good Developer ergonomics so they can just pull up next shell get all the dependencies for the project and then also Get what required for the running the test as well and all the tooling as well So yeah, I didn't really want to go into too much detail about the Python code that I've written but Generally having access to Python here is quite useful There's a really nice property based testing library called hypothesis which You can use to make sure that you're encoding and decoding works in the way you'd expect But generally it seems to be I don't know if many people we use as approach I haven't heard a huge amount about it I'm using temporary databases. There's a tool called PG temp. Oh wait PG temp, which is like a bash script that will run an instance of postgres temporarily And it candles some of the cleaning up a bit better than what I've been doing The There's also a package Pascal as well in called temp postgres TMP dash postgres Which will also be a very similar thing I don't know how Common it is to really need that kind of thing unless you're testing an extension Most of the time I think probably unit tests would cover this the issue is I'm kind of testing how my extension works with postgres Which is perhaps a unique problem space and Yeah, I just I I end up using pytest fixtures for controlling the state of the database which is not very clean and Can end up leaking databases Which is an interesting problem as well if you forget to or if your script breaks properly but With the shells I've written if you want to just jump in and Give run all the tests run a Copy of postgres run all the SQL commands against it that are required to test the extension It's essentially two commands, which I can just put in the read me. I don't need to describe how to apt install all the various packages that people usually require And I think as somewhere where we're not really familiar with functional programming and we don't really People haven't really gotten bored with the next train. I think Having something where it's just two commands is really helpful for getting people on board with it So yeah, cool, I've written this Expression where I can Just define a postgres Installation with the extensions enabled well in the extensions installed as well And what if I want to update it previously add to update doc file update? All these other things and now you just change the package from next packages There are a few problems with this Pgxs has regression tests Which are make install check phase Which require a running postgres? instance To actually test them they essentially this is a script of expected SQL output and some commands And There I haven't really thought of a good way to do that It'll be really nice if there is a Next packages or Make derivation phase for install check But it's not actually a build input. It's sort of a runtime dependency of running the tests and it would be nice if I could just have my single expression for my Extension and just be able to run The tests that way on that expression Sorry in a standalone way rather than at the moment I kind of bundle up all my extensions and test them together with postgres I have no idea how to do that. The only thing is I'm always overriding because I want to make sure that the postgres version I provide to my extensions Is the same as The one that I built with Kind of think maybe I should just be make my extensions unable to build without postgres and kind of curry the expression But I haven't seen that pattern very often in next packages, so I'm kind of wary that Briefly redis also has a module system too. It's not very interesting compared to postgres because redis modules are responsible for Loading initializing themselves. They provide all their information about themselves They don't have an extension system. You just write a make file and as long as it has a redis header You can load it as a shared object into redis I when I actually submitted the abstract for this talk, I kind of thought there'd be more to it, but Unfortunately not or fortunately something I say and I haven't seen any examples of redis modules in next packages I think maybe they're quite new I Maybe I don't know how to discover them. I certainly with postgres as well. There was an issue of trying to find the postgres extensions I Don't know where I would find a redis module if it existed. I kind of think maybe like Python packages or high school packages. There's maybe they could be a redis modules or postgres extensions Attributes it But I don't really know much about that So yeah similarly really simple derivation this is like basically the most simple derivation you can get You've got a redis extension And there's also testing redis like testing place for us so I can reuse all of my testing infrastructure for redis as well Which is nice small caveat When you have your shell next you need a small shell hook to export your redis modules path to your Expression full of redis modules Just thought maybe that might be interesting a little trick to share and Now we're going to talk about Docker. It's inevitable the People who I work with are very keen to everything turned up in the dock container So I have to somehow get my extensions to work with postgres in the dock container or with redis in the dock container So there are a few tools in next packages that will help me accomplish that goal Unfortunately With postgres I can't So doctools has pull image and build image. There's also now build layered image and the Place where it would want to put my extensions It wouldn't be able to be found with the postgres in the base image from just pulling the postgres image Like the official library image. I could wrap it with postgres from Next packages with the patch for lib PGD But it's kind of awkward to do there's like some nice shell scripts at the base library image has and I really want to commit them So the practical side of me currently just uses a doct file. I feel really bad about myself as a result But I hope to improve But I don't really know if there's a good way around doing this However for redis. Oh, that's right So I have this horrible hack where I use our sync to pull out all my extensions and then I put them in my dock container And I really wish I didn't have to do that So just thought I'd share my shame with you So redis on the other side and I mean if you look up doctools in the next OS Sorry next packages manual Redis is actually the canonical example which is quite handy. So I essentially just rip that off and If I want to load my module The lib thing is a bit contrived. That's just where my expression is installing it from my make file Probably I want to have a better place for it But yeah, it's simple enough you just add a let redis config in and then you're or build image definition and Yeah, you just load it up when your server starts easy enough So now you know It's still not great though Cool thing though all these tests I've written using temporary databases I can now run them against the images I've built with build image etc or Docker if I'm feeling bad and Yeah, so Essentially, I've shown you the arcing thing. I'd like to find a better way to do that the problem is I'm using GitLab CI And it is not super compatible with next So there's a few issues one one thing that really irritates me is the Docker sock thing so it wants Have privileged access to that socket so that it can upload things to My Docker registry, but if I built my image with build image I'm in my next runner and I don't have that access So I essentially have to copy the table of my image into another runner Which then loads the Docker image and then sends it to the registry Which is pretty annoying I Think well what I would like to maybe spend some time doing is build like a go tool that Uses a bunch of the Docker things to just upload images So it's just a tool that takes a table from build image and then You can just push it to an arbitrary registry if you have the credentials and I Don't know if something like that exists if someone knows something about that and they can come oh zimbo tem has my back Sorry, I'd tell me in a minute. Okay, Scorpio So that would be really handy and Yeah, hopefully from is all my images for Redis are just one layer Hopefully the build layered image would be helpful, but I haven't managed to integrate that yet into my pipeline Closing for I was reading how can use yesterday and I like to base all of my life decisions on what random people are saying on how can use Someone was saying it would be a good if Postgres had a package manager extensions and Maybe what I would like to see is the ability to manage extensions When when people think oh, I'm gonna manage my postgres extensions. They think of next packages Acknowledgement Justin will help me set up my slides and I said I'd give him an acknowledgement He has a cool repo showing you how to use next packages to build stuff with beamer I This is the point where I beg for twit follows And also this slides that you'll watch reading now are Built and in the repo cool any questions or corrections Are there any questions yes, there are Are you aware of this pull request by told police that Overholds the post-cascual infrastructure in next packages. I Am not that seems like something I should have known about prior gave this talk Yeah, no, I am aware that a lot of the postgres extensions are written by him I Don't know There's some things that they do differently that I'm not sure about so for example They won't use pgxs directly. They'll kind of copy the shared objects and sequel stars up Yeah, do you have the pull request number that I can? Okay, thank you very much anyone else No, then another round of applause for this interesting talk. Thank you interesting is a good word
 We are ready for John who's gonna like I said be talking to us about building and testing database extensions with next  Mike  They go give him a round of applause, please  Hello, thanks for having me  This is kind of a presentation about how I've been using next at work  It's not really recommendations. I guess more of a description of what I've done. Maybe things that I think could  improve or from my side or from maybe things I could contribute to next packages and  Just sort of a perspective from that angle  I've been using next for  Probably about a year and a half. I initially started it for this project  which is I know described in the talk and  Switched to using it as Nick so as to my main operating system of choice for work at home  Generally pretty proud of it. Well, please with it  So let's just begin the talk  So I'm just going to describe I have a couple of postgres extensions and a redis extension that I  build and maintain as part of my job and  I'm just going to cover how that's done and as I said, it's more of a description of the description  Maybe there are some good ideas. I don't know  So background on database extensions particularly in the context of postgres  They've been around since about nine point one exactly since nine point one  And they're actually pretty flexible in what you can do postgres has always had  The ability to for custom types and that was its original  selling point, but  extensions can load in  complicated  Functionalities that you might want to have in your database  some examples  Citus or post-gis timescale DB  Citus is sort of a  Distributed database product it  The private company that does a SAS product based on it  But you kind of a bit wild when it comes to changing what postgres actually does  post-gis is for geographical data  if you want to have a whole bunch of locations and  Find out a query on which locations are in a certain area  You can do really cool stuff with that timescale DB turns postgres into a time series database  Which and I mean these are pretty radical departures from  What you usually do, but  Still within the postgres  Framework and protocol when you use SQL so quite handy for a development perspective  There is a caveat though if you're gonna write an extension  They only work with the major release that you can parlay on some  This is quite handy for making sure everything is compatible when you deploy it but  So you want to update your database you need to make sure your extension is the correct version otherwise they won't load  So we were developing  MySQL UDF which works on a very simple kind of system and  Basically one of my co-workers didn't really understand how I'd laid out the project  We were deploying into a doc file and the doc file had the major release of particularly my SQL version  he updated the build dependencies and  It just meant that  Yeah, if you forget to  Match the versions it's just not gonna work and it's gonna be difficult to figure out exactly why it doesn't work  until you actually try and  use the image  So he'd heard about nix so  It was like well, why don't we try that?  And we did  So here's kind of how  My workflow would work before I started using nix  have to  Install the dev package with a bunch of package matter or apt or something  And still my other dependencies  Build the project locally  Build a Docker image with that output matching the right  server version and then start it as a local container and  Then run a white test against the service  It's nice it's a lot of steps to remember how to do if you want to  Maybe write a bash script that does all this it's nice  Problem is if I want to intervene in something or do something differently. It's a bit more complicated  If I want to run want to run benchmarks instead of the regular tests if I want to run a specific test or  Yeah, if I want to disconnect to the database itself I  Could just write a complicated bash script, but I'm really lazy so  Can I just push it to my CI pipeline that I've got because we're hip and trendy  Yeah, okay, but now  It's got a download of entities wait for the extension to build wait for the Docker image to build wait for the Docker  I'm just be able to just a registry  It's got a pull the image again start as a service and then I've got to wait for my test fail to find out the science gone wrong  This is just motivated by my laziness so  Let's say I want to update which  Major release I'm using for my base database  I've got to update the documentation  Gotta get a new Docker image got update my  local  packages and I've got update the remote build environment  So okay digression into how Postgres works  I'm just gonna talk briefly about how it stops you from loading modules are incompatible and that's called PG module magic  Which I like to think of as a hashtag rather than macro  So essentially as a magic struct that it uses you just add if and death  PG module magic  Add it to your build  Sorry to your C extension  And basically this just stops you from  loading your extension dynamically, so this is what the magic struct looks like if you're interested so  If you were wanted to build something usually  For the postgres extension system, they provide PG excess  Which is kind of just a big make file thing include that you can add  and it  will pull some module definitions and  basically use that to  Hide a lot of the complexity from you  This is an example from the documentation for postgres  Basically it's  definition of this header and C file where you've got your  code for your extension  some  version desk well for telling you how to load and  Unload the module and then this tool called PG config which basically tells you how postgres was set up  And then this is an example of what the sequel might look like  So it's just a make file turns out next packages is very good at handling make files  So it's actually pretty easy win  If you want to add an extra dependency you can just use the LD flags  Customizing the C flags a bit of a pain with PG config though. I'll show you why  Essentially PG config will  Have a bunch of C flags from when postgres is compiled and LD flags as well and she had them  So when I am building an extension I'll  Do this kind of bad practicing where I'm adding the LD flags here  I have since writing this had a better idea about how I could do this but  Essentially you just have to add a pre-configure phase and you can integrate directly with PG access because  Seems to work pretty well that way  So let's say you wanted to  standard make derivation for  postgres extension and you've got  xx hash pretty straightforward everyone here is familiar with next. I don't know why I'm telling you this  and  Same pre-configure stuff  So let's look at  extensions in next packages  There's actually quite a few already. I was not super familiar with where they were or  How to discover them I knew post-gis was there but it's kind of in its own  Libraries directory whereas these are in the postgres directory  So I think maybe discoverability first thing like it could be discovered it could be improved a bit more  But the  Next OS postgres module can just have these added to it and it works  Which is something I would want for my extension as well  But I'm not really sure how you do that because how does postgres find my extension  So yeah, how do we how do we use it? Well, I just tried to copy the way next up next was does it?  if you look into  The ways Nick s s loads extensions  Basically just has a thing that checks if you've asked for any extra plugins to be enabled and  if you have just add some into sub paths and sim links them into the  Build output, but it needs to do a bit extra stuff with  Post-build wrapping to  Basically just set this next PG Libdear  Which  Yeah, it turns out is quite important  Basically postgres will look in a couple of places for where your extensions might be and it's not  It's not in the next door, so  You need to give it a bit more advice on where that would be so that's I think is why next PG Libdear is set  Which comes from a patch that's made to postgres?  When it's built  So this is where that comes from  So that wrapper will allow you to  provide the Libdear  And to find your extensions  So can we just set it to our build output from our extensions and  We're good to go right well not quite because there are some extensions from postgres that you might want to use anyway  So that's why you need to take the same approach and  Wrap it as well  Here I've pretty much copied the same thing  Very similar idea. I'm just calling a package with some extensions in  Just some more paths, but I've also  Exposed in it to be in createGB. We'll see why in a moment because I'm using them to test  But yeah, this just defines my custom postgres and I'm using a particular package from next packages  I don't know if this is clear  Okay  There'll be plenty of time questions, hopefully  so I  Can build this package it's all very nice  What do I do with it? I can  Probably deploy it somewhere and test it but  Can I just maybe start on my system and give it a go  Yeah, okay, but then I need to remember to delete that database. I'm still kind of  At the same place I was initially maybe I can add it to my script, but then I've got to manage a whole bunch of other things to  control that  Essentially what I found is  There's some tools that basically provide you with sort of a femoral databases that  It would basically just kick off a database process with a empty database  sorry  Empty tables and things  and  I'll just use that there's a few piping packages for example that would provide that so I'm using  testing dot postgres, cool  and  testing common and  Essentially they are  Things I provide in next shell so the idea is to get a pretty good  Developer ergonomics so they can just pull up next shell get all the dependencies for the project and then also  Get what required for the running the test as well and all the tooling as well  So yeah, I didn't really want to go into too much detail about the Python code that I've written but  Generally having access to Python here is quite useful  There's a really nice property based testing library called hypothesis which  You can use to make sure that you're encoding and decoding works in the way you'd expect  But generally it seems to be I don't know if many people we use as approach I haven't heard a huge amount about it  I'm using temporary databases. There's a tool called PG temp. Oh wait  PG temp, which is like a bash script that will run an instance of postgres  temporarily  And it candles some of the cleaning up a bit better than what I've been doing  The  There's also a package Pascal as well in called temp postgres TMP dash postgres  Which will also be a very similar thing  I don't know how  Common it is to really need that kind of thing unless you're testing an extension  Most of the time I think probably unit tests would cover this the issue is I'm kind of testing how my extension works with postgres  Which is perhaps a unique problem space  and  Yeah, I just I  I end up using pytest fixtures for controlling the state of the database  which is not very clean and  Can end up leaking databases  Which is an interesting problem as well if you forget to or if your script breaks properly  but  With the shells I've written  if you want to just jump in and  Give run all the tests run a  Copy of postgres run all the SQL commands against it that are required to test the extension  It's essentially two commands, which I can just put in the read me. I don't need to describe how  to  apt install all the various packages that people usually require  And I think as somewhere where we're not really familiar with functional programming and we don't really  People haven't really gotten bored with the next train. I think  Having something where it's just two commands is really helpful for getting people on board with it  So yeah, cool, I've written this  Expression where I can  Just define a postgres  Installation with the extensions enabled well in the extensions installed as well  And what if I want to update it previously add to update doc file update?  All these other things and now you just change the package from next packages  There are a few problems with this  Pgxs has regression tests  Which are make install check phase  Which require a running postgres?  instance  To actually test them they essentially this is a script of expected SQL output and some commands  And  There I haven't really thought of a good way to do that  It'll be really nice if there is a  Next packages  or  Make derivation phase for install check  But it's not actually a build input. It's sort of a runtime dependency of running the tests and it would be nice if I could just have  my single  expression for my  Extension and just be able to run  The tests that way on that expression  Sorry in a standalone way rather than at the moment I kind of bundle up all my extensions and test them together with postgres  I have no idea how to do that. The only thing is I'm always overriding because I want to make sure that the postgres version  I provide to my extensions  Is the same as  The one that I built with  Kind of think maybe I should just be make my extensions unable to build without postgres and kind of curry the expression  But I haven't seen that pattern very often in next packages, so I'm kind of wary that  Briefly redis also has a module system too. It's not very interesting compared to postgres because redis modules are responsible for  Loading initializing themselves. They provide all their information about themselves  They don't have an extension system. You just write a make file and as long as it has a redis header  You can load it as a shared object into redis  I when I actually submitted the abstract for this talk, I kind of thought there'd be more to it, but  Unfortunately not or fortunately something I say and I haven't seen any examples of redis modules in next packages  I think maybe they're quite new I  Maybe I don't know how to discover them. I certainly with postgres as well. There was an issue of trying to find the postgres extensions  I  Don't know where I would find a redis module if it existed. I kind of think maybe like  Python packages or high school packages. There's maybe they could be a redis modules or postgres extensions  Attributes it  But I don't really know much about that  So yeah similarly really simple derivation this is like basically the most simple derivation you can get  You've got a redis extension  And there's also testing redis like testing place for us so I can reuse all of my testing infrastructure for redis as well  Which is nice  small caveat  When you have your shell next you need a small shell hook to export your redis modules path to your  Expression full of redis modules  Just thought maybe that might be interesting a little trick to share  and  Now we're going to talk about Docker. It's inevitable  the  People who I work with are very keen to everything turned up in the dock container  So I have to somehow get my extensions to work with postgres in the dock container or with redis in the dock container  So there are a few tools in next packages that will help me accomplish that goal  Unfortunately  With postgres I can't  So doctools has pull image and build image. There's also now build layered image and the  Place where it would want to put my extensions  It wouldn't be able to be found with the postgres in the base image from just pulling the postgres image  Like the official library image. I could wrap it with postgres from  Next packages with the patch for lib  PGD  But it's kind of awkward to do there's like some nice shell scripts at the base library image has and I really want to commit them  So the practical side of me currently just uses a doct file. I feel really bad about myself as a result  But I hope to improve  But I don't really know if there's a good way around doing this  However for redis. Oh, that's right  So I have this horrible hack where I use our sync to pull out all my extensions and then I put them in my dock container  And I really wish I didn't have to do that  So just thought I'd share my shame with you  So redis on the other side and I mean if you look up doctools in the next OS  Sorry next packages manual  Redis is actually the canonical example which is quite handy. So I essentially just rip that off and  If I want to load my module  The lib thing is a bit contrived. That's just where my expression is installing it from my make file  Probably I want to have a better place for it  But yeah, it's simple enough you just add a let redis config in and then you're  or  build image definition and  Yeah, you just load it up when your server starts easy enough  So now you know  It's still not great though  Cool thing though all these tests I've written using  temporary databases I can now run them against the images I've built with  build image etc or Docker if I'm feeling bad and  Yeah, so  Essentially, I've shown you the arcing thing. I'd like to find a better way to do that the problem is I'm using  GitLab CI  And it is not super compatible with next  So there's a few issues one one thing that really irritates me is  the Docker sock thing so it wants  Have privileged access to that socket so that it can upload things to  My Docker registry, but if I built my image with build image  I'm in my next runner and I don't have that access  So I essentially have to copy the table of my image into another runner  Which then loads the Docker image and then sends it to the registry  Which is pretty annoying I  Think well what I would like to maybe spend some time doing is  build like a go tool that  Uses a bunch of the Docker things to just upload images  So it's just a tool that takes a table from build image and then  You can just push it to an arbitrary registry if you have the credentials and I  Don't know if something like that exists if someone knows something about that and they can come oh zimbo tem has my back  Sorry, I'd tell me in a minute. Okay, Scorpio  So that would be really handy  and  Yeah, hopefully from is all my images for Redis are just one layer  Hopefully the build layered image would be helpful, but I haven't managed to integrate that yet into my pipeline  Closing for I was reading how can use yesterday and I like to base all of my life decisions on what random people are saying on how can use  Someone was saying it would be a good if  Postgres had a package manager extensions and  Maybe what I would like to see is the ability to manage extensions  When when people think oh, I'm gonna manage my postgres extensions. They think of next packages  Acknowledgement Justin will help me set up my slides and I said I'd give him an acknowledgement  He has a cool repo showing you how to use next packages to build stuff with beamer  I  This is the point where I beg for twit follows  And also this slides that you'll watch reading now are  Built and in the repo cool any questions or corrections  Are there any questions yes, there are  Are you aware of this pull request by told police that  Overholds the post-cascual infrastructure in next packages. I  Am not that seems like something I should have known about prior gave this talk  Yeah, no, I am aware that a lot of the postgres extensions are written by him I  Don't know  There's some things that they do differently that I'm not sure about so for example  They won't use pgxs directly. They'll kind of copy the shared objects and sequel stars up  Yeah, do you have the pull request number that I can?  Okay, thank you very much anyone else  No, then another round of applause for this interesting talk. Thank you interesting is a good word
John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_0_227.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_228_5231.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_5232_6131.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_6132_6611.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_6612_7463.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_7464_7511.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_7512_7787.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_7788_8183.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_8184_8543.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_8544_9203.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_9204_9395.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_9396_10235.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_10236_14855.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_14856_16607.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_16608_18383.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_18384_19247.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_19248_22547.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_22548_23747.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_23748_25043.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_25044_28295.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_28296_28343.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_28344_29531.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_29532_29891.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_29892_30035.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_30036_30131.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_30132_30515.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_30516_32039.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_32040_34187.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_34188_35231.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_35232_35759.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_35760_35807.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_35808_36695.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_36696_37679.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_37680_38015.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_38016_38531.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_38532_39659.png John_Children_-_Building_and_Testing_Database_extensions_with_Nix_4xM802RPFVE_39660_39727.png
We are ready for John who's gonna like I said be talking to us about building and testing database extensions with next Mike They go give him a round of applause, please Hello, thanks for having me This is kind of a presentation about how I've been using next at work It's not really recommendations. I guess more of a description of what I've done. Maybe things that I think could improve or from my side or from maybe things I could contribute to next packages and Just sort of a perspective from that angle I've been using next for Probably about a year and a half. I initially started it for this project which is I know described in the talk and Switched to using it as Nick so as to my main operating system of choice for work at home Generally pretty proud of it. Well, please with it So let's just begin the talk So I'm just going to describe I have a couple of postgres extensions and a redis extension that I build and maintain as part of my job and I'm just going to cover how that's done and as I said, it's more of a description of the description Maybe there are some good ideas. I don't know So background on database extensions particularly in the context of postgres They've been around since about nine point one exactly since nine point one And they're actually pretty flexible in what you can do postgres has always had The ability to for custom types and that was its original selling point, but extensions can load in complicated Functionalities that you might want to have in your database some examples Citus or post-gis timescale DB Citus is sort of a Distributed database product it The private company that does a SAS product based on it But you kind of a bit wild when it comes to changing what postgres actually does post-gis is for geographical data if you want to have a whole bunch of locations and Find out a query on which locations are in a certain area You can do really cool stuff with that timescale DB turns postgres into a time series database Which and I mean these are pretty radical departures from What you usually do, but Still within the postgres Framework and protocol when you use SQL so quite handy for a development perspective There is a caveat though if you're gonna write an extension They only work with the major release that you can parlay on some This is quite handy for making sure everything is compatible when you deploy it but So you want to update your database you need to make sure your extension is the correct version otherwise they won't load So we were developing MySQL UDF which works on a very simple kind of system and Basically one of my co-workers didn't really understand how I'd laid out the project We were deploying into a doc file and the doc file had the major release of particularly my SQL version he updated the build dependencies and It just meant that Yeah, if you forget to Match the versions it's just not gonna work and it's gonna be difficult to figure out exactly why it doesn't work until you actually try and use the image So he'd heard about nix so It was like well, why don't we try that? And we did So here's kind of how My workflow would work before I started using nix have to Install the dev package with a bunch of package matter or apt or something And still my other dependencies Build the project locally Build a Docker image with that output matching the right server version and then start it as a local container and Then run a white test against the service It's nice it's a lot of steps to remember how to do if you want to Maybe write a bash script that does all this it's nice Problem is if I want to intervene in something or do something differently. It's a bit more complicated If I want to run want to run benchmarks instead of the regular tests if I want to run a specific test or Yeah, if I want to disconnect to the database itself I Could just write a complicated bash script, but I'm really lazy so Can I just push it to my CI pipeline that I've got because we're hip and trendy Yeah, okay, but now It's got a download of entities wait for the extension to build wait for the Docker image to build wait for the Docker I'm just be able to just a registry It's got a pull the image again start as a service and then I've got to wait for my test fail to find out the science gone wrong This is just motivated by my laziness so Let's say I want to update which Major release I'm using for my base database I've got to update the documentation Gotta get a new Docker image got update my local packages and I've got update the remote build environment So okay digression into how Postgres works I'm just gonna talk briefly about how it stops you from loading modules are incompatible and that's called PG module magic Which I like to think of as a hashtag rather than macro So essentially as a magic struct that it uses you just add if and death PG module magic Add it to your build Sorry to your C extension And basically this just stops you from loading your extension dynamically, so this is what the magic struct looks like if you're interested so If you were wanted to build something usually For the postgres extension system, they provide PG excess Which is kind of just a big make file thing include that you can add and it will pull some module definitions and basically use that to Hide a lot of the complexity from you This is an example from the documentation for postgres Basically it's definition of this header and C file where you've got your code for your extension some version desk well for telling you how to load and Unload the module and then this tool called PG config which basically tells you how postgres was set up And then this is an example of what the sequel might look like So it's just a make file turns out next packages is very good at handling make files So it's actually pretty easy win If you want to add an extra dependency you can just use the LD flags Customizing the C flags a bit of a pain with PG config though. I'll show you why Essentially PG config will Have a bunch of C flags from when postgres is compiled and LD flags as well and she had them So when I am building an extension I'll Do this kind of bad practicing where I'm adding the LD flags here I have since writing this had a better idea about how I could do this but Essentially you just have to add a pre-configure phase and you can integrate directly with PG access because Seems to work pretty well that way So let's say you wanted to standard make derivation for postgres extension and you've got xx hash pretty straightforward everyone here is familiar with next. I don't know why I'm telling you this and Same pre-configure stuff So let's look at extensions in next packages There's actually quite a few already. I was not super familiar with where they were or How to discover them I knew post-gis was there but it's kind of in its own Libraries directory whereas these are in the postgres directory So I think maybe discoverability first thing like it could be discovered it could be improved a bit more But the Next OS postgres module can just have these added to it and it works Which is something I would want for my extension as well But I'm not really sure how you do that because how does postgres find my extension So yeah, how do we how do we use it? Well, I just tried to copy the way next up next was does it? if you look into The ways Nick s s loads extensions Basically just has a thing that checks if you've asked for any extra plugins to be enabled and if you have just add some into sub paths and sim links them into the Build output, but it needs to do a bit extra stuff with Post-build wrapping to Basically just set this next PG Libdear Which Yeah, it turns out is quite important Basically postgres will look in a couple of places for where your extensions might be and it's not It's not in the next door, so You need to give it a bit more advice on where that would be so that's I think is why next PG Libdear is set Which comes from a patch that's made to postgres? When it's built So this is where that comes from So that wrapper will allow you to provide the Libdear And to find your extensions So can we just set it to our build output from our extensions and We're good to go right well not quite because there are some extensions from postgres that you might want to use anyway So that's why you need to take the same approach and Wrap it as well Here I've pretty much copied the same thing Very similar idea. I'm just calling a package with some extensions in Just some more paths, but I've also Exposed in it to be in createGB. We'll see why in a moment because I'm using them to test But yeah, this just defines my custom postgres and I'm using a particular package from next packages I don't know if this is clear Okay There'll be plenty of time questions, hopefully so I Can build this package it's all very nice What do I do with it? I can Probably deploy it somewhere and test it but Can I just maybe start on my system and give it a go Yeah, okay, but then I need to remember to delete that database. I'm still kind of At the same place I was initially maybe I can add it to my script, but then I've got to manage a whole bunch of other things to control that Essentially what I found is There's some tools that basically provide you with sort of a femoral databases that It would basically just kick off a database process with a empty database sorry Empty tables and things and I'll just use that there's a few piping packages for example that would provide that so I'm using testing dot postgres, cool and testing common and Essentially they are Things I provide in next shell so the idea is to get a pretty good Developer ergonomics so they can just pull up next shell get all the dependencies for the project and then also Get what required for the running the test as well and all the tooling as well So yeah, I didn't really want to go into too much detail about the Python code that I've written but Generally having access to Python here is quite useful There's a really nice property based testing library called hypothesis which You can use to make sure that you're encoding and decoding works in the way you'd expect But generally it seems to be I don't know if many people we use as approach I haven't heard a huge amount about it I'm using temporary databases. There's a tool called PG temp. Oh wait PG temp, which is like a bash script that will run an instance of postgres temporarily And it candles some of the cleaning up a bit better than what I've been doing The There's also a package Pascal as well in called temp postgres TMP dash postgres Which will also be a very similar thing I don't know how Common it is to really need that kind of thing unless you're testing an extension Most of the time I think probably unit tests would cover this the issue is I'm kind of testing how my extension works with postgres Which is perhaps a unique problem space and Yeah, I just I I end up using pytest fixtures for controlling the state of the database which is not very clean and Can end up leaking databases Which is an interesting problem as well if you forget to or if your script breaks properly but With the shells I've written if you want to just jump in and Give run all the tests run a Copy of postgres run all the SQL commands against it that are required to test the extension It's essentially two commands, which I can just put in the read me. I don't need to describe how to apt install all the various packages that people usually require And I think as somewhere where we're not really familiar with functional programming and we don't really People haven't really gotten bored with the next train. I think Having something where it's just two commands is really helpful for getting people on board with it So yeah, cool, I've written this Expression where I can Just define a postgres Installation with the extensions enabled well in the extensions installed as well And what if I want to update it previously add to update doc file update? All these other things and now you just change the package from next packages There are a few problems with this Pgxs has regression tests Which are make install check phase Which require a running postgres? instance To actually test them they essentially this is a script of expected SQL output and some commands And There I haven't really thought of a good way to do that It'll be really nice if there is a Next packages or Make derivation phase for install check But it's not actually a build input. It's sort of a runtime dependency of running the tests and it would be nice if I could just have my single expression for my Extension and just be able to run The tests that way on that expression Sorry in a standalone way rather than at the moment I kind of bundle up all my extensions and test them together with postgres I have no idea how to do that. The only thing is I'm always overriding because I want to make sure that the postgres version I provide to my extensions Is the same as The one that I built with Kind of think maybe I should just be make my extensions unable to build without postgres and kind of curry the expression But I haven't seen that pattern very often in next packages, so I'm kind of wary that Briefly redis also has a module system too. It's not very interesting compared to postgres because redis modules are responsible for Loading initializing themselves. They provide all their information about themselves They don't have an extension system. You just write a make file and as long as it has a redis header You can load it as a shared object into redis I when I actually submitted the abstract for this talk, I kind of thought there'd be more to it, but Unfortunately not or fortunately something I say and I haven't seen any examples of redis modules in next packages I think maybe they're quite new I Maybe I don't know how to discover them. I certainly with postgres as well. There was an issue of trying to find the postgres extensions I Don't know where I would find a redis module if it existed. I kind of think maybe like Python packages or high school packages. There's maybe they could be a redis modules or postgres extensions Attributes it But I don't really know much about that So yeah similarly really simple derivation this is like basically the most simple derivation you can get You've got a redis extension And there's also testing redis like testing place for us so I can reuse all of my testing infrastructure for redis as well Which is nice small caveat When you have your shell next you need a small shell hook to export your redis modules path to your Expression full of redis modules Just thought maybe that might be interesting a little trick to share and Now we're going to talk about Docker. It's inevitable the People who I work with are very keen to everything turned up in the dock container So I have to somehow get my extensions to work with postgres in the dock container or with redis in the dock container So there are a few tools in next packages that will help me accomplish that goal Unfortunately With postgres I can't So doctools has pull image and build image. There's also now build layered image and the Place where it would want to put my extensions It wouldn't be able to be found with the postgres in the base image from just pulling the postgres image Like the official library image. I could wrap it with postgres from Next packages with the patch for lib PGD But it's kind of awkward to do there's like some nice shell scripts at the base library image has and I really want to commit them So the practical side of me currently just uses a doct file. I feel really bad about myself as a result But I hope to improve But I don't really know if there's a good way around doing this However for redis. Oh, that's right So I have this horrible hack where I use our sync to pull out all my extensions and then I put them in my dock container And I really wish I didn't have to do that So just thought I'd share my shame with you So redis on the other side and I mean if you look up doctools in the next OS Sorry next packages manual Redis is actually the canonical example which is quite handy. So I essentially just rip that off and If I want to load my module The lib thing is a bit contrived. That's just where my expression is installing it from my make file Probably I want to have a better place for it But yeah, it's simple enough you just add a let redis config in and then you're or build image definition and Yeah, you just load it up when your server starts easy enough So now you know It's still not great though Cool thing though all these tests I've written using temporary databases I can now run them against the images I've built with build image etc or Docker if I'm feeling bad and Yeah, so Essentially, I've shown you the arcing thing. I'd like to find a better way to do that the problem is I'm using GitLab CI And it is not super compatible with next So there's a few issues one one thing that really irritates me is the Docker sock thing so it wants Have privileged access to that socket so that it can upload things to My Docker registry, but if I built my image with build image I'm in my next runner and I don't have that access So I essentially have to copy the table of my image into another runner Which then loads the Docker image and then sends it to the registry Which is pretty annoying I Think well what I would like to maybe spend some time doing is build like a go tool that Uses a bunch of the Docker things to just upload images So it's just a tool that takes a table from build image and then You can just push it to an arbitrary registry if you have the credentials and I Don't know if something like that exists if someone knows something about that and they can come oh zimbo tem has my back Sorry, I'd tell me in a minute. Okay, Scorpio So that would be really handy and Yeah, hopefully from is all my images for Redis are just one layer Hopefully the build layered image would be helpful, but I haven't managed to integrate that yet into my pipeline Closing for I was reading how can use yesterday and I like to base all of my life decisions on what random people are saying on how can use Someone was saying it would be a good if Postgres had a package manager extensions and Maybe what I would like to see is the ability to manage extensions When when people think oh, I'm gonna manage my postgres extensions. They think of next packages Acknowledgement Justin will help me set up my slides and I said I'd give him an acknowledgement He has a cool repo showing you how to use next packages to build stuff with beamer I This is the point where I beg for twit follows And also this slides that you'll watch reading now are Built and in the repo cool any questions or corrections Are there any questions yes, there are Are you aware of this pull request by told police that Overholds the post-cascual infrastructure in next packages. I Am not that seems like something I should have known about prior gave this talk Yeah, no, I am aware that a lot of the postgres extensions are written by him I Don't know There's some things that they do differently that I'm not sure about so for example They won't use pgxs directly. They'll kind of copy the shared objects and sequel stars up Yeah, do you have the pull request number that I can? Okay, thank you very much anyone else No, then another round of applause for this interesting talk. Thank you interesting is a good word
how I've been using next at work It's not really next for Probably about a year and a half. I it as Nick so as to my main operating system of job and I'm just going to cover how that's done don't know So background on database extensions to make sure your extension is the correct SQL version he updated the build dependencies and the versions it's just not gonna work and it's the extension to build wait for the Docker image how Postgres works I'm just gonna talk briefly config will Have a bunch of C flags from when in a couple of places for where your extensions this just defines my custom postgres and I'm using a particular package from next packages I got to manage a whole bunch of other things to Most of the time I think probably unit tests interesting problem as well if you forget to or abstract for this talk, I kind of thought there'd written using temporary databases I can now run images for Redis are just one layer Hopefully the how to use next packages to build stuff with