Critical Race Theory in HCI

Last week, I attended one of the meetups of the Chicago local ACM Chapter. ACM has several SIGs – Special Interest Groups, and technically speaking, I am a part of only one of them: SIGMOD (Management of Data) and also a member of ACM-W – Women in Computing. 

“Before all this started” (everybody these days have to say this, referring to our previous life), so – before it all started, I attended some of the ACM meetups, but not that often. 

The Chicago local chapter of ACM SIGCHI – Computer-Human Interaction – is something outside my area of interest, but I started to attend their virtual events and became more and more interested, and now I want to share it with my network :). 

The topic of the last week’s meetup was  “Critical Race Theory For HCI”, and I regret I didn’t publicize this event! It was so-so-so worth attending!

I am going to cite one example which started this presentation, and you will see what I am talking about. 

Here is the situation. A child is doing a project about a family vacation. If you would google images for “family vacation,” you will see the images of white families on exotic beaches. And it will take you, they said, six pages scrolling to get to the first picture of a non-white family. And if you refine your search with specifying “black family,” you will get pictures with a very different type of vacation. And you might need to refine your search even more. 

This picture is bad enough, especially because when you hear that, you realize right away that it’s true! And you just never thought about it. Well, maybe you did, but I didn’t!  

Afterward, there was a conversation about what can be done to change things and how we can all make it better in technical aspects and personnel management. And I think that was the most important take-away: do not let yourself off guard. Keep questioning yourself. 

And, if this all sounded interesting – consider joining this meetup!

Advertising Another JSON/Postgres Presentation.

For several years, I was referring to the work with did at Braviant Holdings, as “our JSON thing.” But then it got a name, and now we call it NORM. So, why “JSON/Postgres” again?

Well, because I am advertising this online presentation:

Working with JSON Data in PostgreSQL vs. MongoDB

This talk was scheduled to be presented in New York in March, and it was in my shortlist of “must attend.” I was also planning to have this speaker at my panel. The conference did not happen for obvious reasons. Still, I was excited to find out that this talk will be presented virtually. It is going to be awesome (I know the speaker:)), and I am looking forward to finally hear it!

Please consider virtually attending! Hope to “see” you there!

P.S. Yes, it’s the same date as Chicago PUG meetup, but different time!

“The Pioneers of Computing” Talk

Last week, I attended several online events, way more than I usually do. One of them was sponsored by the WIE (Women In Engineering) Group of IEEE Chicago section. I am an IEEE member, and I used to be more active in the group than I am now. As with many other things, I hope that I will be able to participate more in the future.

The talk, which was presented on Thursday, was called “The Pioneers of Computing and The Imposter Syndrome,” and it was about women pioneering programming and computer science. Everybody knows about Ada Lovelace and Grace Hopper, and several other female figures in the world of programming. But I didn’t know that at the dawn of programming as a profession, it was considered a “girls job.”The talk was presented by Anne Lee, Nokia Bell Labs Technology Strategy and Architecture CTO Partner. She made it clear that the narrative “women aren’t biologically inclined or skilled to do tech work” has no foundation whatsoever.

It was surprising to see the clips from newspapers and magazines from the late 50s and 60s, which mentioned “computer girls.” Remember that that was way before the age of personal computers. Still, programming was considered “the office job.” Only later, when programming started to emerge as a high-skilled trade, which was paid really well, men entered the profession and created the myth of “women can’t program.”

I am sure that each person working in tech has met some outstanding female programmers, and I want to reiterate that that is a norm rather than an exception!

How to Drop a User

Do you have some “dead” users in your production database? Users which belonged to some former employees, who are long gone, but their Postgres users still maintain their ghostly presence in your environment? Just be honest :). I know that all of us should have some process in place to drop a user when somebody leaves the company. Most of the time, we are doing it promptly. But sometimes…

Sometimes, when we are trying to drop a user, we get an error: that user owns some objects. And there is no “DROP CASCADE” option for a user. Moreover, most likely, you do not want to drop cascade because that user might own some critical objects you do not want to lose!

