Beyond JDBC
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Glaesemann, Michael
JDBC is a popular standard database client API for Java. As such, it describes a common denominator for implementations, regardless of any specific backend. Creating a custom driver using the PostgreSQL V3 Protocol can provide access to the rich features PostgreSQL provides beyond those specified by JDBC. This talk will cover one such implementation for Clojure, a Lisp hosted on the JVM. JDBC is a popular standard database client API for Java. It's great for portability across database server backends, and the PostgreSQL JDBC Driver is a solid implementation. As with many standards, there's an expected common denominator for behavior. There are circumstances, however, when that common denominator limits what's actually available from a specific underlying system. When you want to leverage the full power of Postgres on the JVM, sometimes you need to go beyond what's available with JDBC. The solution? Implement your own driver! Use the PostgreSQL V3 Protocol to take full advantage of everything PostgreSQL has to offer. In my case, I want a PostgreSQL interface for Clojure-a Lisp hosted on the JVM (as well as compiling to JavaScript)-that is idiomatic to the language. Clojure already has a great wrapper for JDBC, but supporting features such as Clojure's sequence abstraction and data types can be better served by a dedicated driver. In this talk I will cover differences between Java and Clojure and the limitations of the JDBC driver which motivate a new driver for Clojure. In addition, I'll provide an overview of the implementation of the V3 protocol in Clojure, the use of the new driver, and benchmarks comparing it to the current PGJDBC driver.
 So some signposts along our way. First a brief introduction to closure and then hopefully provide some motivation on why I would actually want to do something without JDBC. And then take a look at Zo, the client that I've been working on. If you have any questions as we go by, I will be moving relatively fast, but feel free to pipe up or raise your hand and I'll look to answer the questions as we go. So closure, how many people have had any exposure to closure? Or using it or have played around with it or at least have heard of it? Great. Well, let's just jump right in and take a look at some closure. Here are some, in particular, these are some closure data structures. The first is a vector of three strings and the next one is a map. And we're using keywords for the keys of this map. Though this is very common for closure developers to use, but keys and objects and values in a map can be of any type. For example, the next map we have here, the keys are vectors. And here I'm using them to represent schema-qualified type names and they're mapping them to the OID from the PG type table. And how might a closure developer actually use these data structures? Well, here I have a vector that I'm going to represent as the series of Postgres conference talks. Each of those talks is represented as a map. Of course, there are so many talks and there's a lot of data here. I've elided most of those. And little fun fact, commas are considered white space enclosure. And there's a convention to use three commas like this to show e-leasion and still be syntactically valid. That's the best thing to have. And even this, for the examples I've got, the description is really long. So I'm just going to omit that in my summaries. But imagine that it's still there. So one of the features of Postgres that I'm particularly interested in right now is logical decoding and logical replication. So I want to take a look at the talks that have logical in their description. So here I'm creating a simple function that takes one of those talk maps, takes out the description and does a reget spine for logical in that description. It'll return a truthy value if it finds the logical there and falsey otherwise. So I can filter the talks using this predicate function. And you can see that we've got five talks at PGCon that have logical in the description. And of course, these are in just some random order. So I want to put them in order so it makes it easier for me to arrange my schedule. So I can pass the output of the filter function as an argument to sort by. And this sorts them by the at value of those maps. And as is often the case, once you start drilling into the data, you realize, oh, there's even more I want to do. So I can keep on passing the result of one function call as an argument to another. But that can get really tedious to keep on both to write and to read, trying to go work backwards. So closure provides some alternatives in how to write that, for example, using threading. Here talks is being passed to filter logical, which is being passed to this sort by. And if I stopped right there, that's exactly the same as the first expression above. But again, what I want to figure out, well, when do I need to leave to make sure I attend the very first talk? So I pass the results of that to first. It gives me the first talk. Then I grab the at value of that. Then I have a function that calculates my travel time and adds that to this. All of this other than the logical function and the add travel time, those are parts of the core closure functionality. Closure makes it really easy to do a lot of data processing like this. And I like my type checking, like I like my superheroes, dynamic and strong. Closure is dynamically typed. I don't need to identify my types in the code. And types are not checked at compile time. But closure is strongly typed. Types are checked at runtime. And many of the features of closure actually does dispatch on the types of the values. That said, closure developers will often use these basic data structures rather than instantiate specific classes to group types together. They'll often use maps with keys identifying fields rather than creating an instance of something. And this is actually kind of a superpower itself. Bruce Lee famously said, I fear not the man who practices 10,000 kicks once. I fear the man who practices one kick 10,000 times. So as you become very familiar with a few core data structures, a closure developer very easily is able to process lots of different data in many different ways. Alan Perlis in his epigrams of programming said something similar when he said that it's better to have 100 functions that operate on a single data structure than 10 functions that operate on 10 data structures. Closure also makes a very clean distinction between value, identity and state. This is something that should be really familiar to us as database developers. We refer to tables, for example, by name. Even though the number of rows in that table or the values of the columns of that table may change over time, we know what we mean when we refer to the user's table, for example. And as I mentioned, the number of rows and the values in the rows can change. That's the state changing over time. If you take a query on that table, you get a consistent snapshot of the query when you execute it. Even if the table gets changed after that query is made, the result you got stays the same. That's a value from that snapshot. It's really important in Postgres, of course. There's a lot of work being done to make sure that those snapshots you get are consistent and efficient. And of course, Postgres uses MVCC to do that, multiversion concurrency control. Closure also uses multiversion concurrency control to make sure that you have consistent snapshots of data that may be shared between different parts of your application. The implementation is quite a bit different. In particular, Closure uses something called persistent data structures. And Chris Okezaki's 1996 PhD thesis, purely functional data structures, is a really interesting read and was really influential in making these, popularizing these in the use of functional programming. Phil Bagwell, while at the Federal Institute of Technology in Luzon, Switzerland, built upon that work and created something called array mapped, array, hashed array map trees or some combination of that. And those features there provide the implementation of persistent data structures in Closure. And this actually is one of the reasons that I really like Closure. And one of the things that's appealed to me about with Postgres over the years that I've been using it, both Closure and Postgres are built on these really rigorous academic foundations, but they make a very practical and versatile tool. If you look at Postgres' history, if you're reading the source code, there are references to Knuth all over the place. Also, you think of Kevin Grittner working with Dan Portz at MIT to implement serialized snapshot isolation. All of this rigor gives me the confidence that the tools are going to behave consistently and over time. That gives me a lot of confidence. And Closure gives me that as well. Another feature that's very practical with Closure is its Java interop. Closure is hosted on the JVM, but it doesn't try to abstract away the underlying platform. It makes it really simple to use Java directly. For example, here I've got a Java Util date instance constructor. I'm assigning that instance to the bar now. And any of the methods that you might expect to work on a Java Util instance, for example, at a time, will work just as expected. That's the number of milliseconds since Unik epoch. Similarly, class methods or constants, all of this is immediately available in Closure. This gave Closure a fantastic advantage as soon as it was released. You didn't need to wait for all of the libraries that were already available for Java were instantly available to Closure as well. For example, if you were doing natural language processing, you didn't need to wait for a native Closure library. You could just use Stanford Core NLP. And if you want to use a SQL database, you can use JDBC right out of the box. You want to use Postgres. We've got a great community driver, the PGJDBC driver. So let's take a quick look at what JDBC looks like. First in Java. Here, I'm importing the Java SQL package. I've got a connection string. I've built a connection from that. I have a SQL string. I create a statement with that SQL string. I bind a value to it. I execute the query and I get back a result set. And this is a Java SQL result set that I get back. I can do the same thing with Closure, just using the Java interop. Remember, Closure is dynamically typed, so I only need to import the thing that I'm actually going to refer to by name, the driver manager here. Again, I've got the same SQL connection URL, create a connection. I've got a SQL string. The statement, there's some nicety here. I can do that all in one expression with the binding and the statement creation. I execute the query and again, I'm going to get back a Java SQL result set. This isn't really idiomatic Closure though, and it's not uncommon for Closure developers to create packages that wrap these Java libraries to make it a little easier to use. For example, the Closure Java JDBC library is a really good example. Shod Corfield has been maintaining a fantastic package to take a look at how that's used. Here we require the Java JDBC library. Notice here, instead of an opaque UR connection string, it's actually broken down into a simple map with the connection parameters. We've got a SQL string and look at where we're actually calling the query. Here we have a data structure that represents the query and the parameters we want to pass to it. With the connection parameters, we call the query and what we get back is one of these basic Closure data structures. In this case, it's a sequence with a map containing the row that's being returned. A Closure developer can act on this directly. They don't need to look up the Java doc to figure out how to iterate, for example, over a result set. So I'm using Closure. I've got great Java interop. I've got a great database system with Postgres and can even a nice Closure wrapper to make that really idiomatic. So why can't I get no satisfaction? What is my problem? If you just want a good SQL implementation, Postgres is great for that. Java JDBC, the Postgres driver, the Closure package around that, all you want is SQL. This is a great package. However, how many of us likes Postgres more than just SQL? There's fantastic types. It's got all of these other features associated with that. And that's one of the reasons that I choose Postgres. One of the tools around that, for example, is libpq, the C-client library. Anything that implements libpq automatically gets access to all of the environment connection parameters, the pgpass file, the service file, which my goodness, once I realized how to use that, you can have collections of connection parameters and refer to those by name. Fantastic. And you can define all of your connection information in one place. If you're using PSQL or Ruby or Python or Perl, all of that stuff is only defined once. Fantastic. Yay, I'm on the JVM. I don't use the C-client library. And I really don't like having to define my connection parameters one more time. It's one more thing for me to forget to update. So in a fit of peak and frustration, I finally realized, oh, I don't need to implement a complete client. Java can read the environment just as well as C-can. So I've created a small library in the Unix tradition of trying to do one thing and do it the best I can. And I created the peak library. And it returns two functions. It provides two functions, one of which returns the environment parameters. It reads the same files as libpq does and provides them in a libpq flavor. And also, I do want to use it with the JDBC stuff. So the JDBC spec function includes the DB type. And there's some spelling differences with some of the parameters. So hey, isn't nice, small wins. It's amazing how these can make your life easier. But let's take a step back and think of why we're using a database in the first place. You might be using it as a common integration point for applications. Maybe you really like the analytics and the reporting that's available in SQL or in your database system. But it really all comes down to the data. And you want to make sure that that data is all consistent. If you're using something like small talk, your database is most likely going to be some kind of object store. The data that's in your object store is going to be the same types of data that you've got in your application. Another example of this would be maybe if you're using Node.js with MongoDB, the types in your data store are the same types that you've got in your application. If you're using PSQL or if you're using Postgres, unless you're actually in Postgres itself, the types in your database are likely going to have some mismatch with the types in your application. And this isn't the object relational mismatch that people often talk about, where you're mapping classes to, for example, database tables. These are the concrete types, for example, in PG type. In Java, some of the types, the short, integers, and longs, they map very nicely to Postgres's int2, int4, and int8. It's very easy to encode those and decode those, pretty much adjust verbatim from one system to the other. Other types are a little more complex. For example, date times. Postgres has great date time support and has for years. Java has been a little bit more frustrating. Java Util Date was an example we saw so far. That is not really a great system to be using if you want to do any kind of, actually any kind of date manipulation. And the Jota Time Library became very popular because it had a lot of great facilities for that. And the Jota Time Library strongly influenced the Java Time package that was included recently in Java 8. So let's take a look at Java Time Instant Offset Date Time. Both of these are great representations in Postgres as timestamped TZ. If you have a Postgres timestamped TZ value, well, which one are you going to decode that to? It really depends on your application and what you're going to use. If you're using Java JDBC, you're going to, the way that you do this is you go from your Java Instant to JDBC Java SQL timestamp and then back, you have to do this encoding and decoding through this intermediary type. And remember back at the beginning where I was talking about how Clojure developers will use a few key data structures to represent a lot of different data? Well, how are we going to switch on type for that? For example, the Clojure maps we were looking at. In Postgres, we've got a number of types, HStore, JSON, even points that we may end up representing in maps in Clojure. But if we've got a Clojure map, there's really not a lot of information there for us to decide on how we're going to encode that in Postgres. And for example, the community JDBC driver, when it sees a Java map, it'll try to encode that as HStore. This was before Postgres had good JSON support and before JSON was so popular. But now with the rise in popularity with JSON, it probably makes sense to encode Clojure maps as JSON. Make sense? So how does all this encoding and decoding happen anyway? Well Postgres has the version three protocol, which describes all of the messages that can go between the client back end. I forgot that wrong, didn't I? The server back end and the client front end. And it describes all of the valid messages and this is actually almost 15 years old now. It was released with Postgres 7.4 back in 2003. And there's a lot of facility there. I was surprised how much there was there when I started digging into it. If you take a SQL query, for example, the one at the top, you can see that I've got two parameters and I'm returning three columns. I can pass this query to the back end and ask Postgres to parse it and give me examples of what's it expecting for those parameter types and what it's actually going to be returning to me. For example, when I ask Postgres to parse this query, it tells me that the parameters it is expecting are types 25 and 26. These are the OIDs that correspond to the types in the PG type table. 25 is text and 26 is OID, which makes sense given the query that we've got above. We also get back a row description. And if we take a look at the first field there, you can see that it returns the name of the field OID. It also returns the type ID, which is 26, again OID, which is what we would expect. But there's a lot of other information there as well. I was surprised to learn that it also provides the at rel ID and at number. This is the column where that data was actually sourced from Postgres. And here, if Postgres can determine which column it was, it'll provide this information. At rel ID is the PG type table, that's the OID for the PG type table, and at num minus two is the OID column in that table. Taking a look at the second field there, we're returning the type namespace column. And in the query, we're casting that to reg namespace. And that's the type ID, 4089 is the reg namespace type. But notice the at rel ID and at number are both zero. We've done this cast, so the column that's being returned no longer corresponds to the underlying column in the table, so Postgres doesn't provide that information. Again, with the third column, it's returning type name. Type ID is 19, which corresponds to the name, data type, and Postgres. And at rel ID is, again, 1247, the PG type table, and at num is one because type name is the first column in the PG type table. So we actually have a lot of information available to us. If we want to choose our encoding, we can look at the closure value we've got. We can also look at the parameter types that Postgres is expecting. If we're doing decoding, there's a lot more information that I even knew was available to be able to decide how we're going to decode that. And again, one of the things I love about Postgres is the extensive type support that it has. A lot of it's already built in, for example JSON, which has really become the lingua franca. And I think definitely helped with Postgres' popularity rise recently. And so, of course, let's take a look at how we might translate closure maps to JSON. Here I've got a simple closure map with two values. The second one is actually a vector. And I'm just going to try to round trip that. And you can see when I try that, I get an exception. And the first time I saw this exception, I was thinking, well, I've got a closure map. I'm casting it to JSON. What does h-store have to do with any of this? At that point, I had kind of forgotten we even had an h-store type. And what's happening there is, remember, the community driver will look at a Java map and try to encode that as h-store. And closure maps implement Java's map interface. So the community driver is trying to encode that as h-store. I don't have that installed. And therefore, that's the exception I get. So let's install h-store. It wasn't that fast. And so I'm doing the same round trip. I don't get the exception. And I get back this PG object because the driver doesn't know how to choose the encoding back into closure. But it does include a lot. The PG object includes both the type information, which it says it's JSON-B, and it gives me back the value. And at first glance, this looks OK. But take a look closer at that value that's associated with B. It looks almost like an array, but it's actually just a string, bracket 1, space 2, space 3, space close bracket. So we've lost information when we're trying to do our encoding. Well let's try to do that serialization ourselves. I bring in the Cheshire library, which is a common JSON serialization library for closure. I pass my map and serialize that myself, pass the serialization in there and let Postgres do the interpretation. No exception. I get back a PG object. And look at the value. That's actually a JSON array. So just a quick comparison. Without serialization, you can see the difference in the value of B. But again, let's take a look at the keys. The keys are spelled differently as well. And frankly, I don't know whether I can say that one of them is wrong and one of them is right. It really depends on what your application is expecting. It's nice to be able to have the choice of how that's going to be interpreted. User? That's being done by your Cheshire. This hasn't even gone back through Cheshire. Something that's been stripping as code. Oh, right. Exactly. Yep. So, the serialized map, it's stripping the key, it's making the keyword strings. Correct. And that's actually an option in the Cheshire library. And to fully do this, I would then want to pass that get value, return to string, and I would want to pass that string back to the Cheshire library. Good point. But the whole point of what I would like to do is I would like to be able to ignore all of that stuff. I wouldn't, I don't want to have to do all of this kind of stuff manually. User defined types are another thing that's really nice in Postgres, to be able to define types that aren't even part of Postgres natively. And PostGIS is a great example of that. They've implemented a lot of C-based types that make it very efficient for indexing or for operations on more complex data. But you don't even have to go into C to really take advantage of user defined types. Do you have a question, Steve? Okay. For example, enums. I love enums. I like the additional validation that you get with them. I like the more efficient storage and I can still have something that's readable as opposed to some magic number. So let's create a myenum type in the zoo schema. It's got three values, A, C, and B. And in closure, again, it's rather than creating a separate class for this, it's common to use just a keyword. So let's try to round trip that. And I unsurprisingly get an exception because it's Java database connectivity. It's not closure database connectivity. So it has no idea what to do with this keyword. So let's kind of serialize it ourselves like we did with JSON. This name function called on a keyword will return a string of the keyword. And I get back a Postgres object because Postgres doesn't know how to decode an enum into closure. And I can see that the type that comes back is zo myenum. So that looks pretty good. It looks good, but there's actually a bug in the Postgres community driver where you can actually lose the schema information depending on what your order of operations are when you're using user-defined types. And I'm actually a big fan of namespaces. I didn't realize how important namespaces were until I really saw how well they were used in Java and likewise in closure. Namespaces are a great way to logically separate your code into logical parts. It's also, particularly in Java, you kind of get your own realm of workspace that no one else is going to be creating other functions in there or stomping on your variables. Good morning, Alvaro. And Postgres actually has namespaces as well. They go by the horribly overloaded term schema. And a good indication that schemas are actually meant for namespacing is the underlying Postgres table that implements schemas is called PG namespace. So I really like, even when I create an enum type, to be able to put that in its own namespace, its own schema, all my tables as well. I strongly encourage you, if you don't take advantage of schemas, please start doing it. There are some times you should not be playing in public. So. That's a fantastic idea. Another feature of JDBC that frustrates me a little bit is data realization. When you do a query to the Postgres back end, each, once the data starts coming back, it comes back a row at a time. And that as they come in off the wire, off the socket, the JDBC driver will decode each of those rows and it's, accumulates all those rows together into a result set. We said the Java SQL result sets that we talked about earlier. That whole result set is then handed over to your application. And that's a Java SQL result set. That's very unlikely to be the language of the domain of your own application. You're very likely going to be iterating over that same thing to put it into the language of your application. So you end up iterating over this and realizing this data twice. It would be really, really nice to take that transform function and push it all the way down with the decoding right off of the wire where the rows are coming in. Then I only need to iterate over that data once. And so it's in the language of my application. Postgres also has this fantastic async listen notify system, which I think is underutilized, partly because many of the drivers we already have out there don't do a really great job of exposing it. And it is truly async. These messages can come into your driver to your clients at any time from other clients that happen to be connected at the same time to the Postgres back end. The closure community PGJDBC driver does implement listen notify. And as these messages come in, it accumulates them in an array. But the way that you figure out whether or not you've got a message is that you have to keep polling the driver to see if any of those messages have come in. And you can build up your own loop that does that polling at the frequency that you want, but I would really like a more proper async interface to listen notify. So the things that I would really be looking for in a driver are the features of libpq, which I mentioned that I was already able to take care of with the peak library, good user-defined types with good namespace support. I really only want to realize my data once. And I want to have an idiomatic async interface with listen notify. So yeah, that's kind of why I'm not satisfied. And other people have found that they would like something a little bit different as well. There's the PGJDBC-NG driver, which is an alternative JDBC implementation. And it provides a little better support in some of these areas. Thomas Heller and Auntie Laissie both have created non-JDBC Postgres drivers in Java. And interestingly enough, both of them also provide closure wrappers for those. But if you take a look at the matrix, none of them really satisfy the criteria that I was looking at. I was also a little motivated to get my hands a little dirty and understand the version 3 protocol better. So I created the library zoo. Taking a page from the Postgres mascot, Slonic, which my understanding means little elephant, zoo is the Japanese name for elephant. It's also a very nice short word. So it saves me a little bit of typing. And as far as I know, it's not taken by anyone else. So one more domain owned by Michael Glazman. So we've gotten to our destination. So let's take a look at how it's going so far. So as is very common, closure developers can do a lot of work at what's called a REPL. It's very much like working with the PSQL interface. Here at this prompt at the bottom there, I am connected to a live closure back end with my libraries loaded. And I can put in expressions there. For example, let's add 1 and 2, and I get back to response 3. Kind of nice. But I actually don't do a lot of my work directly at a REPL like this. Yeah, the hazards of live coding. I usually have a separate file open, and this file is actually associated with that REPL. And I can take one of these expressions and pass it to the REPL and get back the results right here. And that's usually done with a key command. So I can take that same 1 plus 2, and it's evaluated, and it's given me the result right there. So I love test-driven development, and often we'll have my files being watched for any changes and they run my tests again. Give me a pretty good turnaround. This is a lot faster. And I can use any expression like that. So let's require the zoo library. Notice both zoo and peak are still alpha. So let's require those. And remember, peak should give me the environment variables. And there's the connection information that I've got for my Postgres connection. Let's assign those parameters to this comprams var. And here I'm going to actually create a connection that wraps those connection parameters. This client is actually just a session factory. It hasn't created a connection at this point, but it knows how to create connections. So let's actually create a session from the client. So this now has connected to the Postgres instance. And like being a good closure dev, I want to be able to specify my query as one of these simple data structures. So I've got a map here with a SQL key that indicates the statement that I want to send to the back end. I evaluate that. I get back a result set, which is enclosed in a vector, and it's returning one map for each row that's coming back. In this case, we've got one row with one column and the value one. So a lot of ones going on here. And if I need parameters to associate with that, I can add the params key with a vector of the parameters that go in, and I can evaluate that. One of the things that's really nice about this is that map is just a data structure. I can pass that around and modify it as I'm doing work. And then when I actually need to execute it, I can just pass it to the query. And yes, we do have things that can return more than one row. So here's an example of multiple maps coming back with one result set. One of the other things that I get a little bit bored with is I know that this query is going to return just a single value and always unwrapping those things and unwrap it from the result set, unwrap it from the row. So I've provided a few shortcuts for myself as well. So I know this is going to return a value. So the query form is a little bit different, but it does all that in wrapping for me. This isn't really magic. It just saves me some time. Similarly, I can pass in some parameters and return just a row. It unwraps it so it returns a map. And sometimes you don't even want the column names. You just want a vector of information back. So row v indicates that it's going to return a vector, and that's what I get. Any questions on the data structures here so far? And one of the big things for me, of course, is types. And at the end of the day, there are three things you need if you've got a custom type. You need to know its name because the OID can change depending on your Postgres installation. You need to know how to encode it going from closure into Postgres. And we already looked at this name function that will do that for us. And similarly, on the way out, you need a function that's going to decode the value that comes back from Postgres. For the enums that we were using before, I'm going to use keywords as values in closure, and I'm going to use the name function and the keyword function to do my encoding and decoding. So I need the name of the type, a way to encode, and a way to decode. And I want to be able to do that flexibly. So I'll bring in another namespace from closure, which has information on how to do that. And here I'm specifying the type name that's associated with that type. Here's where I'm taking a keyword, and I'm wrapping it with the name function. And here's where I'm decoding it. It's taking a string from the Postgres back end and calling the keyword function on that. So let's define that. And this is one of the cases where a closure developer may actually be using classes. This def type actually does create a Java class called xymezo myenum codec. But I'm not using this to encode any kind of state or data. It's a convenient way of associating these three functions together. So it is a class. So to use it, I'm going to instantiate it. And here I've got a query where I'm going to try to round trip that keyword value. I've got the keyword parameter. But I also can supply my encoders and decoders directly in the query. So the encoder vector corresponds to, I should have one encoder for each parameter that I'm passing in. And similarly, the decoders, I should have one decoder for every column that I'm returning. And here I've got one column or one parameter in one column. And I'm using the same, since I'm round tripping, I'm using the same codec. So ta-da, keyword going in, keyword coming out. But who wants to type that every time they need to specify something? So it'd be nice to be able to just add this to the number of types that your clients actually just know. So here I'm going to import the registry, which is the collection of encoders and decoders that the clients can know about. And I'm going to add these codecs as encoders and decoders. And just, this is one of those cases where that motivates why I have a connection factory. So now this client knows about the connection parameters, but it also has this other registry of encoders and decoders that it has. And so let's create a session using that new client. So here, oh gosh, I have debugging information in there. This is one of the bugs that still doesn't work. But notice here I don't supply the encoders and decoders, because hopefully the client already knows how to do that. And that works as well. So it looks like I've got a pretty convenient way to specify in a custom way my encoders and decoders. I also mentioned that I wanted idiomatic async listen notify. And there's a lot of different strategies for using async and in a lot of different languages. Callbacks are one common way of doing that. In Clojure, how many people have used go or looked at go? And go famously has go loops, which allow you to do async. And both Clojure core async library and go loops are inspired by Tony Hor's communicating sequential processes, another fantastic, good, rigorous academic paper. And the core async library, rather than using callbacks, has the concept of channels. And a channel is this object that you pass around. And some parts of the application can take that channel and push messages onto it. And other parts of the application can pull messages off of it. So it's a way of decoupling different aspects of your application, but they can still communicate with each other. So that's what I'm going to use here to, as the messages come into the session, is the session will put those messages onto a channel. And I'm going to grab a channel from the session and pull messages off of it as they come in, asynchronously, not something that I will need to pull on. So let's create a client that's going to be listening for a session for listening. And this is how I get access to that channel. I call this O listen function on the session. But there's a lot of different strategies that you might want. Any time that you've got messages being passed in forth, back and forth, you might want to figure out your buffering strategies and how that's going to be doing. And by default, core async will just block. Once a message goes on something, it won't allow any more messages to be put on that until someone pulls it off. So I'm going to create a loop here, a go loop, actually, that's going to take a channel. And every time it'll, at the start of the loop, it'll wait until a message comes in. And then here, I'm going to print that message to standard out with this herd prefix and whatever the message came in. And then it's just going to loop. So this will consume messages as they come in on the channel so it won't block. And so I actually will be able to see with my output. Most likely, you're going to do something more interesting than just print them to standard out. But this will work for the purposes of demonstration. And oh, I didn't. That's what happens when you don't require the library. Let's require the library. Let's create the listener, grab our listening channel, and print loop. Great. So we'll call that on that channel that we've got. Okay. And you can see that repl that we were connected to earlier. That's where the errors came out as well. So this is the repl that's being connected. I still have to tell the back end what pulsegres channels I want to listen to. So let's listen to my channel. And for fun, let's listen to my other channel as well. We need someone to be sending messages, so let's create a session that will send notifications. And let's send a notification to MyChan. And you can see in standard out, we heard a message. It's process ID 23430 on the channel name MyChan and with an empty payload. That process ID refers to the back end that's supporting the Notifier session. And this is actually all of the information that's available that comes back from the version 3 protocol. You can do with that whatever you want. Let's make sure that we can pass a payload and you can see the payload through comes through and send a message on the other channel. We're listening to that as well. And what happens if we send something we're not listening to? Well, message was sent and as expected, we didn't hear anything because we weren't listening for it. And believe me, this stuff isn't just broken. We can still listen to the things we were expecting before. So hey. So that's Zo. And I try to get back to my slides. Hey, there are worse technical difficulties than to lose your last two slides. So am I satisfied? Well, if you remember my criteria, I wanted the facilities with libpq to be able to read my environment. I was able to get that with the peak library. I didn't need to do anything with my, with my, with actually create a client for that. I wanted good namespace support with user defined types. And you can see that it's really easy to create your encoders and decoders and create a session that knows how to handle those without digging into the internals of any kind of driver. I wanted to have good core async support with idiomatic async listen notify. And you can see that I got that. So in terms of the criteria that I had set up for myself, yeah, I am pretty satisfied with how things are working out. This is still very much alpha and I'm still dog-fooding it and making improvements to it. But I'm pretty happy with how it's shaping up. And my name is Michael Glaisman. If you go up to GRZM on GitHub, you can see the library that's available there and how that's going. And I'm happy to answer any of your questions. We have a few minutes left. Yeah. We should really change the JDBC driver so it's not using HST. Pardon? We should really change the community of JDBC drivers so that at least you have the option of not using HST or having good structure sense of JSON instead of directly instead of HST or anything. Oh, indeed. The only reason that I've started using, getting involved in the community JDBC driver, is to fix some of these things. I think the first time I actually looked at the code was September of last year, so I'm really a neophyte. I was happy that at Postgres comp in Drizzy City that Dave actually came to my talk and said, oh, these are things that we can improve. So he's very open to doing things like that as well. And very few of these limitations are actually inherent in the JDBC spec. Some of them are like the results that realizing things once. The namespace thing, that's just a bug, which unfortunately has been bike-shedded for a number of years. And I just need to get enough people on board to review some of the patches that I've got in for fixing them. But yeah, there's a lot of things that Dave's willing to do to improve it. Any other questions? Yeah. I'm not from Java world, so I don't know what the enclosure and Java hold things can go together. But I currently contribute to the JDBC to satisfy the support. Yeah, I saw that Serope started a thread on that. And I've wanted to chime in a little bit more on that thread. And I haven't seen the direction that's going. But I think there's no reason why we can't be doing this in JDBC itself. Yes, yes, that's not the problem. I think like the use is quite heavy. Yes. And for me, I would put in developer stuff. So it's quite hard to dive into. It is. And I would encourage you, any problems that you've got getting started? Like, I have a collection of notes for myself that, like, where did I find this stuff? So anything that you can share as well? Because we should really improve the onboarding for that. I see that there is a JDBCNG. Yes. So there is new clients everywhere for Java developers. I'm wondering, can you use a zoo when you write Java and so on? Do you think together it's the basic choice? I can. I'll answer this question and I believe we're going to be done. But I can create interfaces from zoo. So you could call that from Java. I haven't created that yet. I'm unsure whether or not there would be a lot of benefit from doing that, just because it is really specific to trying to make the closure experiences well. And as you can see, what people expect from Java and what people expect from closure can be quite different. So I guess you didn't have a lot of coverage on progress questions. No. To be honest, what you saw up there is pretty much all zoo does right now. So no, I don't have any coverage for previous versions. And which is also a benefit, right? The JDBC driver has an expectation that it would go back and support things that have been around for many, many years. I don't envy that and I am very happy to say no. Postgres 10 and if it happens to work earlier, great. And if it doesn't, that's... Yeah, exactly, exactly. If it breaks, you get to keep it pieces. Right, exactly. So thank you very, very much. Thank you very much.
 So some signposts along our way.  First a brief introduction to closure and then hopefully provide some motivation on why I  would actually want to do something without JDBC.  And then take a look at Zo, the client that I've been working on.  If you have any questions as we go by, I will be moving relatively fast, but feel free  to pipe up or raise your hand and I'll look to answer the questions as we go.  So closure, how many people have had any exposure to closure?  Or using it or have played around with it or at least have heard of it?  Great.  Well, let's just jump right in and take a look at some closure.  Here are some, in particular, these are some closure data structures.  The first is a vector of three strings and the next one is a map.  And we're using keywords for the keys of this map.  Though this is very common for closure developers to use, but keys and objects and values in  a map can be of any type.  For example, the next map we have here, the keys are vectors.  And here I'm using them to represent schema-qualified type names and they're mapping them to the  OID from the PG type table.  And how might a closure developer actually use these data structures?  Well, here I have a vector that I'm going to represent as the series of Postgres conference  talks.  Each of those talks is represented as a map.  Of course, there are so many talks and there's a lot of data here.  I've elided most of those.  And little fun fact, commas are considered white space enclosure.  And there's a convention to use three commas like this to show e-leasion and still be syntactically  valid.  That's the best thing to have.  And even this, for the examples I've got, the description is really long.  So I'm just going to omit that in my summaries.  But imagine that it's still there.  So one of the features of Postgres that I'm particularly interested in right now is logical  decoding and logical replication.  So I want to take a look at the talks that have logical in their description.  So here I'm creating a simple function that takes one of those talk maps, takes out the  description and does a reget spine for logical in that description.  It'll return a truthy value if it finds the logical there and falsey otherwise.  So I can filter the talks using this predicate function.  And you can see that we've got five talks at PGCon that have logical in the description.  And of course, these are in just some random order.  So I want to put them in order so it makes it easier for me to arrange my schedule.  So I can pass the output of the filter function as an argument to sort by.  And this sorts them by the at value of those maps.  And as is often the case, once you start drilling into the data, you realize, oh, there's even  more I want to do.  So I can keep on passing the result of one function call as an argument to another.  But that can get really tedious to keep on both to write and to read, trying to go work  backwards.  So closure provides some alternatives in how to write that, for example, using threading.  Here talks is being passed to filter logical, which is being passed to this sort by.  And if I stopped right there, that's exactly the same as the first expression above.  But again, what I want to figure out, well, when do I need to leave to make sure I attend  the very first talk?  So I pass the results of that to first.  It gives me the first talk.  Then I grab the at value of that.  Then I have a function that calculates my travel time and adds that to this.  All of this other than the logical function and the add travel time, those are parts of  the core closure functionality.  Closure makes it really easy to do a lot of data processing like this.  And I like my type checking, like I like my superheroes, dynamic and strong.  Closure is dynamically typed.  I don't need to identify my types in the code.  And types are not checked at compile time.  But closure is strongly typed.  Types are checked at runtime.  And many of the features of closure actually does dispatch on the types of the values.  That said, closure developers will often use these basic data structures rather than instantiate  specific classes to group types together.  They'll often use maps with keys identifying fields rather than creating an instance of  something.  And this is actually kind of a superpower itself.  Bruce Lee famously said, I fear not the man who practices 10,000 kicks once.  I fear the man who practices one kick 10,000 times.  So as you become very familiar with a few core data structures, a closure developer very  easily is able to process lots of different data in many different ways.  Alan Perlis in his epigrams of programming said something similar when he said that it's  better to have 100 functions that operate on a single data structure than 10 functions  that operate on 10 data structures.  Closure also makes a very clean distinction between value, identity and state.  This is something that should be really familiar to us as database developers.  We refer to tables, for example, by name.  Even though the number of rows in that table or the values of the columns of that table  may change over time, we know what we mean when we refer to the user's table, for example.  And as I mentioned, the number of rows and the values in the rows can change.  That's the state changing over time.  If you take a query on that table, you get a consistent snapshot of the query when you  execute it.  Even if the table gets changed after that query is made, the result you got stays the  same.  That's a value from that snapshot.  It's really important in Postgres, of course.  There's a lot of work being done to make sure that those snapshots you get are consistent  and efficient.  And of course, Postgres uses MVCC to do that, multiversion concurrency control.  Closure also uses multiversion concurrency control to make sure that you have consistent  snapshots of data that may be shared between different parts of your application.  The implementation is quite a bit different.  In particular, Closure uses something called persistent data structures.  And Chris Okezaki's 1996 PhD thesis, purely functional data structures, is a really interesting  read and was really influential in making these, popularizing these in the use of functional  programming.  Phil Bagwell, while at the Federal Institute of Technology in Luzon, Switzerland, built  upon that work and created something called array mapped, array, hashed array map trees  or some combination of that.  And those features there provide the implementation of persistent data structures in Closure.  And this actually is one of the reasons that I really like Closure.  And one of the things that's appealed to me about with Postgres over the years that I've  been using it, both Closure and Postgres are built on these really rigorous academic foundations,  but they make a very practical and versatile tool.  If you look at Postgres' history, if you're reading the source code, there are references  to Knuth all over the place.  Also, you think of Kevin Grittner working with Dan Portz at MIT to implement serialized  snapshot isolation.  All of this rigor gives me the confidence that the tools are going to behave consistently  and over time.  That gives me a lot of confidence.  And Closure gives me that as well.  Another feature that's very practical with Closure is its Java interop.  Closure is hosted on the JVM, but it doesn't try to abstract away the underlying platform.  It makes it really simple to use Java directly.  For example, here I've got a Java Util date instance constructor.  I'm assigning that instance to the bar now.  And any of the methods that you might expect to work on a Java Util instance, for example,  at a time, will work just as expected.  That's the number of milliseconds since Unik epoch.  Similarly, class methods or constants, all of this is immediately available in Closure.  This gave Closure a fantastic advantage as soon as it was released.  You didn't need to wait for all of the libraries that were already available for Java were  instantly available to Closure as well.  For example, if you were doing natural language processing, you didn't need to wait for a  native Closure library.  You could just use Stanford Core NLP.  And if you want to use a SQL database, you can use JDBC right out of the box.  You want to use Postgres.  We've got a great community driver, the PGJDBC driver.  So let's take a quick look at what JDBC looks like.  First in Java.  Here, I'm importing the Java SQL package.  I've got a connection string.  I've built a connection from that.  I have a SQL string.  I create a statement with that SQL string.  I bind a value to it.  I execute the query and I get back a result set.  And this is a Java SQL result set that I get back.  I can do the same thing with Closure, just using the Java interop.  Remember, Closure is dynamically typed, so I only need to import the thing that I'm actually  going to refer to by name, the driver manager here.  Again, I've got the same SQL connection URL, create a connection.  I've got a SQL string.  The statement, there's some nicety here.  I can do that all in one expression with the binding and the statement creation.  I execute the query and again, I'm going to get back a Java SQL result set.  This isn't really idiomatic Closure though, and it's not uncommon for Closure developers  to create packages that wrap these Java libraries to make it a little easier to use.  For example, the Closure Java JDBC library is a really good example.  Shod Corfield has been maintaining a fantastic package to take a look at how that's used.  Here we require the Java JDBC library.  Notice here, instead of an opaque UR connection string, it's actually broken down into a simple  map with the connection parameters.  We've got a SQL string and look at where we're actually calling the query.  Here we have a data structure that represents the query and the parameters we want to pass  to it.  With the connection parameters, we call the query and what we get back is one of these  basic Closure data structures.  In this case, it's a sequence with a map containing the row that's being returned.  A Closure developer can act on this directly.  They don't need to look up the Java doc to figure out how to iterate, for example, over  a result set.  So I'm using Closure.  I've got great Java interop.  I've got a great database system with Postgres and can even a nice Closure wrapper to make  that really idiomatic.  So why can't I get no satisfaction?  What is my problem?  If you just want a good SQL implementation, Postgres is great for that.  Java JDBC, the Postgres driver, the Closure package around that, all you want is SQL.  This is a great package.  However, how many of us likes Postgres more than just SQL?  There's fantastic types.  It's got all of these other features associated with that.  And that's one of the reasons that I choose Postgres.  One of the tools around that, for example, is libpq, the C-client library.  Anything that implements libpq automatically gets access to all of the environment connection  parameters, the pgpass file, the service file, which my goodness, once I realized how to  use that, you can have collections of connection parameters and refer to those by name.  Fantastic.  And you can define all of your connection information in one place.  If you're using PSQL or Ruby or Python or Perl, all of that stuff is only defined once.  Fantastic.  Yay, I'm on the JVM.  I don't use the C-client library.  And I really don't like having to define my connection parameters one more time.  It's one more thing for me to forget to update.  So in a fit of peak and frustration, I finally realized, oh, I don't need to implement a  complete client.  Java can read the environment just as well as C-can.  So I've created a small library in the Unix tradition of trying to do one thing and do  it the best I can.  And I created the peak library.  And it returns two functions.  It provides two functions, one of which returns the environment parameters.  It reads the same files as libpq does and provides them in a libpq flavor.  And also, I do want to use it with the JDBC stuff.  So the JDBC spec function includes the DB type.  And there's some spelling differences with some of the parameters.  So hey, isn't nice, small wins.  It's amazing how these can make your life easier.  But let's take a step back and think of why we're using a database in the first place.  You might be using it as a common integration point for applications.  Maybe you really like the analytics and the reporting that's available in SQL or in your  database system.  But it really all comes down to the data.  And you want to make sure that that data is all consistent.  If you're using something like small talk, your database is most likely going to be some  kind of object store.  The data that's in your object store is going to be the same types of data that you've got  in your application.  Another example of this would be maybe if you're using Node.js with MongoDB, the types  in your data store are the same types that you've got in your application.  If you're using PSQL or if you're using Postgres, unless you're actually in Postgres  itself, the types in your database are likely going to have some mismatch with the types  in your application.  And this isn't the object relational mismatch that people often talk about, where you're  mapping classes to, for example, database tables.  These are the concrete types, for example, in PG type.  In Java, some of the types, the short, integers, and longs, they map very nicely to Postgres's  int2, int4, and int8.  It's very easy to encode those and decode those, pretty much adjust verbatim from one  system to the other.  Other types are a little more complex.  For example, date times.  Postgres has great date time support and has for years.  Java has been a little bit more frustrating.  Java Util Date was an example we saw so far.  That is not really a great system to be using if you want to do any kind of, actually any  kind of date manipulation.  And the Jota Time Library became very popular because it had a lot of great facilities for  that.  And the Jota Time Library strongly influenced the Java Time package that was included recently  in Java 8.  So let's take a look at Java Time Instant Offset Date Time.  Both of these are great representations in Postgres as timestamped TZ.  If you have a Postgres timestamped TZ value, well, which one are you going to decode that  to?  It really depends on your application and what you're going to use.  If you're using Java JDBC, you're going to, the way that you do this is you go from your  Java Instant to JDBC Java SQL timestamp and then back, you have to do this encoding and  decoding through this intermediary type.  And remember back at the beginning where I was talking about how Clojure developers will  use a few key data structures to represent a lot of different data?  Well, how are we going to switch on type for that?  For example, the Clojure maps we were looking at.  In Postgres, we've got a number of types, HStore, JSON, even points that we may end  up representing in maps in Clojure.  But if we've got a Clojure map, there's really not a lot of information there for us to decide  on how we're going to encode that in Postgres.  And for example, the community JDBC driver, when it sees a Java map, it'll try to encode  that as HStore.  This was before Postgres had good JSON support and before JSON was so popular.  But now with the rise in popularity with JSON, it probably makes sense to encode Clojure  maps as JSON.  Make sense?  So how does all this encoding and decoding happen anyway?  Well Postgres has the version three protocol, which describes all of the messages that can  go between the client back end.  I forgot that wrong, didn't I?  The server back end and the client front end.  And it describes all of the valid messages and this is actually almost 15 years old now.  It was released with Postgres 7.4 back in 2003.  And there's a lot of facility there.  I was surprised how much there was there when I started digging into it.  If you take a SQL query, for example, the one at the top, you can see that I've got  two parameters and I'm returning three columns.  I can pass this query to the back end and ask Postgres to parse it and give me examples  of what's it expecting for those parameter types and what it's actually going to be returning  to me.  For example, when I ask Postgres to parse this query, it tells me that the parameters  it is expecting are types 25 and 26.  These are the OIDs that correspond to the types in the PG type table.  25 is text and 26 is OID, which makes sense given the query that we've got above.  We also get back a row description.  And if we take a look at the first field there, you can see that it returns the name of the  field OID.  It also returns the type ID, which is 26, again OID, which is what we would expect.  But there's a lot of other information there as well.  I was surprised to learn that it also provides the at rel ID and at number.  This is the column where that data was actually sourced from Postgres.  And here, if Postgres can determine which column it was, it'll provide this information.  At rel ID is the PG type table, that's the OID for the PG type table, and at num minus  two is the OID column in that table.  Taking a look at the second field there, we're returning the type namespace column.  And in the query, we're casting that to reg namespace.  And that's the type ID, 4089 is the reg namespace type.  But notice the at rel ID and at number are both zero.  We've done this cast, so the column that's being returned no longer corresponds to the  underlying column in the table, so Postgres doesn't provide that information.  Again, with the third column, it's returning type name.  Type ID is 19, which corresponds to the name, data type, and Postgres.  And at rel ID is, again, 1247, the PG type table, and at num is one because type name  is the first column in the PG type table.  So we actually have a lot of information available to us.  If we want to choose our encoding, we can look at the closure value we've got.  We can also look at the parameter types that Postgres is expecting.  If we're doing decoding, there's a lot more information that I even knew was available  to be able to decide how we're going to decode that.  And again, one of the things I love about Postgres is the extensive type support that  it has.  A lot of it's already built in, for example JSON, which has really become the lingua  franca.  And I think definitely helped with Postgres' popularity rise recently.  And so, of course, let's take a look at how we might translate closure maps to JSON.  Here I've got a simple closure map with two values.  The second one is actually a vector.  And I'm just going to try to round trip that.  And you can see when I try that, I get an exception.  And the first time I saw this exception, I was thinking, well, I've got a closure map.  I'm casting it to JSON.  What does h-store have to do with any of this?  At that point, I had kind of forgotten we even had an h-store type.  And what's happening there is, remember, the community driver will look at a Java map and  try to encode that as h-store.  And closure maps implement Java's map interface.  So the community driver is trying to encode that as h-store.  I don't have that installed.  And therefore, that's the exception I get.  So let's install h-store.  It wasn't that fast.  And so I'm doing the same round trip.  I don't get the exception.  And I get back this PG object because the driver doesn't know how to choose the encoding back  into closure.  But it does include a lot.  The PG object includes both the type information, which it says it's JSON-B, and it gives me  back the value.  And at first glance, this looks OK.  But take a look closer at that value that's associated with B. It looks almost like an  array, but it's actually just a string, bracket 1, space 2, space 3, space close bracket.  So we've lost information when we're trying to do our encoding.  Well let's try to do that serialization ourselves.  I bring in the Cheshire library, which is a common JSON serialization library for closure.  I pass my map and serialize that myself, pass the serialization in there and let Postgres  do the interpretation.  No exception.  I get back a PG object.  And look at the value.  That's actually a JSON array.  So just a quick comparison.  Without serialization, you can see the difference in the value of B. But again, let's take a  look at the keys.  The keys are spelled differently as well.  And frankly, I don't know whether I can say that one of them is wrong and one of them  is right.  It really depends on what your application is expecting.  It's nice to be able to have the choice of how that's going to be interpreted.  User?  That's being done by your Cheshire.  This hasn't even gone back through Cheshire.  Something that's been stripping as code.  Oh, right.  Exactly.  Yep.  So, the serialized map, it's stripping the key, it's making the keyword strings.  Correct.  And that's actually an option in the Cheshire library.  And to fully do this, I would then want to pass that get value, return to string, and  I would want to pass that string back to the Cheshire library.  Good point.  But the whole point of what I would like to do is I would like to be able to ignore all  of that stuff.  I wouldn't, I don't want to have to do all of this kind of stuff manually.  User defined types are another thing that's really nice in Postgres, to be able to define  types that aren't even part of Postgres natively.  And PostGIS is a great example of that.  They've implemented a lot of C-based types that make it very efficient for indexing or  for operations on more complex data.  But you don't even have to go into C to really take advantage of user defined types.  Do you have a question, Steve?  Okay.  For example, enums.  I love enums.  I like the additional validation that you get with them.  I like the more efficient storage and I can still have something that's readable as opposed  to some magic number.  So let's create a myenum type in the zoo schema.  It's got three values, A, C, and B. And in closure, again, it's rather than creating  a separate class for this, it's common to use just a keyword.  So let's try to round trip that.  And I unsurprisingly get an exception because it's Java database connectivity.  It's not closure database connectivity.  So it has no idea what to do with this keyword.  So let's kind of serialize it ourselves like we did with JSON.  This name function called on a keyword will return a string of the keyword.  And I get back a Postgres object because Postgres doesn't know how to decode an enum into closure.  And I can see that the type that comes back is zo myenum.  So that looks pretty good.  It looks good, but there's actually a bug in the Postgres community driver where you  can actually lose the schema information depending on what your order of operations are when  you're using user-defined types.  And I'm actually a big fan of namespaces.  I didn't realize how important namespaces were until I really saw how well they were  used in Java and likewise in closure.  Namespaces are a great way to logically separate your code into logical parts.  It's also, particularly in Java, you kind of get your own realm of workspace that no  one else is going to be creating other functions in there or stomping on your variables.  Good morning, Alvaro.  And Postgres actually has namespaces as well.  They go by the horribly overloaded term schema.  And a good indication that schemas are actually meant for namespacing is the underlying Postgres  table that implements schemas is called PG namespace.  So I really like, even when I create an enum type, to be able to put that in its own namespace,  its own schema, all my tables as well.  I strongly encourage you, if you don't take advantage of schemas, please start doing it.  There are some times you should not be playing in public.  So.  That's a fantastic idea.  Another feature of JDBC that frustrates me a little bit is data realization.  When you do a query to the Postgres back end, each, once the data starts coming back, it  comes back a row at a time.  And that as they come in off the wire, off the socket, the JDBC driver will decode each  of those rows and it's, accumulates all those rows together into a result set.  We said the Java SQL result sets that we talked about earlier.  That whole result set is then handed over to your application.  And that's a Java SQL result set.  That's very unlikely to be the language of the domain of your own application.  You're very likely going to be iterating over that same thing to put it into the language  of your application.  So you end up iterating over this and realizing this data twice.  It would be really, really nice to take that transform function and push it all the way  down with the decoding right off of the wire where the rows are coming in.  Then I only need to iterate over that data once.  And so it's in the language of my application.  Postgres also has this fantastic async listen notify system, which I think is underutilized,  partly because many of the drivers we already have out there don't do a really great job  of exposing it.  And it is truly async.  These messages can come into your driver to your clients at any time from other clients  that happen to be connected at the same time to the Postgres back end.  The closure community PGJDBC driver does implement listen notify.  And as these messages come in, it accumulates them in an array.  But the way that you figure out whether or not you've got a message is that you have  to keep polling the driver to see if any of those messages have come in.  And you can build up your own loop that does that polling at the frequency that you want,  but I would really like a more proper async interface to listen notify.  So the things that I would really be looking for in a driver are the features of libpq,  which I mentioned that I was already able to take care of with the peak library, good  user-defined types with good namespace support.  I really only want to realize my data once.  And I want to have an idiomatic async interface with listen notify.  So yeah, that's kind of why I'm not satisfied.  And other people have found that they would like something a little bit different as well.  There's the PGJDBC-NG driver, which is an alternative JDBC implementation.  And it provides a little better support in some of these areas.  Thomas Heller and Auntie Laissie both have created non-JDBC Postgres drivers in Java.  And interestingly enough, both of them also provide closure wrappers for those.  But if you take a look at the matrix, none of them really satisfy the criteria that I  was looking at.  I was also a little motivated to get my hands a little dirty and understand the version  3 protocol better.  So I created the library zoo.  Taking a page from the Postgres mascot, Slonic, which my understanding means little elephant,  zoo is the Japanese name for elephant.  It's also a very nice short word.  So it saves me a little bit of typing.  And as far as I know, it's not taken by anyone else.  So one more domain owned by Michael Glazman.  So we've gotten to our destination.  So let's take a look at how it's going so far.  So as is very common, closure developers can do a lot of work at what's called a REPL.  It's very much like working with the PSQL interface.  Here at this prompt at the bottom there, I am connected to a live closure back end with  my libraries loaded.  And I can put in expressions there.  For example, let's add 1 and 2, and I get back to response 3.  Kind of nice.  But I actually don't do a lot of my work directly at a REPL like this.  Yeah, the hazards of live coding.  I usually have a separate file open, and this file is actually associated with that REPL.  And I can take one of these expressions and pass it to the REPL and get back the results  right here.  And that's usually done with a key command.  So I can take that same 1 plus 2, and it's evaluated, and it's given me the result right  there.  So I love test-driven development, and often we'll have my files being watched for any  changes and they run my tests again.  Give me a pretty good turnaround.  This is a lot faster.  And I can use any expression like that.  So let's require the zoo library.  Notice both zoo and peak are still alpha.  So let's require those.  And remember, peak should give me the environment variables.  And there's the connection information that I've got for my Postgres connection.  Let's assign those parameters to this comprams var.  And here I'm going to actually create a connection that wraps those connection parameters.  This client is actually just a session factory.  It hasn't created a connection at this point, but it knows how to create connections.  So let's actually create a session from the client.  So this now has connected to the Postgres instance.  And like being a good closure dev, I want to be able to specify my query as one of these  simple data structures.  So I've got a map here with a SQL key that indicates the statement that I want to send  to the back end.  I evaluate that.  I get back a result set, which is enclosed in a vector, and it's returning one map for  each row that's coming back.  In this case, we've got one row with one column and the value one.  So a lot of ones going on here.  And if I need parameters to associate with that, I can add the params key with a vector  of the parameters that go in, and I can evaluate that.  One of the things that's really nice about this is that map is just a data structure.  I can pass that around and modify it as I'm doing work.  And then when I actually need to execute it, I can just pass it to the query.  And yes, we do have things that can return more than one row.  So here's an example of multiple maps coming back with one result set.  One of the other things that I get a little bit bored with is I know that this query  is going to return just a single value and always unwrapping those things and unwrap  it from the result set, unwrap it from the row.  So I've provided a few shortcuts for myself as well.  So I know this is going to return a value.  So the query form is a little bit different, but it does all that in wrapping for me.  This isn't really magic.  It just saves me some time.  Similarly, I can pass in some parameters and return just a row.  It unwraps it so it returns a map.  And sometimes you don't even want the column names.  You just want a vector of information back.  So row v indicates that it's going to return a vector, and that's what I get.  Any questions on the data structures here so far?  And one of the big things for me, of course, is types.  And at the end of the day, there are three things you need if you've got a custom type.  You need to know its name because the OID can change depending on your Postgres installation.  You need to know how to encode it going from closure into Postgres.  And we already looked at this name function that will do that for us.  And similarly, on the way out, you need a function that's going to decode the value  that comes back from Postgres.  For the enums that we were using before, I'm going to use keywords as values in closure,  and I'm going to use the name function and the keyword function to do my encoding and  decoding.  So I need the name of the type, a way to encode, and a way to decode.  And I want to be able to do that flexibly.  So I'll bring in another namespace from closure, which has information on how to do that.  And here I'm specifying the type name that's associated with that type.  Here's where I'm taking a keyword, and I'm wrapping it with the name function.  And here's where I'm decoding it.  It's taking a string from the Postgres back end and calling the keyword function on that.  So let's define that.  And this is one of the cases where a closure developer may actually be using classes.  This def type actually does create a Java class called xymezo myenum codec.  But I'm not using this to encode any kind of state or data.  It's a convenient way of associating these three functions together.  So it is a class.  So to use it, I'm going to instantiate it.  And here I've got a query where I'm going to try to round trip that keyword value.  I've got the keyword parameter.  But I also can supply my encoders and decoders directly in the query.  So the encoder vector corresponds to, I should have one encoder for each parameter that I'm  passing in.  And similarly, the decoders, I should have one decoder for every column that I'm returning.  And here I've got one column or one parameter in one column.  And I'm using the same, since I'm round tripping, I'm using the same codec.  So ta-da, keyword going in, keyword coming out.  But who wants to type that every time they need to specify something?  So it'd be nice to be able to just add this to the number of types that your clients actually  just know.  So here I'm going to import the registry, which is the collection of encoders and decoders  that the clients can know about.  And I'm going to add these codecs as encoders and decoders.  And just, this is one of those cases where that motivates why I have a connection factory.  So now this client knows about the connection parameters, but it also has this other registry  of encoders and decoders that it has.  And so let's create a session using that new client.  So here, oh gosh, I have debugging information in there.  This is one of the bugs that still doesn't work.  But notice here I don't supply the encoders and decoders, because hopefully the client  already knows how to do that.  And that works as well.  So it looks like I've got a pretty convenient way to specify in a custom way my encoders  and decoders.  I also mentioned that I wanted idiomatic async listen notify.  And there's a lot of different strategies for using async and in a lot of different languages.  Callbacks are one common way of doing that.  In Clojure, how many people have used go or looked at go?  And go famously has go loops, which allow you to do async.  And both Clojure core async library and go loops are inspired by Tony Hor's communicating  sequential processes, another fantastic, good, rigorous academic paper.  And the core async library, rather than using callbacks, has the concept of channels.  And a channel is this object that you pass around.  And some parts of the application can take that channel and push messages onto it.  And other parts of the application can pull messages off of it.  So it's a way of decoupling different aspects of your application, but they can still communicate  with each other.  So that's what I'm going to use here to, as the messages come into the session, is the  session will put those messages onto a channel.  And I'm going to grab a channel from the session and pull messages off of it as they come in,  asynchronously, not something that I will need to pull on.  So let's create a client that's going to be listening for a session for listening.  And this is how I get access to that channel.  I call this O listen function on the session.  But there's a lot of different strategies that you might want.  Any time that you've got messages being passed in forth, back and forth, you might want to  figure out your buffering strategies and how that's going to be doing.  And by default, core async will just block.  Once a message goes on something, it won't allow any more messages to be put on that  until someone pulls it off.  So I'm going to create a loop here, a go loop, actually, that's going to take a channel.  And every time it'll, at the start of the loop, it'll wait until a message comes in.  And then here, I'm going to print that message to standard out with this herd prefix and  whatever the message came in.  And then it's just going to loop.  So this will consume messages as they come in on the channel so it won't block.  And so I actually will be able to see with my output.  Most likely, you're going to do something more interesting than just print them to standard  out.  But this will work for the purposes of demonstration.  And oh, I didn't.  That's what happens when you don't require the library.  Let's require the library.  Let's create the listener, grab our listening channel, and print loop.  Great.  So we'll call that on that channel that we've got.  Okay.  And you can see that repl that we were connected to earlier.  That's where the errors came out as well.  So this is the repl that's being connected.  I still have to tell the back end what pulsegres channels I want to listen to.  So let's listen to my channel.  And for fun, let's listen to my other channel as well.  We need someone to be sending messages, so let's create a session that will send notifications.  And let's send a notification to MyChan.  And you can see in standard out, we heard a message.  It's process ID 23430 on the channel name MyChan and with an empty payload.  That process ID refers to the back end that's supporting the Notifier session.  And this is actually all of the information that's available that comes back from the  version 3 protocol.  You can do with that whatever you want.  Let's make sure that we can pass a payload and you can see the payload through comes  through and send a message on the other channel.  We're listening to that as well.  And what happens if we send something we're not listening to?  Well, message was sent and as expected, we didn't hear anything because we weren't listening  for it.  And believe me, this stuff isn't just broken.  We can still listen to the things we were expecting before.  So hey.  So that's Zo.  And I try to get back to my slides.  Hey, there are worse technical difficulties than to lose your last two slides.  So am I satisfied?  Well, if you remember my criteria, I wanted the facilities with libpq to be able to read  my environment.  I was able to get that with the peak library.  I didn't need to do anything with my, with my, with actually create a client for that.  I wanted good namespace support with user defined types.  And you can see that it's really easy to create your encoders and decoders and create a session  that knows how to handle those without digging into the internals of any kind of driver.  I wanted to have good core async support with idiomatic async listen notify.  And you can see that I got that.  So in terms of the criteria that I had set up for myself, yeah, I am pretty satisfied  with how things are working out.  This is still very much alpha and I'm still dog-fooding it and making improvements to it.  But I'm pretty happy with how it's shaping up.  And my name is Michael Glaisman.  If you go up to GRZM on GitHub, you can see the library that's available there and how  that's going.  And I'm happy to answer any of your questions.  We have a few minutes left.  Yeah.  We should really change the JDBC driver so it's not using HST.  Pardon?  We should really change the community of JDBC drivers so that at least you have the option  of not using HST or having good structure sense of JSON instead of directly instead of HST  or anything.  Oh, indeed.  The only reason that I've started using, getting involved in the community JDBC driver, is  to fix some of these things.  I think the first time I actually looked at the code was September of last year, so I'm  really a neophyte.  I was happy that at Postgres comp in Drizzy City that Dave actually came to my talk and  said, oh, these are things that we can improve.  So he's very open to doing things like that as well.  And very few of these limitations are actually inherent in the JDBC spec.  Some of them are like the results that realizing things once.  The namespace thing, that's just a bug, which unfortunately has been bike-shedded for a  number of years.  And I just need to get enough people on board to review some of the patches that I've got  in for fixing them.  But yeah, there's a lot of things that Dave's willing to do to improve it.  Any other questions?  Yeah.  I'm not from Java world, so I don't know what the enclosure and Java hold things can go  together.  But I currently contribute to the JDBC to satisfy the support.  Yeah, I saw that Serope started a thread on that.  And I've wanted to chime in a little bit more on that thread.  And I haven't seen the direction that's going.  But I think there's no reason why we can't be doing this in JDBC itself.  Yes, yes, that's not the problem.  I think like the use is quite heavy.  Yes.  And for me, I would put in developer stuff.  So it's quite hard to dive into.  It is.  And I would encourage you, any problems that you've got getting started?  Like, I have a collection of notes for myself that, like, where did I find this stuff?  So anything that you can share as well?  Because we should really improve the onboarding for that.  I see that there is a JDBCNG.  Yes.  So there is new clients everywhere for Java developers.  I'm wondering, can you use a zoo when you write Java and so on?  Do you think together it's the basic choice?  I can.  I'll answer this question and I believe we're going to be done.  But I can create interfaces from zoo.  So you could call that from Java.  I haven't created that yet.  I'm unsure whether or not there would be a lot of benefit from doing that, just because  it is really specific to trying to make the closure experiences well.  And as you can see, what people expect from Java and what people expect from closure can  be quite different.  So I guess you didn't have a lot of coverage on progress questions.  No.  To be honest, what you saw up there is pretty much all zoo does right now.  So no, I don't have any coverage for previous versions.  And which is also a benefit, right?  The JDBC driver has an expectation that it would go back and support things that have  been around for many, many years.  I don't envy that and I am very happy to say no.  Postgres 10 and if it happens to work earlier, great.  And if it doesn't, that's...  Yeah, exactly, exactly.  If it breaks, you get to keep it pieces.  Right, exactly.  So thank you very, very much.  Thank you very much.
Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_0_107.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_108_251.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_252_383.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_384_971.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_972_1211.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_1212_2147.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_2148_2951.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_2952_3323.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_3324_3791.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_3792_4223.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_4224_4979.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_4980_6059.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_6060_6647.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_6648_7067.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_7068_7643.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_7644_7979.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_7980_9227.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_9228_9671.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_9672_10859.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_10860_11855.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_11856_12167.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_12168_13379.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_13380_13475.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_13476_13739.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_13740_14279.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_14280_15167.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_15168_15371.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_15372_16499.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_16500_17699.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_17700_18911.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_18912_19199.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_19200_20135.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_20136_20567.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_20568_21851.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_21852_22223.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_22224_23015.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_23016_23903.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_23904_24179.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_24180_25187.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_25188_25811.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_25812_29459.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_29460_29627.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_29628_29963.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_29964_31667.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_31668_32279.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_32280_32699.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_32700_33407.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_33408_33491.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_33492_34379.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_34380_35003.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_35004_35279.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_35280_36011.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_36012_36875.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_36876_38687.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_38688_38855.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_38856_39995.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_39996_40223.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_40224_41459.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_41460_42059.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_42060_42239.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_42240_42995.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_42996_43319.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_43320_43583.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_43584_43823.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_43824_52115.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_52116_58403.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_58404_58451.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_58452_58499.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_58500_58799.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_58800_59015.png Michael_Glaesemann__Beyond_JDBC_--_PGCon_2018_U0TBhRx7Y9o_59016_69450.png