Standard SQL Gap Analysis
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.
Winand, Markus
PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance. This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB. PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance. This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB. The comparison uses a set of conformance tests I use for my website modern-sql.com. These tests are based on the SQL:2016 standard and attempt to do a rather complete test of the requirements set out in the standard. This includes the correct declared type of expressions as well as the correct SQLSTATE in case of errors (teaser: nobody seems to care about SQLSTATE). This presentation covers two aspects: (1) features not supported by PostgreSQL but by other databases; (2) features available in PostgreSQL that are less complete or conforming as in other databases.
 So, welcome to my talk about bad things about Postgres. So, why are you here? That's the interesting question. And why did this talk ever get accepted? I have no idea. So, before I show you what there are some missing features or less complete features as compared to other databases. That's the important part of this talk today. I'm only presenting features that do exist in other databases or that work in a more standard conformant way. Otherwise, it would be pointless because there are so many features that are supported, but not a single database. I mean, I could list them, but that's pretty much pointless. The exercise in this gap analysis was to find the gaps, actually. So, where does this data come from? The background is that a runner website called modernsql.com. Who has ever seen that page? Oh, great. So, what I'm doing there, the aim of this web page is to teach the new SQL features to developers. That's the primary aim, where new means pretty much everything that was released after SQL 92 and, surprise enough, also a few stuff that was done in SQL 92 already. But there's also the second point I have to take care. I also want to make it visible to developers which database supports these features. This is pretty much where this data comes from. If you look at modernsql.com, you will see matrixes like this. Here I've took one where Postgres is better than all the other ones. That's the only time you see it like this here. Sorry. So, how do I come to this red X and this green check marks? They are based on actual test cases. I used to start making them by reading the docs, but that was pretty much at the end. So I now have test cases. And whenever there is a red X, it basically means a test case was failed, one or more test cases were failed. And when there is a check mark, it means all tests were passed. I come up with these test cases by actually reading the standard, the real standard document. Yes, that's as bad as it sounds. So that also means the standard defines so many aspects of stuff. And I'm not testing all of this every time. So the depth at which I'm testing, it greatly varies. You will see some examples where I look at really small, small, small details. Like the first example I have for you today for the gap. And in other cases, I'm just doing some kind of screening to see, okay, which database can do that. That's just what I have at the moment. I aim to go into the details for all the features. But yeah, well, if you know the standard, it will take me one or two lives, I don't know. One last word for brevity. I'm using the word wrong to mean not as according to the standard, and more importantly, not as according to the standard like I understand it, which might be wrong as well. So that's really just, it doesn't mean it's bad. It doesn't mean it needs fixing. Some do need fixing. Some might be bad. But just because I'm saying wrong doesn't mean to imply anything, any judgment. It's just from the standard's perspective, not conforming to the standard. Okay, let's get started. I start first by features that are available in Postgres, but have some gaps regarding to other databases when you look at the details. And the first one is extract. You won't believe that there is a small gap in how Postgres implements extract. And it's a really, really, really small one. So this is how I can demonstrate at what kind of detail I'm looking if I can. So you see it's actually checkmarked everywhere, but there are some food nodes, and the food node says it returns an approximate numeric type. And that's even documented. So it's double precision. But if you look into the standard, then it clearly says that in both cases, it has to return an implantation defined exact numeric type. So this is the gap. And this is really one of the smallest things. And if you look back at the matrix, I did not even, I still checkmarked it fully green because this is so small, it's not worth actually mentioning. Yet I'm showing it here to show you at what detail I aim to look at. So next one. This is one I think it's well known in the community. It's also documented. Respect nodes and ignore nodes in some window functions, first value, last value, and so on. And if you look, there are some databases that support that. So there is technically a gap. That's all what I mean to say here. There's a patch for that. I don't think, it's there since when? Okay, so there's a patch which is. Okay. Okay. Okay. Yeah, that's a good point. It doesn't because the filter clause only applies to aggregate functions and those don't qualify as aggregate functions. Yet I think it might be an interesting idea to add the filter clause for the non-aggregates as well. That would kind of. Just an idea. So one more statement. Most of these matrices are based on some 11 snapshot. It's not the beta one, something like a week before the beta one. So it's most definitely know the window functions get a big extension with Postgres 11. So this was already considered there. So I don't bring up the range as a gap because it's that let's consider that fixed as long as it doesn't get rollbacked. Okay, so there is a patch but it's not applied for 11 and so there is a gap. Yeah, also the documentation agrees. It's well known. Everybody knows that. I mean, it's no surprise. Yet it's a gap which comes out of this analysis. Another gap which you may or may not be aware of is distinct aggregate functions when you use them as window functions like distinct count or distinct sum or whatever. There's only one database supporting that. That's the way it is. Go ahead. Okay. So there's nothing more I can say. It's a gap. That's what it is. There's another gap when it comes to fetch first, fetch next and so on. You know, this is basically what the limit clause does but a little bit more advanced. Let's put it that way or at least more keywords for the same purpose. So what's the gap in there? You see it here actually. So there are two times these red X's here and they are about the options percent and with ties because the fetch first clause actually also allows to say I would not like to fetch like 10 rows. You can ask for 10% of the result set. And you can also ask for like 10 rows but please include ties in case the order by clause does not establish an absolute sort order. And in the form like it is defined in the standard, only the Oracle database has supported at the moment. But if you look at SQL server, there's also one note because SQL server does not have the fetch first clause but it has the top syntax for that. So it reads select top and then you give the number there. And for this top syntax, it actually supports both of these variants. So you can ask for top 10% in SQL server and you can also ask for top five rows with ties. So that's why I want to highlight that the functionality is there. Although it got a red X because the syntax is not the same. And that's documented. In the list of missing features of Postgres, it's documented. There are two feature IDs assigned in the SQL standard and it's documented that they are not there. Okay. Functional dependencies. That's a fun one. Or it's actually a sad one. If you just look at the matrix, it might be the biggest surprise you have ever seen. Yeah. That's my SQL. It's not a mistake. That's really my SQL has a pretty good support, the best support I've ever seen for detecting functional dependencies. Postgres ranks in second, as you can see. So what is it about? Is it really important? I don't know. It's documented as well. It says partially supported. I agree with that as you can see in the slides and in the checkmarks. I have just one example for you where I have assigned the light green checkmark to Postgres. Because what is it about functional dependencies? It's basically knowing that one, a set of columns depends on the values of a set of another columns. And if you look at this query, if you just look at the table T1 for the back. If you look at T1, I'm doing a group by the primary key of T1, but I'm also joining T2, also via the primary key to another primary key. And finally, I'm selecting something from T2, some other column. And if you have proper functional dependency checking, this is valid because it turns out that if you, the T1PK implies the result that the T1B has only one distinct value. So it's valid if you have this optional feature implemented in your database. And this is basically the one test case which failed here. So it didn't give it a solid green checkmark, but the light green checkmark. And this is how functional dependencies look in the standard. That's just a table of contents. So it's read across 10 pages, which are really hard to digest. And the best thing, it's not even complete. It still leaves room for window extensions. Like what I can think of is if you're doing some row number together with the partition by class, if the partition happens to be a primary key, then you can also have new functional dependencies. The same is true for ordinality, if you do unnest or anything like that. So there's still room for window extensions there. Okay. This was the first part. The first part is features that are there, but in some way less conforming and less complete than in other databases. And it was really hard to find those things because generally said, Postgres is leading in standard conformance. Now let's look at the second part. Features that I'm missing at all. And my most favorite feature is row pattern recognition. Who has ever seen that? Oh, one at least. Did it change your life already? I think it's pretty interesting. Oh, pretty interesting. If you think window functions are awesome, then yeah, you will like that. I think personally, this is the most important feature for the next decade maybe. It's really at least as important as window functions. I will show you what it does. The example I have chosen here is like processing a log file, a stream of events. Let's say this is some web log file where you have hits to some web pages. And what you would like to do is to do some statistics on that. How many sessions do I have in this log file? What's the average duration of one of these sessions? These are the two examples which I will now solve using this new feature. And you can see there there's this 30-minute threshold. So I define one session as long as the next hit is within 30 minutes of the previous hit. And this is how the query looks like with the new feature. And in good old tradition of SQL, it makes most sense to read it up from bottom up, not the other way around. So what is it? The general idea of this is that you would like to combine rows in some logical way very much like group by does, but in a more flexible way. And the more flexible way means by using regular expressions to match them, to match rows together, to mark them as they belong together. And once you have matched them in one whatever group you want to say, you can post-process them. You can then apply a group by you can do similar things like window functions and so on. But the really important thing is the idea is apply regular expressions over a sequence of rows to identify matches. And so you can then process some statistics and whatever over these matches. And if you think of regular expressions, they were characters. They were based on characters. What they are matching are characters. But what we are matching here, these are rows. And in a normal regular expression, a character usually stands for itself except of those few meta characters like star and dot and so on. But we don't have this here in SQL. We need to define some names, how we can define a row. And this is what we have to define class for. So what we can assign there, we can have some names. Like what I have here is continuation, and I assign some condition. Like when the timestamp is less than the previous timestamp, and you see this previous is pretty much like lag for window functions, plus interval 30 minutes. So when it falls into this interval, then this row can be assigned the property of being this cont tag. And this is something you can then use in the next step, which is actually the regular expression inside this pattern. What you see there is regular expression syntax. The main difference is just not characters, but these variables which we have just defined. And there's one odd thing I must admit, if you see any, or basically if you use some name in here, you see the cont is the one I've defined down there, but the any is not defined. And the strange rule is that if it's not defined, then it matches everything. So this means the any will pretty much match just any row, but only once, because it's a regular expression, there's no quantifier there. And then it will start to try matching these cont rows. And it will succeed as long as they fall within this 30 minute period, as long as this condition here applies true. And because there's a star, like in regular expressions, it will make it 0, 1, or however often. And this is the star you know from regular expressions. It could be a plus as well. The dialect which is used there is defined in the standard itself, so it's not reusing some postings or whatever dialect, and it's not actually as powerful, but it's still, most of it is still, the useful stuff is there, I would say. Yes, question? Do you know if it's a direct subset or is it conflicting against regular expressions? I think it is a subset. It does have some extensions. So it does have some strange features which are not in any other regular expression syntax. It's not dealing with characters anyway, that star doesn't apply to the t, it applies to the conch. Exactly, exactly. Regular expressions. Exactly. That's the important stuff. It's not on characters. Therefore, you can also divide spaces irrelevant there. So it's not, I mean, it's not meaningful to ask if there's a subset of regular expressions involved, so it's a completely different beast. I mean, I think the question I was trying to drive at is, if it was to be extended by the committee in the future, would it be extended in some manner that would be more consistent with what we think of as regular expressions? I mean, I can give you one example which is missing in this subset. It's character classes. So you cannot do character classes, but you can do an OR. You can use the pipe. You can also use brackets. You have the greedy and non-greedy matching. Of course, it doesn't make sense to have some shortcuts for character classes too, like upper or lower or something like that. It just doesn't make sense. But as far as I've seen, there are some extensions, but as long as these are not characters which you are matching, it doesn't matter. I have it in one slide. One strange feature. So that is the regular expression. And here comes one other interesting thing. Here you can see, in the result, I would like to have one row per match, which implies you could also say all rows per match. And this is the difference between making a group by, actually, or working more like in a window function fashion. And there is the measures clause, which pretty much translates to the select clause. So you can define some columns in there. And the way I'm doing it here, I'm using the last and first functions. And I have no idea why they are called last and first and not last value and first value like the window functions. It's the way it is. And they can access pretty much any rows in this matched set of rows. Another interesting thing, which is not actually shown in this example. Sorry. These variable names, which we have defined in the define clause, we can access them later on. If it has matched, you can say, for example, the last out of the cont matched rows. In this particular example, I could also have used any.ts, because any is the first match. It only matches one row. So it's pretty much the same like first t.s. And that's pretty much the query. There's an order by clause. You can have partition by clause. No big deal. Other than that, at the end, I'm just doing the statistics I've mentioned there. So here I'm doing average and count of the sessions to finally get the result like I described it before. So what I like about this feature is the endless possibilities. You have in one feature which can do quite a lot. So I've shown you you can do group by because you can say one row per match. You can work like with the over clause because you can say all rows per match. You can have unlike window functions. You cannot define a sliding window, but you have at least two semantics you can choose from. The final semantic, which is pretty much like from unbounded preceding to unbounded following. And the running semantic, which is from rows unbounded preceding to current row. So the two use cases you use most often are there. You have features like you otherwise find in having or work clauses because this you can encode this in the definitions and in the pattern clause. You can even combine group by and over. So I have an example in other slides I will show you where some rows are passed through one by one and another set of rows is condensed into a single one. So you can combine this in one go. Data-driven matching, like the bin fitting problem can be solved with that because these definitions you can also use like a sum. To match as long as the sum is below some threshold, which can be some dynamic threshold. So that's really wow powerful. And another thing is you cannot only filter out rows. You can even duplicate rows to some extent. So one example I have in my slides is if you have some scheduling system where you have room bookings from two, from two, from two. You can have one match recognized clause that fills you the gaps. It makes you new rows for the missing gaps. So you get more rows out of this clause than you have sent in. That's also possible to some extent. So I really think it's super awesome and a little bit complex. But once you get used into the thinking, it's really wow. All this nesting of window functions. If you have a query that nests window functions, I bet this can solve it with a single expression. So can you use it yet? Yes. There's one database that supports it. If you're interested in this feature, I have some resources for you here. There's a free technically report by ISO. Go read it if you're interested in this feature. It gives you good use cases. It's free. And it's not like standard talk. It's like normal talk. Do you know, did Oracle like? They implemented it first and then it came into the standard. Did it exist before they got standardized? Yes. It was in Oracle 12. And it came into the standard with 2016. So it was in Oracle working. And I think there might be even some gaps. It's a kind of cheating way to get it ticked. Yes. Yes. But there is still room to outperform them. Because they are only supporting one out of the three features that we find in the standard, as you can see there. Because what I've shown you is the match recognized clause, like you use it as a table function, similar to the table function. But the standard also defines it that you can use it as a framing clause of window functions. So that instead of saying unbounded preceding and whatever, that you can put a pattern in there to say over which how the window moves, how the frame of the window basically looks like. And this is not implemented by Postgres. So go ahead. You don't show it. There are no databases. What's that standard for all? Pardon? You have a list of databases that don't support it. Yes. I think there are some less-esqually databases that support this concept. OK. All right. So there are some out there that have some. Yeah. But don't ask me for names. OK. I've seen it, but not in the big ones. OK. So read this paper if you're interested in this feature. And then go to the standard and make sense out of the standard. But don't go to the standard first. It seems like a little bit related to the stream SQL stuff, which is, I think, there's like a working group that's going to pop out of standard sometime quite soon, apparently. OK. That seems kind of... Yeah. I don't know that. So I'm not affiliated with the standard committee at all. I'm just buying the standard and trying to make sense out of it. I always say, other study the Bible. I study the standard. I don't know which is more pointless. You can fill a life with both of them. OK. So read this one. Another recommendation I have is, of course, my own set of slides. I've mentioned some use cases before. You can find them in these slides. And because Oracle is the only database that supports that yet, there is one guy, one Oracle, a consultant who has quite a lot of stuff on material and that is you ashen, go to his blog and look for match recognize, which is the keyword. And there you can find some other interesting use cases and so on. Maybe one last word. All the benchmarks I've done when I was comparing the window function solution to the match recognize solution. In pretty much all cases, the match recognize was faster. And that even applies to such simple cases like top end per group. So you have a category and you're usually doing, you know, row number partition by and then an outside query where the row number is less than something. Even that was faster with match recognize because it's basically just career. Then match anything. Curly brackets 10 or whatever. And that's it. And the partition by class maybe. And it was faster than the window function implementation. So I think there's a lot of potential in there. Then the next one. Temporal and by temporal tables. Ah, yeah, there are some discussions recently. I'll first go to what is it about. And I give you another hint where you can find good information. Of course you can read the standard, but you can also read this paper which was released a few months after it was put into the standard and it's also good paper. It's a paper you can download. It's for free and it gives you quite comprehensive answers on the ideas. It also gives you an example. You will see syntax there. Go read this paper. If you don't have access to the standard, this is the next best resource to look at. Don't look at the documentation at other databases. Take this. Or the standard, of course. But also read this first because you start trying to make sense out of the standard. So what is it about? There are two distinct concepts. System versioning and application versioning. And the idea is that this model two different time dimensions. The system versioning is there to model when we learned about some change in the reality. While the application versioning is meant to model when some change happened in the reality. An example is when some couple marries and maybe a name changes. It happens at a date of marriage. But when you learn about it, this is pretty much later usually as a company, as a bank or whatever. And these are the two concepts to model these two things. And the system versioning is named system versioning because the system manages it. Because it's easy enough to just look at the system watch and use this time stamp to recognize when we learned about something. And the application versioning is consequently called application versioning. Because the application has to provide the proper time stamps which might be in the past or even in the future. Like if you have a fee schedule, you would like to have maybe some discount as of tomorrow starting. Then you can put it with time stamps in the future. Both of them in the standard can be used on a table level. And they can be intermixed as you like. So you can have none of them or you can have both of them or only one of them as you like it. In the DDL, both of them require, according to the standard, explicit collumes which give you the start and the end time stamp of when this row was valid. So both of them required that explicitly. And on top of that, they require something which is called a period, which logically combines that in what we know as a range type on some time stamp thing. In system versioning, you have to use the generate always clause to tell the system take care of that. The system sets these time stamps for you. The period name is fixed. It's system underscore time. On the application versioning side, you can use arbitrary columns, of course. And you can also use arbitrary period names, but you have to use that. The DMLs, like in the update delete merge, system versioning takes care of that. It's mostly transparent to the application. I say mostly because the two collumes you have to add to the table, they are visible. And some databases to make it 100% transparent offer some invisible or hidden collumes, but that's not a feature from the standard. On the other hand, the application versioning is completely up to the user. So if you want to have only the current row, you have to write a where clause for it. The standard does give you, however, some tools to make that work. And you have to take care of constraints as well in application versioning. But it gives you a tool, like now, and primarily can read like, primarily ID, comma, without overlaps and then the name of the period, which is very much like an exclusion constraint in Postgres, like we have it today. So the standard provides framework. And here, for example, we have it on the right-hand side for the application versioning. We have new predicates, like contains, which work on these periods of this, what we call range types. On the other hand, in the system versioning, we have an extension of the from clause. In the from clause, in the system versioning, per default, we only see the current data. We don't see historic data until we ask for it. And the way you ask for it in system versioning, according to the standard, is you say from table name and then for system time, and then you can say like S of three hours ago, like current time stamp minus interval three hours. Or you can say between to get everything, and there's also from and to syntax. So this is the way you work to work with system versioning. But according to the standard, these are two distinct things. The system versioning, we're using this for system time S of stuff, while for application versioning, we're using the where clause. Recent, more or less recent discussion I found on the hackers list was the S of queries. This was around Christmas time. For the application versioning, there was a page sent like a week ago. I think there hasn't been much discussion about that yet. But I think this is this is the current state of art I know about Postgres. Okay, so this is pretty much distinction system versioning application versioning. Let's look in these two separately. So which databases are supporting system versioning S of right now? And yeah, you can see quite some checkmarks there. The first interesting one is actually this one MariaDB. It was released last week. So MariaDB is the MySQL clone, which is not already owned, but run by the kind of old MySQL people. And yes, they have released it. I did test it. I found only one issue that's this last X in there. But otherwise, it's pretty close to what the commercial databases do as well. There are even some aspects which are like more than in other databases. And I have to tell you something about Oracle. I marked them straight red here. If there's an Oracle representative, they will say, but, but, but, but we have S of. Yes, but it's something different. It's not in the idea of how the standard sees system versioning. The idea of is you can access the undo lock to get data from like a few minutes ago in case it's still there. Okay. The syntax is similar, but yet a little bit different. And the idea of having some audit trail or something is just not there. It's similar syntax, similar idea, but still something different. And if you are considering working on these, who's considering working on these? Ah, one brave one. Another one. There's a few hints. The system versioning in the standard, it might have one or two gaps. First of all, schema changes are just not supported by the standard. When you have a system version table, pretty much every alter table will fail, except alter table drop system versioning. So the way to evolve such tables is to, according to the standard, is to alter table drop system versioning, which also drops all the historic rows. So they are gone. You can back up them first if you like. Then do the DDL you want to make and then again alter and enable system versioning. Yeah, it's not nice. It's a gap. No retention. The standard doesn't give any framework like you could define like five years. I would like to keep that or whatever. It's even the opposite. It's directly says it's one of the aims that it cannot be filled with that. There is, I'm from Europe and I'm presenting these features to developers. And the most common question, the first question I've got in like the last six months was, is this GDBR compliant? And the answer is no. Because according to the standard, in the standard, you cannot have a four system time S of for delete. You cannot fiddle around with historic data. That's the point. It has audit character. Okay. So this is just not in the standard. Pretty much every implementation I have seen does offer a solution for that, but it's not standardized. The first time I got asked is it GDBR compliant, I was like, hey, is this kidding this guy? But no, it's real. Question? The implementations that you've seen in the other database systems that provide this, are they all consistent? No, they provide it. MariaDB has an extension for the four class in delete. So you can specifically delete something. In SQL Server, it works pretty much like described by the standard except, so you disable it. It does not drop the historic records. You can then delete one specifically and re-enable it and all of this in one transaction. Okay. In DB2, it's also, you can disable it and you have a separate history table which is not dropped if you disable it. And then you can fiddle around with that and then re-enable it. But I don't think it works in one transaction in DB2. So there's no standard committee to change it? There is no, it's different everywhere. There's no consistency. Yeah, that's true. So if you ask me which one to follow, I would personally take the MariaDB because it's the most convenient. And what MariaDB also got right is they have an extra access right for that. So you need some extra grant. So you can... Okay. Okay. And here is, I think, the worst issue. For system time, if you work with system versioning, you have this, you can ask for system time as of. But this works only on base tables. Not on views. And there's not even a session setting in the standard defined. The session setting is available in some of the databases. But it's not like you can now say, old session universe as of that time stamp and every system version table would be used like that. That's just not there. You can make an extension, but the standard doesn't tell you how to do that. So you need to make unique to what has all explicitly on every layer? From every from. It belongs to the from and it doesn't apply to views. So if you have a view which previous some system version table, you cannot use the view for as of queries. According to the standard. Yeah. Does MariaDB extend that at all? I think they have a session setting where you can say for all system version tables as of something. It seems like something you want to be able to push down into the view list. Yeah. In some sense. Single variable like that. If you want to do it to view and you have a table involved, you might want to do as of this time and as of this time differently. It's possible according to the standard because you have to say it for each from just right. You can have a different. I think this is something you definitely need. I think it's an omission unreasonable omission in the standard. So therefore I'm listing it. And the last thing which is somehow questionable in the standards is transaction time. So this generate always as clauses. They assign the transaction time. So you have the same value within all the changes you're doing one transaction. But the transaction time is the commit time. That means if you have a long running transaction, you're asking for S of some specific date in the past. It doesn't mean you get the same result as of that time. Because it might have not have been committed and the transaction time is usually the first data change you run in a transaction. So there are also extensions by some windows where you can transaction granularization where you can map it the transaction ID to the commit time. But it's something to keep in mind. I think the standard is written flexible enough so that you could use the commit time for that. But it's just a little bit, yeah, in direction. You need the interaction because if you do the changes first, you don't have the commit time at that time. So just so I understand when is that time captured? The time is captured. So the standard says it is assigned at the time the first data change runs in a transaction and it is immutable from then on. But it does not say that it needs to assign that time. A time. I think you could. It has to be immutable. You assign a time of X. Can I ask what it is? What it is? So I begin a transaction. I do the first thing that touches something that's in a system version table. You could query the system version table. The columns there. As soon as I do something, I can actually see that. I can see what's being assigned. But it might be another week until I commit. Yeah. So technically, if you do the insert, you can write after the insert, run a select on it. And then it will be the most current version you see. So you can, these are explicit columns from two. You can look at them. So I can consider any guidance in terms of like current time step in Postgres will be the same time step throughout the transaction. And now that's actually when the transaction is complete, correct? Yes. Or when the first data changes done. Though in Postgres it is in the transaction. So is there intended to be consistency between this concept of transaction time and say current time steps? In the standard wording, it's not consistent. Transaction time is indeed some term which is used in the standard for the purpose of system versioning. Right. It's a new term. It's in. Thank you. I'm running out of time, but let's go on. Notes from current implementations I've seen. The most common way to implement system versioning is having separate history tables. DB2 and SQL Server do it that way. What I find quite interesting idea is how MariaDB does it. You can have a single table which may be partitioned by a new magic way to partition current rows from past rows. So you may have it in a single table if you choose to, but you can also partition it. And you can also partition it like very old rows to cope with the question of data retention. You just drop the very old partitions. So it's not like two partitions. You have one current partition and then you can have as many history partitions as you like. And finally mentioning that Oric is accessing the undo log for that, which is pretty much a different idea. Application version tables. That's a little bit simpler. Who supports it? Yeah, DB2. But if you look at the notes, you will see that I've put there some notes like Postgres use range types for that. And this is also what the patch which is currently more sub-inded a few days ago is basically about. It's a different syntax for range types. I think most of the functionality is already there in the Postgres core. It's just to make it accept the syntax like outlined by the standard. And one of these syntax extensions are the period predicates. I've mentioned them before like overlap equals contains and all of these which have equivalent in the range types already. I think. Okay. Now a few features which I don't explain in so much detail. I think those two features which I've shown you now, the row pattern recognition and temporal databases. These are the big features which have huge demand in the scene in the next foreseeable time. These are the features which people asked me about when I present about this. Generated columns. You might know them already. This is really an uncommon picture here that Postgres falls out of the general market here. What other databases uses for is for example also function-based indexes. So many databases don't support expression in the index definition. Instead they put an index on a generated column. From standard perspective it's pretty similar. A computed column, a generated column can be used almost everywhere where you also can use base columns. That means you can also use them in constraints definition and so on. So these are the use cases from standard perspective. If they are good or not, I don't know. Note that the generate always as clause is reused between identity columns and also the system versioning. Here comes one. I would really like the next feature. Who has ever heard of that? Combine data change and retrieval. Is there a reason why it's done differently in Postgres? I'll show you. It's writable CTs. Who knows writable CTs? There's a standard way to do that. It looks just a little bit different. So you can have delete update, insert, merge in subqueries in the standard. You just have to prefix it with something old, new, or final, table. And what you're getting then, you can query this data and you can do whatever you like with that. This example is valid SQL and it copies data from some source table to some target table. If you do the same with CTs in Postgres, it would probably look like that. You have inside the CT, you have to delete, you're returning everything, and then outside in the main query, you're inserting to that. Pardon? I'm just thinking about it in Postgres that there was two very different. I mean, I get your point and look like we should be able to support it. There are some differences, at least some which I have found. There are three modes. You have seen old table. For delete, you can of course only access old data because there is new data. But you can also, for update and insert, you can also ask for new data. But here comes the gotcha. The new data is not actually the final data, and not even the final data is the final data. What it is, is actually there is defined in the sequence of processing, there's one step defined where it takes the data. And this is before, after triggers run, and this is also before some constraint violations run. So the point is, if you ask for new data, you're not necessarily getting the data that is actually written. And then there is the final, which kind of sounds like it would be something different, but final gives you the very same data like new does, but it fails in case there is some data changed on afterwards. So if you get the result back for final, you will be sure that this is the data that has been written, otherwise it would fail. Yeah. As opposed to as in writable cities, give you pretty much the data which is stored in the table after it was run, which is maybe more useful. So I would probably want another one which does it, like Postgres does it. Yeah. Can you get the old data and the new data? For update. Not at the same time. Not at the same time. Yeah. So you can have it in one. You can have multiple of those in one query basically. But there will be different statements. Those would be different statements. Yeah. You cannot get both in one step. But then you need to update it twice. So you would... So there's also a question. Why isn't there one for app industry? I don't know. I'm not affiliated with the standard. But the likely answer, if you look at the support matrix, is because IBM did not have that at that time and they pitched their implementation into the standard. So DB2 can do that. But DB2 has also some limitations. So it doesn't support merge in there. Okay. Postgres doesn't support merge. You can see why because merge produces old and new. You don't even know which. It depends on which row. You kind of want to deal with both in order to get out of this support. Yeah. There is definitely some more complexity than in in-insut update delete. No question. What I find interesting, the example which I've shown you, which is valid SQL as far as I know, doesn't work in DB2 because in DB2 the main query must always be a select if you have in sub-queries some data change statements. But you can bypass this by chaining with clauses. In the end you make a dummy, dummy select, select where one equals zero. Okay. I'm actually running out of time. I do have some things I will take through. Partition join is filling gaps in time series. As long as you have only one time series, it's easy. If you have multiple groups, this is the syntax we have now in the SQL standard. So you can say partition by and then do the gap filling so it's doing the partition-partition only oracle supports it. List-ag, which is very much like string-ag. Distinct data types, which is core SQL but hardly supported. And mentioning that two work in progress things are merge and chasen. I've listed them in separate section because I think the patches are in such a condition that there is a good chance they are applied at some point in time. So at the moment for chasen everything is red. But once I apply the set of patches, pretty much, it looks pretty much good at the time. I didn't look into detail. This is really preliminary results. I plan to have a closer look and report any things I find in there. So this is the overview and this is the last slide I would like to go through in rush. So whenever a new database release is coming out, like MariaDB or Postgres, I aim to publish something about that release within a few weeks on my website. That means at that time I will have this matrix for the new features you are touching. But this is pretty late if you want to have feedback. I usually start to prepare these things and start to test these things when there's a public beta, like now for Postgres. But that's also pretty late because it's already committed. The first time I usually notice that there's something new coming to Postgres is when Depe's writes about it, waiting for something. This is my trigger basically. It's also pretty late because it's already committed. So if you want to have my opinion on some of these things, please just ping me. I don't want it to hack us, but you can reach me. Contact is interestingly supposed to be shown here, but it's now missing. So you can see it actually here or not. So you can find me on Twitter. There's also an email address, but you can find me just over modern SQL. There's also contact and so on. Please ping me. You have to ping me. I will try to help you as good as I can, but you have to ping me because I'm not monitoring the development until it is really good. I'm reported by Depeche as waiting for. So sorry for overrunning five minutes. We have had some questions. If you have more questions, I'll be here till like today. Reach out for me or reach out via Twitter, email, whatever. Thank you.
 So, welcome to my talk about bad things about Postgres.  So, why are you here?  That's the interesting question.  And why did this talk ever get accepted?  I have no idea.  So, before I show you what there are some missing features or less complete features  as compared to other databases.  That's the important part of this talk today.  I'm only presenting features that do exist in other databases or that work in a more  standard conformant way.  Otherwise, it would be pointless because there are so many features that are supported,  but not a single database.  I mean, I could list them, but that's pretty much pointless.  The exercise in this gap analysis was to find the gaps, actually.  So, where does this data come from?  The background is that a runner website called modernsql.com.  Who has ever seen that page?  Oh, great.  So, what I'm doing there, the aim of this web page is to teach the new SQL features to  developers.  That's the primary aim, where new means pretty much everything that was released after SQL  92 and, surprise enough, also a few stuff that was done in SQL 92 already.  But there's also the second point I have to take care.  I also want to make it visible to developers which database supports these features.  This is pretty much where this data comes from.  If you look at modernsql.com, you will see matrixes like this.  Here I've took one where Postgres is better than all the other ones.  That's the only time you see it like this here.  Sorry.  So, how do I come to this red X and this green check marks?  They are based on actual test cases.  I used to start making them by reading the docs, but that was pretty much at the end.  So I now have test cases.  And whenever there is a red X, it basically means a test case was failed, one or more test  cases were failed.  And when there is a check mark, it means all tests were passed.  I come up with these test cases by actually reading the standard, the real standard document.  Yes, that's as bad as it sounds.  So that also means the standard defines so many aspects of stuff.  And I'm not testing all of this every time.  So the depth at which I'm testing, it greatly varies.  You will see some examples where I look at really small, small, small details.  Like the first example I have for you today for the gap.  And in other cases, I'm just doing some kind of screening to see, okay, which database  can do that.  That's just what I have at the moment.  I aim to go into the details for all the features.  But yeah, well, if you know the standard, it will take me one or two lives, I don't know.  One last word for brevity.  I'm using the word wrong to mean not as according to the standard, and more importantly, not  as according to the standard like I understand it, which might be wrong as well.  So that's really just, it doesn't mean it's bad.  It doesn't mean it needs fixing.  Some do need fixing.  Some might be bad.  But just because I'm saying wrong doesn't mean to imply anything, any judgment.  It's just from the standard's perspective, not conforming to the standard.  Okay, let's get started.  I start first by features that are available in Postgres, but have some gaps regarding  to other databases when you look at the details.  And the first one is extract.  You won't believe that there is a small gap in how Postgres implements extract.  And it's a really, really, really small one.  So this is how I can demonstrate at what kind of detail I'm looking if I can.  So you see it's actually checkmarked everywhere, but there are some food nodes, and the food  node says it returns an approximate numeric type.  And that's even documented.  So it's double precision.  But if you look into the standard, then it clearly says that in both cases, it has to  return an implantation defined exact numeric type.  So this is the gap.  And this is really one of the smallest things.  And if you look back at the matrix, I did not even, I still checkmarked it fully green  because this is so small, it's not worth actually mentioning.  Yet I'm showing it here to show you at what detail I aim to look at.  So next one.  This is one I think it's well known in the community.  It's also documented.  Respect nodes and ignore nodes in some window functions, first value, last value, and so  on.  And if you look, there are some databases that support that.  So there is technically a gap.  That's all what I mean to say here.  There's a patch for that.  I don't think, it's there since when?  Okay, so there's a patch which is.  Okay.  Okay.  Okay.  Yeah, that's a good point.  It doesn't because the filter clause only applies to aggregate functions and those don't qualify  as aggregate functions.  Yet I think it might be an interesting idea to add the filter clause for the non-aggregates  as well.  That would kind of.  Just an idea.  So one more statement.  Most of these matrices are based on some 11 snapshot.  It's not the beta one, something like a week before the beta one.  So it's most definitely know the window functions get a big extension with Postgres 11.  So this was already considered there.  So I don't bring up the range as a gap because it's that let's consider that fixed as long  as it doesn't get rollbacked.  Okay, so there is a patch but it's not applied for 11 and so there is a gap.  Yeah, also the documentation agrees.  It's well known.  Everybody knows that.  I mean, it's no surprise.  Yet it's a gap which comes out of this analysis.  Another gap which you may or may not be aware of is distinct aggregate functions when you  use them as window functions like distinct count or distinct sum or whatever.  There's only one database supporting that.  That's the way it is.  Go ahead.  Okay.  So there's nothing more I can say.  It's a gap.  That's what it is.  There's another gap when it comes to fetch first, fetch next and so on.  You know, this is basically what the limit clause does but a little bit more advanced.  Let's put it that way or at least more keywords for the same purpose.  So what's the gap in there?  You see it here actually.  So there are two times these red X's here and they are about the options percent and  with ties because the fetch first clause actually also allows to say I would not like to fetch  like 10 rows.  You can ask for 10% of the result set.  And you can also ask for like 10 rows but please include ties in case the order by clause  does not establish an absolute sort order.  And in the form like it is defined in the standard, only the Oracle database has supported  at the moment.  But if you look at SQL server, there's also one note because SQL server does not have  the fetch first clause but it has the top syntax for that.  So it reads select top and then you give the number there.  And for this top syntax, it actually supports both of these variants.  So you can ask for top 10% in SQL server and you can also ask for top five rows with ties.  So that's why I want to highlight that the functionality is there.  Although it got a red X because the syntax is not the same.  And that's documented.  In the list of missing features of Postgres, it's documented.  There are two feature IDs assigned in the SQL standard and it's documented that they  are not there.  Okay.  Functional dependencies.  That's a fun one.  Or it's actually a sad one.  If you just look at the matrix, it might be the biggest surprise you have ever seen.  Yeah.  That's my SQL.  It's not a mistake.  That's really my SQL has a pretty good support, the best support I've ever seen for detecting  functional dependencies.  Postgres ranks in second, as you can see.  So what is it about?  Is it really important?  I don't know.  It's documented as well.  It says partially supported.  I agree with that as you can see in the slides and in the checkmarks.  I have just one example for you where I have assigned the light green checkmark to Postgres.  Because what is it about functional dependencies?  It's basically knowing that one, a set of columns depends on the values of a set of another  columns.  And if you look at this query, if you just look at the table T1 for the back.  If you look at T1, I'm doing a group by the primary key of T1, but I'm also joining T2,  also via the primary key to another primary key.  And finally, I'm selecting something from T2, some other column.  And if you have proper functional dependency checking, this is valid because it turns out  that if you, the T1PK implies the result that the T1B has only one distinct value.  So it's valid if you have this optional feature implemented in your database.  And this is basically the one test case which failed here.  So it didn't give it a solid green checkmark, but the light green checkmark.  And this is how functional dependencies look in the standard.  That's just a table of contents.  So it's read across 10 pages, which are really hard to digest.  And the best thing, it's not even complete.  It still leaves room for window extensions.  Like what I can think of is if you're doing some row number together with the partition  by class, if the partition happens to be a primary key, then you can also have new functional  dependencies.  The same is true for ordinality, if you do unnest or anything like that.  So there's still room for window extensions there.  Okay.  This was the first part.  The first part is features that are there, but in some way less conforming and less complete  than in other databases.  And it was really hard to find those things because generally said, Postgres is leading  in standard conformance.  Now let's look at the second part.  Features that I'm missing at all.  And my most favorite feature is row pattern recognition.  Who has ever seen that?  Oh, one at least.  Did it change your life already?  I think it's pretty interesting.  Oh, pretty interesting.  If you think window functions are awesome, then yeah, you will like that.  I think personally, this is the most important feature for the next decade maybe.  It's really at least as important as window functions.  I will show you what it does.  The example I have chosen here is like processing a log file, a stream of events.  Let's say this is some web log file where you have hits to some web pages.  And what you would like to do is to do some statistics on that.  How many sessions do I have in this log file?  What's the average duration of one of these sessions?  These are the two examples which I will now solve using this new feature.  And you can see there there's this 30-minute threshold.  So I define one session as long as the next hit is within 30 minutes of the previous hit.  And this is how the query looks like with the new feature.  And in good old tradition of SQL, it makes most sense to read it up from bottom up, not  the other way around.  So what is it?  The general idea of this is that you would like to combine rows in some logical way very  much like group by does, but in a more flexible way.  And the more flexible way means by using regular expressions to match them, to match rows together,  to mark them as they belong together.  And once you have matched them in one whatever group you want to say, you can post-process  them.  You can then apply a group by you can do similar things like window functions and so on.  But the really important thing is the idea is apply regular expressions over a sequence  of rows to identify matches.  And so you can then process some statistics and whatever over these matches.  And if you think of regular expressions, they were characters.  They were based on characters.  What they are matching are characters.  But what we are matching here, these are rows.  And in a normal regular expression, a character usually stands for itself except of those  few meta characters like star and dot and so on.  But we don't have this here in SQL.  We need to define some names, how we can define a row.  And this is what we have to define class for.  So what we can assign there, we can have some names.  Like what I have here is continuation, and I assign some condition.  Like when the timestamp is less than the previous timestamp, and you see this previous  is pretty much like lag for window functions, plus interval 30 minutes.  So when it falls into this interval, then this row can be assigned the property of being  this cont tag.  And this is something you can then use in the next step, which is actually the regular  expression inside this pattern.  What you see there is regular expression syntax.  The main difference is just not characters, but these variables which we have just defined.  And there's one odd thing I must admit, if you see any, or basically if you use some  name in here, you see the cont is the one I've defined down there, but the any is not  defined.  And the strange rule is that if it's not defined, then it matches everything.  So this means the any will pretty much match just any row, but only once, because it's  a regular expression, there's no quantifier there.  And then it will start to try matching these cont rows.  And it will succeed as long as they fall within this 30 minute period, as long as this condition  here applies true.  And because there's a star, like in regular expressions, it will make it 0, 1, or however  often.  And this is the star you know from regular expressions.  It could be a plus as well.  The dialect which is used there is defined in the standard itself, so it's not reusing  some postings or whatever dialect, and it's not actually as powerful, but it's still,  most of it is still, the useful stuff is there, I would say.  Yes, question?  Do you know if it's a direct subset or is it conflicting against regular expressions?  I think it is a subset.  It does have some extensions.  So it does have some strange features which are not in any other regular expression syntax.  It's not dealing with characters anyway, that star doesn't apply to the t, it applies to  the conch.  Exactly, exactly.  Regular expressions.  Exactly.  That's the important stuff.  It's not on characters.  Therefore, you can also divide spaces irrelevant there.  So it's not, I mean, it's not meaningful to ask if there's a subset of regular expressions  involved, so it's a completely different beast.  I mean, I think the question I was trying to drive at is, if it was to be extended by  the committee in the future, would it be extended in some manner that would be more  consistent with what we think of as regular expressions?  I mean, I can give you one example which is missing in this subset.  It's character classes.  So you cannot do character classes, but you can do an OR.  You can use the pipe.  You can also use brackets.  You have the greedy and non-greedy matching.  Of course, it doesn't make sense to have some shortcuts for character classes too, like  upper or lower or something like that.  It just doesn't make sense.  But as far as I've seen, there are some extensions, but as long as these are not characters which  you are matching, it doesn't matter.  I have it in one slide.  One strange feature.  So that is the regular expression.  And here comes one other interesting thing.  Here you can see, in the result, I would like to have one row per match, which implies  you could also say all rows per match.  And this is the difference between making a group by, actually, or working more like  in a window function fashion.  And there is the measures clause, which pretty much translates to the select clause.  So you can define some columns in there.  And the way I'm doing it here, I'm using the last and first functions.  And I have no idea why they are called last and first and not last value and first value  like the window functions.  It's the way it is.  And they can access pretty much any rows in this matched set of rows.  Another interesting thing, which is not actually shown in this example.  Sorry.  These variable names, which we have defined in the define clause, we can access them later  on.  If it has matched, you can say, for example, the last out of the cont matched rows.  In this particular example, I could also have used any.ts, because any is the first  match.  It only matches one row.  So it's pretty much the same like first t.s.  And that's pretty much the query.  There's an order by clause.  You can have partition by clause.  No big deal.  Other than that, at the end, I'm just doing the statistics I've mentioned there.  So here I'm doing average and count of the sessions to finally get the result like I  described it before.  So what I like about this feature is the endless possibilities.  You have in one feature which can do quite a lot.  So I've shown you you can do group by because you can say one row per match.  You can work like with the over clause because you can say all rows per match.  You can have unlike window functions.  You cannot define a sliding window, but you have at least two semantics you can choose  from.  The final semantic, which is pretty much like from unbounded preceding to unbounded following.  And the running semantic, which is from rows unbounded preceding to current row.  So the two use cases you use most often are there.  You have features like you otherwise find in having or work clauses because this you  can encode this in the definitions and in the pattern clause.  You can even combine group by and over.  So I have an example in other slides I will show you where some rows are passed through  one by one and another set of rows is condensed into a single one.  So you can combine this in one go.  Data-driven matching, like the bin fitting problem can be solved with that because these  definitions you can also use like a sum.  To match as long as the sum is below some threshold, which can be some dynamic threshold.  So that's really wow powerful.  And another thing is you cannot only filter out rows.  You can even duplicate rows to some extent.  So one example I have in my slides is if you have some scheduling system where you have  room bookings from two, from two, from two.  You can have one match recognized clause that fills you the gaps.  It makes you new rows for the missing gaps.  So you get more rows out of this clause than you have sent in.  That's also possible to some extent.  So I really think it's super awesome and a little bit complex.  But once you get used into the thinking, it's really wow.  All this nesting of window functions.  If you have a query that nests window functions, I bet this can solve it with a single expression.  So can you use it yet?  Yes.  There's one database that supports it.  If you're interested in this feature, I have some resources for you here.  There's a free technically report by ISO.  Go read it if you're interested in this feature.  It gives you good use cases.  It's free.  And it's not like standard talk.  It's like normal talk.  Do you know, did Oracle like?  They implemented it first and then it came into the standard.  Did it exist before they got standardized?  Yes.  It was in Oracle 12.  And it came into the standard with 2016.  So it was in Oracle working.  And I think there might be even some gaps.  It's a kind of cheating way to get it ticked.  Yes.  Yes.  But there is still room to outperform them.  Because they are only supporting one out of the three features that we find in the standard,  as you can see there.  Because what I've shown you is the match recognized clause, like you use it as a table function,  similar to the table function.  But the standard also defines it that you can use it as a framing clause of window functions.  So that instead of saying unbounded preceding and whatever, that you can put a pattern  in there to say over which how the window moves, how the frame of the window basically  looks like.  And this is not implemented by Postgres.  So go ahead.  You don't show it.  There are no databases.  What's that standard for all?  Pardon?  You have a list of databases that don't support it.  Yes.  I think there are some less-esqually databases that support this concept.  OK.  All right.  So there are some out there that have some.  Yeah.  But don't ask me for names.  OK.  I've seen it, but not in the big ones.  OK.  So read this paper if you're interested in this feature.  And then go to the standard and make sense out of the standard.  But don't go to the standard first.  It seems like a little bit related to the stream SQL stuff, which is, I think, there's  like a working group that's going to pop out of standard sometime quite soon, apparently.  OK.  That seems kind of...  Yeah.  I don't know that.  So I'm not affiliated with the standard committee at all.  I'm just buying the standard and trying to make sense out of it.  I always say, other study the Bible.  I study the standard.  I don't know which is more pointless.  You can fill a life with both of them.  OK.  So read this one.  Another recommendation I have is, of course, my own set of slides.  I've mentioned some use cases before.  You can find them in these slides.  And because Oracle is the only database that supports that yet, there is one guy, one Oracle,  a consultant who has quite a lot of stuff on material and that is you ashen, go to his  blog and look for match recognize, which is the keyword.  And there you can find some other interesting use cases and so on.  Maybe one last word.  All the benchmarks I've done when I was comparing the window function solution to the match  recognize solution.  In pretty much all cases, the match recognize was faster.  And that even applies to such simple cases like top end per group.  So you have a category and you're usually doing, you know, row number partition by and  then an outside query where the row number is less than something.  Even that was faster with match recognize because it's basically just career.  Then match anything.  Curly brackets 10 or whatever.  And that's it.  And the partition by class maybe.  And it was faster than the window function implementation.  So I think there's a lot of potential in there.  Then the next one.  Temporal and by temporal tables.  Ah, yeah, there are some discussions recently.  I'll first go to what is it about.  And I give you another hint where you can find good information.  Of course you can read the standard, but you can also read this paper which was released  a few months after it was put into the standard and it's also good paper.  It's a paper you can download.  It's for free and it gives you quite comprehensive answers on the ideas.  It also gives you an example.  You will see syntax there.  Go read this paper.  If you don't have access to the standard, this is the next best resource to look at.  Don't look at the documentation at other databases.  Take this.  Or the standard, of course.  But also read this first because you start trying to make sense out of the standard.  So what is it about?  There are two distinct concepts.  System versioning and application versioning.  And the idea is that this model two different time dimensions.  The system versioning is there to model when we learned about some change in the reality.  While the application versioning is meant to model when some change happened in the reality.  An example is when some couple marries and maybe a name changes.  It happens at a date of marriage.  But when you learn about it, this is pretty much later usually as a company, as a bank or whatever.  And these are the two concepts to model these two things.  And the system versioning is named system versioning because the system manages it.  Because it's easy enough to just look at the system watch and use this time stamp to recognize when we learned about something.  And the application versioning is consequently called application versioning.  Because the application has to provide the proper time stamps which might be in the past or even in the future.  Like if you have a fee schedule, you would like to have maybe some discount as of tomorrow starting.  Then you can put it with time stamps in the future.  Both of them in the standard can be used on a table level.  And they can be intermixed as you like.  So you can have none of them or you can have both of them or only one of them as you like it.  In the DDL, both of them require, according to the standard, explicit collumes which give you the start and the end time stamp of when this row was valid.  So both of them required that explicitly.  And on top of that, they require something which is called a period,  which logically combines that in what we know as a range type on some time stamp thing.  In system versioning, you have to use the generate always clause to tell the system take care of that.  The system sets these time stamps for you.  The period name is fixed.  It's system underscore time.  On the application versioning side, you can use arbitrary columns, of course.  And you can also use arbitrary period names, but you have to use that.  The DMLs, like in the update delete merge, system versioning takes care of that.  It's mostly transparent to the application.  I say mostly because the two collumes you have to add to the table, they are visible.  And some databases to make it 100% transparent offer some invisible or hidden collumes,  but that's not a feature from the standard.  On the other hand, the application versioning is completely up to the user.  So if you want to have only the current row, you have to write a where clause for it.  The standard does give you, however, some tools to make that work.  And you have to take care of constraints as well in application versioning.  But it gives you a tool, like now, and primarily can read like,  primarily ID, comma, without overlaps and then the name of the period, which is very much like an exclusion constraint in Postgres, like we have it today.  So the standard provides framework.  And here, for example, we have it on the right-hand side for the application versioning.  We have new predicates, like contains, which work on these periods of this, what we call range types.  On the other hand, in the system versioning, we have an extension of the from clause.  In the from clause, in the system versioning, per default, we only see the current data.  We don't see historic data until we ask for it.  And the way you ask for it in system versioning, according to the standard, is you say from table name and then for system time,  and then you can say like S of three hours ago, like current time stamp minus interval three hours.  Or you can say between to get everything, and there's also from and to syntax.  So this is the way you work to work with system versioning.  But according to the standard, these are two distinct things.  The system versioning, we're using this for system time S of stuff, while for application versioning, we're using the where clause.  Recent, more or less recent discussion I found on the hackers list was the S of queries.  This was around Christmas time.  For the application versioning, there was a page sent like a week ago.  I think there hasn't been much discussion about that yet.  But I think this is this is the current state of art I know about Postgres.  Okay, so this is pretty much distinction system versioning application versioning.  Let's look in these two separately.  So which databases are supporting system versioning S of right now?  And yeah, you can see quite some checkmarks there.  The first interesting one is actually this one MariaDB.  It was released last week.  So MariaDB is the MySQL clone, which is not already owned, but run by the kind of old MySQL people.  And yes, they have released it.  I did test it.  I found only one issue that's this last X in there.  But otherwise, it's pretty close to what the commercial databases do as well.  There are even some aspects which are like more than in other databases.  And I have to tell you something about Oracle.  I marked them straight red here.  If there's an Oracle representative, they will say, but, but, but, but we have S of.  Yes, but it's something different.  It's not in the idea of how the standard sees system versioning.  The idea of is you can access the undo lock to get data from like a few minutes ago in case it's still there.  Okay.  The syntax is similar, but yet a little bit different.  And the idea of having some audit trail or something is just not there.  It's similar syntax, similar idea, but still something different.  And if you are considering working on these, who's considering working on these?  Ah, one brave one.  Another one.  There's a few hints.  The system versioning in the standard, it might have one or two gaps.  First of all, schema changes are just not supported by the standard.  When you have a system version table, pretty much every alter table will fail,  except alter table drop system versioning.  So the way to evolve such tables is to, according to the standard, is to alter table drop system versioning,  which also drops all the historic rows.  So they are gone.  You can back up them first if you like.  Then do the DDL you want to make and then again alter and enable system versioning.  Yeah, it's not nice.  It's a gap.  No retention.  The standard doesn't give any framework like you could define like five years.  I would like to keep that or whatever.  It's even the opposite.  It's directly says it's one of the aims that it cannot be filled with that.  There is, I'm from Europe and I'm presenting these features to developers.  And the most common question, the first question I've got in like the last six months was,  is this GDBR compliant?  And the answer is no.  Because according to the standard, in the standard, you cannot have a four system time S of for delete.  You cannot fiddle around with historic data.  That's the point.  It has audit character.  Okay.  So this is just not in the standard.  Pretty much every implementation I have seen does offer a solution for that, but it's not standardized.  The first time I got asked is it GDBR compliant, I was like, hey, is this kidding this guy?  But no, it's real.  Question?  The implementations that you've seen in the other database systems that provide this, are they all consistent?  No, they provide it.  MariaDB has an extension for the four class in delete.  So you can specifically delete something.  In SQL Server, it works pretty much like described by the standard except, so you disable it.  It does not drop the historic records.  You can then delete one specifically and re-enable it and all of this in one transaction.  Okay.  In DB2, it's also, you can disable it and you have a separate history table which is not dropped if you disable it.  And then you can fiddle around with that and then re-enable it.  But I don't think it works in one transaction in DB2.  So there's no standard committee to change it?  There is no, it's different everywhere.  There's no consistency.  Yeah, that's true.  So if you ask me which one to follow, I would personally take the MariaDB because it's the most convenient.  And what MariaDB also got right is they have an extra access right for that.  So you need some extra grant.  So you can...  Okay.  Okay.  And here is, I think, the worst issue.  For system time, if you work with system versioning, you have this, you can ask for system time as of.  But this works only on base tables.  Not on views.  And there's not even a session setting in the standard defined.  The session setting is available in some of the databases.  But it's not like you can now say,  old session universe as of that time stamp and every system version table would be used like that.  That's just not there.  You can make an extension, but the standard doesn't tell you how to do that.  So you need to make unique to what has all explicitly on every layer?  From every from.  It belongs to the from and it doesn't apply to views.  So if you have a view which previous some system version table, you cannot use the view for as of queries.  According to the standard.  Yeah.  Does MariaDB extend that at all?  I think they have a session setting where you can say for all system version tables as of something.  It seems like something you want to be able to push down into the view list.  Yeah.  In some sense.  Single variable like that.  If you want to do it to view and you have a table involved, you might want to do as of this time and as of this time differently.  It's possible according to the standard because you have to say it for each from just right.  You can have a different.  I think this is something you definitely need.  I think it's an omission unreasonable omission in the standard.  So therefore I'm listing it.  And the last thing which is somehow questionable in the standards is transaction time.  So this generate always as clauses.  They assign the transaction time.  So you have the same value within all the changes you're doing one transaction.  But the transaction time is the commit time.  That means if you have a long running transaction, you're asking for S of some specific date in the past.  It doesn't mean you get the same result as of that time.  Because it might have not have been committed and the transaction time is usually the first data change you run in a transaction.  So there are also extensions by some windows where you can transaction granularization where you can map it the transaction ID to the commit time.  But it's something to keep in mind.  I think the standard is written flexible enough so that you could use the commit time for that.  But it's just a little bit, yeah, in direction.  You need the interaction because if you do the changes first, you don't have the commit time at that time.  So just so I understand when is that time captured?  The time is captured.  So the standard says it is assigned at the time the first data change runs in a transaction and it is immutable from then on.  But it does not say that it needs to assign that time.  A time. I think you could.  It has to be immutable.  You assign a time of X.  Can I ask what it is?  What it is?  So I begin a transaction.  I do the first thing that touches something that's in a system version table.  You could query the system version table.  The columns there.  As soon as I do something, I can actually see that.  I can see what's being assigned.  But it might be another week until I commit.  Yeah.  So technically, if you do the insert, you can write after the insert, run a select on it.  And then it will be the most current version you see.  So you can, these are explicit columns from two.  You can look at them.  So I can consider any guidance in terms of like current time step in Postgres will be the same time step throughout the transaction.  And now that's actually when the transaction is complete, correct?  Yes.  Or when the first data changes done.  Though in Postgres it is in the transaction.  So is there intended to be consistency between this concept of transaction time and say current time steps?  In the standard wording, it's not consistent.  Transaction time is indeed some term which is used in the standard for the purpose of system versioning.  Right.  It's a new term.  It's in.  Thank you.  I'm running out of time, but let's go on.  Notes from current implementations I've seen.  The most common way to implement system versioning is having separate history tables.  DB2 and SQL Server do it that way.  What I find quite interesting idea is how MariaDB does it.  You can have a single table which may be partitioned by a new magic way to partition current rows from past rows.  So you may have it in a single table if you choose to, but you can also partition it.  And you can also partition it like very old rows to cope with the question of data retention.  You just drop the very old partitions.  So it's not like two partitions.  You have one current partition and then you can have as many history partitions as you like.  And finally mentioning that Oric is accessing the undo log for that, which is pretty much a different idea.  Application version tables.  That's a little bit simpler.  Who supports it?  Yeah, DB2.  But if you look at the notes, you will see that I've put there some notes like Postgres use range types for that.  And this is also what the patch which is currently more sub-inded a few days ago is basically about.  It's a different syntax for range types.  I think most of the functionality is already there in the Postgres core.  It's just to make it accept the syntax like outlined by the standard.  And one of these syntax extensions are the period predicates.  I've mentioned them before like overlap equals contains and all of these which have equivalent in the range types already.  I think.  Okay.  Now a few features which I don't explain in so much detail.  I think those two features which I've shown you now, the row pattern recognition and temporal databases.  These are the big features which have huge demand in the scene in the next foreseeable time.  These are the features which people asked me about when I present about this.  Generated columns.  You might know them already.  This is really an uncommon picture here that Postgres falls out of the general market here.  What other databases uses for is for example also function-based indexes.  So many databases don't support expression in the index definition.  Instead they put an index on a generated column.  From standard perspective it's pretty similar.  A computed column, a generated column can be used almost everywhere where you also can use base columns.  That means you can also use them in constraints definition and so on.  So these are the use cases from standard perspective.  If they are good or not, I don't know.  Note that the generate always as clause is reused between identity columns and also the system versioning.  Here comes one.  I would really like the next feature.  Who has ever heard of that?  Combine data change and retrieval.  Is there a reason why it's done differently in Postgres?  I'll show you.  It's writable CTs.  Who knows writable CTs?  There's a standard way to do that.  It looks just a little bit different.  So you can have delete update, insert, merge in subqueries in the standard.  You just have to prefix it with something old, new, or final, table.  And what you're getting then, you can query this data and you can do whatever you like with that.  This example is valid SQL and it copies data from some source table to some target table.  If you do the same with CTs in Postgres, it would probably look like that.  You have inside the CT, you have to delete, you're returning everything, and then outside  in the main query, you're inserting to that.  Pardon?  I'm just thinking about it in Postgres that there was two very different.  I mean, I get your point and look like we should be able to support it.  There are some differences, at least some which I have found.  There are three modes. You have seen old table.  For delete, you can of course only access old data because there is new data.  But you can also, for update and insert, you can also ask for new data.  But here comes the gotcha.  The new data is not actually the final data, and not even the final data is the final data.  What it is, is actually there is defined in the sequence of processing,  there's one step defined where it takes the data.  And this is before, after triggers run, and this is also before some constraint violations run.  So the point is, if you ask for new data, you're not necessarily getting the data that is actually written.  And then there is the final, which kind of sounds like it would be something different,  but final gives you the very same data like new does,  but it fails in case there is some data changed on afterwards.  So if you get the result back for final, you will be sure that this is the data that has been written,  otherwise it would fail.  Yeah.  As opposed to as in writable cities, give you pretty much the data which is stored in the table after it was run,  which is maybe more useful.  So I would probably want another one which does it, like Postgres does it.  Yeah.  Can you get the old data and the new data?  For update.  Not at the same time. Not at the same time.  Yeah. So you can have it in one. You can have multiple of those in one query basically.  But there will be different statements.  Those would be different statements. Yeah. You cannot get both in one step.  But then you need to update it twice.  So you would...  So there's also a question.  Why isn't there one for app industry?  I don't know.  I'm not affiliated with the standard.  But the likely answer, if you look at the support matrix, is because IBM did not have that at that time  and they pitched their implementation into the standard.  So DB2 can do that.  But DB2 has also some limitations. So it doesn't support merge in there.  Okay. Postgres doesn't support merge.  You can see why because merge produces old and new. You don't even know which.  It depends on which row.  You kind of want to deal with both in order to get out of this support.  Yeah.  There is definitely some more complexity than in in-insut update delete.  No question.  What I find interesting, the example which I've shown you, which is valid SQL as far as I know,  doesn't work in DB2 because in DB2 the main query must always be a select if you have in sub-queries some data change statements.  But you can bypass this by chaining with clauses.  In the end you make a dummy, dummy select, select where one equals zero.  Okay. I'm actually running out of time.  I do have some things I will take through.  Partition join is filling gaps in time series.  As long as you have only one time series, it's easy.  If you have multiple groups, this is the syntax we have now in the SQL standard.  So you can say partition by and then do the gap filling so it's doing the partition-partition only oracle supports it.  List-ag, which is very much like string-ag.  Distinct data types, which is core SQL but hardly supported.  And mentioning that two work in progress things are merge and chasen.  I've listed them in separate section because I think the patches are in such a condition that there is a good chance they are applied at some point in time.  So at the moment for chasen everything is red.  But once I apply the set of patches, pretty much, it looks pretty much good at the time.  I didn't look into detail.  This is really preliminary results.  I plan to have a closer look and report any things I find in there.  So this is the overview and this is the last slide I would like to go through in rush.  So whenever a new database release is coming out, like MariaDB or Postgres,  I aim to publish something about that release within a few weeks on my website.  That means at that time I will have this matrix for the new features you are touching.  But this is pretty late if you want to have feedback.  I usually start to prepare these things and start to test these things when there's a public beta, like now for Postgres.  But that's also pretty late because it's already committed.  The first time I usually notice that there's something new coming to Postgres is when Depe's writes about it, waiting for something.  This is my trigger basically.  It's also pretty late because it's already committed.  So if you want to have my opinion on some of these things, please just ping me.  I don't want it to hack us, but you can reach me.  Contact is interestingly supposed to be shown here, but it's now missing.  So you can see it actually here or not.  So you can find me on Twitter.  There's also an email address, but you can find me just over modern SQL.  There's also contact and so on.  Please ping me.  You have to ping me.  I will try to help you as good as I can, but you have to ping me because I'm not monitoring the development until it is really good.  I'm reported by Depeche as waiting for.  So sorry for overrunning five minutes.  We have had some questions.  If you have more questions, I'll be here till like today.  Reach out for me or reach out via Twitter, email, whatever.  Thank you.
Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_0_107.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_108_251.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_252_383.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_384_1547.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_1548_5603.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_5604_6347.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_6348_6395.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_6396_6611.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_6612_7535.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_7536_7763.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_7764_10511.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_10512_10763.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_10764_11231.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_11232_11555.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_11556_11687.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_11688_11735.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_11736_14675.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_14676_14927.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_14928_15083.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15084_15143.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15144_15191.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15192_15263.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15264_15311.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15312_15431.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15432_15479.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_15480_16199.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_16200_21959.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_21960_22007.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_22008_22163.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_22164_22259.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_22260_22319.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_22320_22943.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_22944_23675.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_23676_29459.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_29460_29531.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_29532_32927.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_32928_37019.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_37020_39071.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_39072_40151.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_40152_42191.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_42192_43427.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_43428_44927.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_44928_46415.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_46416_47123.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_47124_62759.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_62760_63107.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_63108_63683.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_63684_65075.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_65076_65567.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_65568_71183.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71184_71279.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71280_71687.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71688_71807.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71808_71891.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71892_71987.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_71988_72107.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_72108_72203.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_72204_72755.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_72756_72815.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_72816_74219.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_74220_74423.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_74424_74483.png Markus_Winand__Standard_SQL_Gap_Analysis_--_PGCon__XkyOgz9APdI_74484_75426.png