background

New CVEs, Postgres Programming, JSONB, Advisory Locks | Scaling Postgres 76

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss new CVEs, programming Postgres, JSONB comparison and using advisory locks.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres we talk about new CVEs postgres programming,JSON B and advisory locks. I'm Kristen Jamison and this is scaling postgres episode 76.

[00:00:20] Alright, I hope you're having a great week. Our first piece of content is actually newPostgreSQL releases on the Postgresql.org website. So they have new releases including betathree of twelve. Now most of these appear to be due to four security issues that were identified,but also 40 bugs were addressed. Now two of these security issues involve SQL Server itself.One related to security definer functions which we've talked about in a previous episode ofScaling Postgres. And if you're using these you should definitely consult the documentation onthe best way to use them without causing a potential problem. And then the last one is memorydisclosure and cross type comparison for hashed subplan. And this one says for an attack tobecome possible, a super user would need to create unusual operators. The other two involveWindows installers and of course you also get a host of bug fixes as well. So go ahead andupdate your instance when it's best for your environment.

[00:01:26] The next post is postgres is the coolest database. Reason number five it cannot bebought out. And this is from secondquadrant.com and they've had various different reasonscoming up why Postgres is the coolest database. This is reason number five and they have linksto some of the other ones that they've been talking about. But this was kind of interesting talkingabout the structure of the PostgreSQL community and how it develops and why that kind ofmakes it pretty resilient and that it can't be bought out. For example, they use the example ofMySQL being bought out by Oracle and how that's not really possible because there's really noone company behind it. And it says it assigns copyrights to the PGDG which is the PostgreSQLGlobal Development Group which is a global community that cuts across countries, enterprises,people and cultures. Some other interesting things is something as important as the core teamthey say, which is currently comprised of five people. It does not have more than two membersfrom the same company and community conferences don't allow more than 50% representationfrom the same company on talk selection committees. So basically this just gives more evidencethat it is truly an open source project that cuts across a variety of organizations and individuals.And here are the links to some of the other reasons. So interesting blog post to check out.

[00:02:51] The next post is overview of server side programming in PostgreSQL. So this goesover all the different ways that you can do server side programming. The first area they addressare SQL functions, so you can create a function and make sure the language is SQL and youcan basically create user defined functions that do some sort of purpose like here's updatingitems and inserting into an audit table for example. Now they make a note here, the functionbody can only contain SQL statements, however, that means there are no flow controlstatements if while variables and the like. Now you can do that with plpgsql which is essentiallyprocedural level postgres SQL.

[00:03:38] So because the language is SQL, you can't really use these capabilities. But we'll seethat in the post further down. The next thing they talk about is C functions. So you can develop Cfunctions, as they say, to pretty much do anything. So you can create a function and then defineessentially where your entry point is talking about. You have a shared library here and the entrypoint being a sum. So you can call that function within some C code and then they go into theplpgsql functions. And again the language specified here is plpgsql and this does allow loopsand conditionals and variables and things of that nature. So it is more of a programmingenvironment. And this is included by default in PostgreSQL. And then there are other coreprocedural languages, so you can use Python Perl as well as TCL, but you may need to installsome additional packages in order to get that to work because it's not really included by default.For example, they were saying on debian you may have to do install this package in order to getthe Python working. But then they say there's also some non core procedural languages, sothere's some open source projects that have support for things like Java, Lua, R, et cetera. An --d they have a link to the list here. And then they're talking about aggregate functions and howyou can essentially create your own. And again they're using the SQL language to be able to dothat. So for example, this does a median and they do select medium from a students table. Andof course you also have user defined types, so you can basically create your own types. Andyou remember how one of the CVEs was about creating some sort of custom operator. There'sa risk associated with that. Well here they're literally creating an operator. The next area theycover are triggers and how you can set up a trigger to be before after an insert update, delete arow of a table on the truncate of a table, or instead of inserting update, delete a row of a view.So triggers perform some action when another action takes place. So their example here is afteran update on the items table, basically you're going to insert into an audit table and then theyhave event triggers which are similar to triggers because as they say quote while triggersrespond to DML events on a single table, event triggers can respond to DDL events on aparticular database. Then you can define rules which essentially lets you rewrite queries as theysay here. So for example, create a rule that on an insert into items do instead nothing. Sobasically don't allow it. Then they talk about stored procedures, which is new with Eleven, whichis essentially like the standard functions before, but these procedures allow transaction controlso you can do commits and rollbacks within them, whereas with a Plpg SQL straight functionsyou can't do that. It's all part of one transaction, but with procedures they enable you to have thistransaction control. Then they cover what they classify as a few other exotic things such asforeign data wrappers, connecting to another database, another postgres instance, or MySQLOracle, et cetera. How it's possible to create your own index types with upcoming PostgreSQLbeing able to create your own table access methods. They talked about logical replicationplugins used with things like logical decoding procedure, language handler as well asextensions. So of course this allows a whole host of functionality to extend what postgres cando. So it's definitely an interesting blog post. I encourage you to check it out if you're moreinterested in more options when it comes to server side programming in PostgreSQL.