Yes, we should not have such situations in the first place, and you might even have a policy stating that objects should never belong to the individual users, only to the group roles. But things happen. Maybe, somebody was in a hurry fixing production issues. Maybe, something was created “temporarily.” In any case, you can’t drop this user. You need to reassign the objects the user owns to the correct user/role. You need to revoke permissions. In case you can understand what in the world these permissions mean!

Several weeks ago, I was facing one of these situations. I successfully reassigned objects and revoked permissions, except for a couple. These last ones were weird, and I could not revoke then no matter what! What I mean, I was issuing a revoke command, it would complete successfully, but permissions still stayed! Apparently, I did not know what I was doing :).
I had to rely on the last resort – ask Grandpa Google. And I quickly discovered a command I didn’t use before: DROP OWNED BY. Looks like exactly what I wanted, almost DROP CASCADE, right? Wrong! Because in contrast to DROP CASCADE, it won’t tell you what exactly it dropped! When you issue DROP CASCADE in a transaction, it would tell you which objects it is going to drop, and you can make sure you are not accidentally dropping something you didn’t mean to drop. But this command executes in dead silence!

I can’t even describe how scary it was to actually press ENTER:)

… In case you think I have no fear!

Introducing NORM repo

In the course of the past two and a half years, I gave an endless number of talks about “our JSON thing,” which we now call NORM. And since my very first presentation, people would ask me whether I can show an example of the code developed using this approach. And I never had a good answer. I do not know what took me so long, but now I finally have it. 

For those who love an idea of the wold without ORM, and who want to start “doing it the right way,” the working example of the NORM technology can be found here. 

Please take a look! We are planning to add to this repo a small java program that will use the PostgreSQL functions. 

And I would love to hear from people who would

• clone this repo and let us know what does not work or how documentation can be improved
• develop a simple app on ruby on rails, which will use the same Postgres functions 
• utilize the same approach with any other database/application combination

Looking forward to the feedback!

My Fourth Anniversary at Braviant Holdings

April 18 marked my fourth anniversary at Braviant Holdings. Granted, I pictured it to be very different – I had so many professional plans for 2020! It’s hard to say whether any of them will come to the reality that year, but it is always the next one :). And regardless of the current situation, I could not be happier with my decided to join this company four years ago!

Although I could not properly celebrate this milestone with my team, there was a moment of joy three days later – we officially shut down the old client site database. To provide some context, it was “the last piece of the past.” This database was created before my times, and one of my goals through all these four years was to replace it with a better solution. It took a while, but we were able to identify and eliminate all dependencies on the old code and old data. I can’t even describe how good it feels!

There was an index – but not the right one!

Several months ago, I came up with what I thought was a great idea: an easy algorithm to quickly verify whether a SELECT will execute in a reasonable time. Writing down these simple rules helped me a lot, especially in all too frequent situations when a report has to be deployed in production immediately because it is a business emergency. 

One of those happened a couple of weeks ago on Friday. Yes, I know – who in the world deploys anything to production on Friday afternoon?!  Apparently, I do, especially when the word “emergency” is in the air. 

A person who developed this report told me that in the staging environment, it was executed “as before,” about 30 minutes. It as unacceptably slow, but I didn’t have time to optimize, and I could not block this deployment. It was an incremental refresh; I checked the execution plan – it was using indexes, and I said – OK. And deployed it.

The next thing I knew was that it was running and running and running. And I had to kill it because it was blocking other processes. Then I decided to leave it to run overnight and see whether it is going to complete ever. It ran for four hours! I took it out of the schedule. Now I had to figure out how to run it :). I looked for the delta criteria and tried to run just this one – and saw a full scan. And then, I ran an execution plan for the whole query again. And looked at it closer. Well… it was using an index in the inner join, but … the wrong index! 

You know what I mean: there are situations when the using index is slower than a table scan – it was one of those! And it happened because the field which was the driver of the delta was not indexed at all! After all, the solution was easy: I created the two missing indexes, then together with the developer, we removed a couple of unnecessary checks, and then the whole report took just 30 seconds to run. 

A little bit better than four hours, right?!

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:)