More Updates on the State of Bitemporality

A month ago, I’ve proudly announced that I was able to modify bitemporal correction to allow correction of any effective interval of past and present, not just the current one. I was planning to embed this capability into the regular bitemporal correction, but after a couple of weeks of hesitation I decided against it.

The reason is very egotistical :). At Braviant Holdings, we use bitemporal correction in our production application, and the response time is critical. I already know that the generalized case of bitemporal correction is slightly slower due to the additional checks, and I am not going to risk the performance of what’s already in place. This being said, it shouldn’t make a significant difference for those who is using pg_bitemporal at their own risk, since the difference in the execution time is just “times two,” so if your original correction is optimized to 0.8 sec, the new one may be 1.7 sec. Makes difference for us, though.

Howeer, the function is out there and available, so if you want to try it out, check out ll_bitemporal_correction_hist.

Another update is relate to my attempts to create a barrier for straight insert/update to bitemporal tables (without compromising performance).

My original idea, that it would be possible to revoke  simple INSERT/UPDATE/DELETE from bitemporal tables and make bitemporal operations SECURITY DEFINER won’t work.

The reason is that if we create bitemporal functions as SECURITY DEFINER, and definer is a database owner, then when we grant any user permission to execute this bitemporal function on ANY bitemporal table. That means, we won’t be able to restrict access to specific tables, because we can’t grant execution based on the function parameters (again, theoretically possible, but requires creating additional objects)Now I am back to the trigger idea again.  What could be theoretically done is to have INSTEAD triggers, which would just disable insert/update/delete and then to disable these triggers inside functions. But this again requires a higher level of permissions. 

Obviously, anybody who want to instill this level of data safeguarding, can create their own aliased to bitemporal functions, related to specific tables or schemas, but that will be a custom build, not anything I can provide generically.

At the moment I am out of new ideas, just wanted to state it officially. And if anybody is interested in how to create these custom-built functions – please feel free to reach out!

pg_bitemporal public repo can be found here.

A Word About JOIN_COLLAPSE_LIMIT

I remember when I learned about JOIN_COLLAPSE_LIMIT parameter in Postgres. And I remember the unfortunate circumstances of that encounter. Specifically, it was set to eight, which caused a relatively simple SELECT to be executed in a completely wrong way. 

Since I didn’t know anything about that parameter at the moment, it looked to me like the order of joins in the SELECT statements in Postgres matters, and it sounded very wrong and the opposite of what documentation said.

But then I learned about JOIN_COLLAPSE_LIMIT, and suddenly all the execution plans magically fixed themselves after I changed it for 12. I performed a lot of experiments back then trying to find an optimal value for that parameter, and at the end of the day I decided, that no matter how many tables I am joining, changing it to anything above 12 does not help. Either the execution plan does not improve (you have 16 tables in the join, and you think you change the limit to 16, and the query would fix itself – and it does not). Or, if you change it to more than 20, the optimization takes too much time.

However, a couple of weeks ago, somebody requested my help in query optimization. The problem was that it was not a single query, but rather a query framework, and a person would run several queries in sequence, each time adding or removing search criteria that could belong to any of the tables involved in this SELECT.

Yes, “if it would be me,” I would write the SELECT statement differently each time, but that was not something I could suggest to a person who asked for my help. That would be too complicated. I looked at the query, estimated the number of joins and suggested, that at the beginning of each session they would execute 

SET JOIN_COLLAPSE_LMIT=16

And it worked!!!! I do not know what’s so funny in the fact that it worked as expected… but I was really amused 🙂

New Bitemporal Correction

It might take several more days, till the new ll_bitemporal_correction function will appear in pg_bitemporal GitHub repo, but it is ready. I finished development and basic testing, and it works.

It means nothing for those who do not care about “the state of bitemporality”, but I know that some people care. For those people, I am happy to report, that finally I delivered what you guys have asked for a long time. Now, you can make bitemporal correction not just to the last effective interval, but to any stretch of time. It may cover several historical intervals, it may start in the middle. It can’t end i the middle, because I didn’t come up with any business case for such situation, but if there will be a demand, I am sure I can provide :). Overall – I am very happy about this result :). Stay tunes – I will post when it will be officially available.

On a different note – we are almost P12 compatible, only bitemporal referential integrity still does not work, but it will be fixed shortly.

Huge thank you for everybody who show interest in this activity:)

PG Conf NYC 2020 reminder!

For those who are considering attending PG Conf NYC 2020: the last Day of the EarlyBird discount is January 31! The program (or at least most of it:)) is up, so you can decided which day(s) you want to attend in can’t make it for the whole week. There will be tons of interesting talks, I am already upset I won’t be able to be it three different rooms at the same time 🙂

For those who is interested in my presentations, here is the most complete information so far:

• Boris and I will be doing the same Ultimate Optimization Training as we presented in Chicago in December. The even was sold out than, but it is not sold out yet in NYC :). Our training will be on the last day of the conference, Friday March 27
• I will be presenting the talk about building local communities (specifically, how to build a Postgers User Group) on the second day of the conference
• I won’t be presenting neither bitemporal library, nor NORM, but I was invited to talk at another event during the same week, where I will present the NORM talk (extended from my Cyprus presentation). I will publish all the details as soon as it will be finalized

Once again – there will be an amazing number of interesting presentations, and many experienced people to talk to (I already have a list of twenty:)), so please consider attending!

Looking for New Ways to Bridge the Old Gap: New Ideas After the Conference

Before I went to this conference, I was resentful regarding the fact that the gap between applications and databases will never be closed. Even at the conference focused on both data engineering and software development, there was barely a place for me, and our talk barely got accepted.

I have to admit, I didn’t explore the program much before coming because I had never-ending work crisis, and we had to rework our presentation several times.

But when I took a closer look, I realized that I am way more interested in the SE sessions than in the database sessions. Day four, I could not miss a single moment, and I had several interesting conversations with the speakers.

It turned out that most of them were not even present on day one when I was giving my talk. And they said they would love to come if it won’t be on the first day.

Now I am wondering whether I did it right, never trying to present my work at the SE conferences. On the one hand, I am always saying that my success won’t be possible if I won’t have such an incredible backend team. On the other hand, I routinely say that inefficient programming is all application developers’ fault. That is not true.

One of the talks was about the refactoring techniques, and after the presentation, I asked the speaker whether he ever tried to consider taking into account the factor of accessing a database as a factoring criterion.
He replied that one of his colleagues tried to explore this option, but found it challenging: queries appear to be so entangled, so difficult to extract, that it led to nothing. I told him about my work and suggested that we would love to collaborate if he will find it interesting. He said that he would take a look, and then also mentioned that usually, the database people are not collaborating. He mentioned the lack of constraints and unwillingness to use views. I said that views are horrible because most of the time they decrease performance. But they provide a level of abstraction, he suggested. I replied – the are better ways! He said – well, then give them to us!

This exchange made me think that I am not explaining myself enough when talking about NORM. And if I know that successful implementation depends on cooperation with app developers, I should advocate for them.

More on our Presentation at SOFSEM 2020: NORM Goest Live!

My friends and colleagues who follow me for a while are well aware of the fact that my papers about ORIM/ORM were rejected at several conferences. Also, it’s a known fact that this kind of submissions is not welcomed at the conferences in general. My recent work, which was implemented at Braviant Holding with what I would call a smashing success, was only presented at the Postgres conferences. Our three attempts to submit it to any CS/Academic conferences failed. At some point, it became a matter of principle.

We submitted the new version to SOFSEM 2020 in the hope that at DB/SE conference, the topic could feel more appealing. The submissions formate required us to reduce the size of the paper almost twice, but we were still hopeful we would be able to convey a message.
That ended up being only a partial success. The paper was accepted, however, only as a short paper. Reading the reviews, we realized that none of the reviewers understood what it was about (in fact, we got much better reviews fro VLDB, were we got weak accept, which ended up being not enough). To make the paper even shorter, we had to remove all the actual work from the body:), leaving there only the motivation and the conclusion. But as I said, it was the matter of principle.

Then we spend quite a bit of time reworking the presentation. First, Boris wanted it to look more academic, that my older Postgres presentations. But then we started to change it back to more and more visual since we realized that the topic, the motivation, and the reality are pretty much unknown to our audience. Besides, my Postgres presentations were 40-50 minutes long, and here we had only 30 minutes, including questions. I tried very hard to get it to 23-25 minutes.

The last blow came two days before the conference when we were told that the presentations for short papers should be 18 minutes long, including questions, which meant I had only 15 minutes to present. That did not sound feasible, and we were desperate. Boris tried to negotiate our twelve minutes back with the organizers:).

But then a sort of miracle happened. Right before our session began, it turned out that the first person didn’t show up, and I claimed all the time I could. Unfortunately, I already cut several slides out of my presentation (I put them pack in the pdf, which I previously published here). After all, I was able to bring my point across, and I think that everybody present there, enjoyed the presentation. How I wish I won’t be presenting on the first day because then I `would convince more people to come. But in any case, I hope that now we will be able to publish a full version of the paper somewhere.

Our Presentation at SOFSEM 2020

A preface: as it usually happens with my favorite topic, neither database conferences, nor software engineering conference acknowledge that kind of research. Thereby, when although the paper got accepted, it was accepted as a short paper (because nobody understood what it was about :)).

Moreover, two days before the conference we were asked to shorten the presentation to 15 minutes. Fortunately for me, there was one no-show at our session, so I was able to present in full. Below is a full presentation version including two slides which were not in today’s presentation.

NORM – SOFSEM2020