[00:07:27] The next post is comparing postgres JSON B with NoSQL and this is fromCouchbase.com. So of course they're talking about NoSQL in the context of CouchDB, which isa NoSQL database. So in terms of comparing them, there's probably going to be a little bit ofbias. But whenever they talk about JSON B and different ways to use it in PostgreSQL, I alwaysfind that content interesting.

[00:07:54] So I like just looking through it just to kind of understand more how people arepotentially data modeling and using JSON B in the context of PostgreSQL or even NoSQLdatabases. Now of course they do talk about some disadvantages. Like they're saying the JSONB syntax is not necessarily friendly, but again, with a JSON path that's going to be coming, I findthat syntax much more appealing. And I included the link here to the JSON path feature that'scoming in PostgreSQL Twelve where you can do like syntax like equipment rings, get the firstitem of the ring array and get the name. So I find this syntax much easier to understandcompared to some of the other syntax that JSON b uses with our Curtain set of operators. So ifyou're interested in more content as it relates to JSON b, definitely a post to check out.

[00:08:50] The next post is postgres advisory locks with Async IO. Now, I believe Async IO andAsync PG are related to Python here, but they are talking about advisory locks and how they'reusing it as a solution to have only one process interact with a database on a particularconnection. And you can use here's the syntax that they use in terms of you just create anadvisory lock with a given name. So there's not a lot of PostgreSQL specific content in thisarticle, meaning code, so a lot of the code is Python and using these particular tools. But if youwant to know how someone is using an advisory lock in their use case. This is a pretty shortpost to check out and see how they're using it. The next post is doubling the sorting speed ofpostgres network types with abbreviated keys. Now, this is a post that talks abo --ut how they developed a patch for postgres to be able to speed up inet and CIDR types inpostgres. So it's more of a technical discussion. It doesn't give you guidance on how to improvethe sorting speed of PostgreSQL as it is, but it's what this individual did in terms of developingthe patch, in terms of achieving almost doubling of speed with inet and CDR data types. So ifyou're interested in the content behind this patch and a lot of detail into how it was developedand the rationale with regard to it, definitely a blog post to check out.

[00:10:25] Next post is automatically updating materialized views. Now, this is a short post, butit's basically communicating how to do it. Now, I don't see a lot of code with regard to thematerialized views because how they're doing it is through triggers. So they don't actually havethe code of the trigger, but they do explain some of the concepts of how you would need todevelop triggers to be able to update a materialized view incrementally. So again, this is a verybrief post, but it gives some ideas conceptually of how to automatically update materializedviews. And this is from Pgsqlpgpool blogspot.com.

[00:11:04] The next post is PostgreSQL Graph Search Practices 10 billion scale graph withmillisecond response. Now, this is from Alibaba Cloud and they're talking about using graphsearch using a CTE syntax that you can easily implement a graph search in terms of in depthsearch, shortest path, point, edge property and so on. So this is a pretty long post that includesa lot of code in detail about how to do this. So if you have a data set that can be represented in agraph such as this, this might be a blog post you want to check out to see how you couldpotentially use CTEs and recursive CTEs to be able to achieve the similar type of response timein your application.

[00:11:54] The last post is waiting for PostGIS three separate Raster extension. So again,upcoming in PostGIS three, they've broken out something that was included by defaultpreviously, but from what they're saying here, breaking out the Raster functionality allowspackagers to more easily build strip down just the basics postgres without also building theRaster dependencies, which include the somewhat heavy GDAL library. So just something tokeep in mind when you upgrade PostGIS to version three that this functionality is a separateextension. Now that does it. For this episode of Scaling Postgres, you can get links to all thecontent mentioned in the show Notes. Be sure to head over to Scalingpostgres.com where youcan sign up to receive weekly notifications of each episode, or you could subscribe via YouTubeor itunes. Thanks. --

episode_image