Category Archives: Data management

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!

Leave a comment

Filed under Data management, SQL

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?!

2 Comments

Filed under Data management, Development and testing, SQL

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.

Leave a comment

Filed under Data management, SQL

Funny thing happened…

I have to share that:). I had a ticket to develop one operational report. Not only the report requirements were complex, but also it was very difficult to debug. We just started to collect the data required for this report, and we do not have enough of it to cover all potential issues. Moreover, since the data volumes are so small, the issues are resolved fast, so in two days, I never had a chance to catch a single case of exception. Until this morning. When a thought suddenly came to my mind, and I asked myself: Hettie, why in the world you are waiting for an exception to happen in real-time?! All your tables are bitemporal, so you can time-travel to any moment of the past, including the time, when exceptions occurred!

It’s funny and not funny that it took me two days to figure this out! Especially because I was the one who introduced bitemporality!

Worked as expected 🙂

Leave a comment

Filed under Data management, Development and testing

Bitemporal documentation is available!

Everybody who was curious enough to start using our pg_bitemporal github repo would complain about the lack of documentation, so we’ve tried really hard to provide our follows with some guidance.

What we have now, is very far from perfect, but if you go to the docs directory, there is a lot of documentation, including our old presentations, explanations of the basic bitemporal concepts and most importantly first ever bitemporal functions manual, which we promise to make more readable in the nearest future. Meanwhile – please share your feedback! Thank you!

Leave a comment

Filed under Data management, research

Databases are not sexy?

I’ve heard this line from a former colleague of mine. He was explaining why there are so little database people around, why IT students are not specializing in databases. That was his answer – that it not cool. “WEB-designer” sounds cool, “database developer” does not.

Several months passed since I heard that, and I was thinking: I should write a blog post about it! However, honestly – what else I can say except the things I’ve already said in this blog multiple times? That there is nothing more exciting, than exploring the endless possibilities of SQL, that nothing can be more rewarding than applying your magic to the jobs, which runs for an hour, and all of a sudden it runs in less than a minute and produces the same result:)

I suspect that the general public does not think that there is something behind a web page, and when somebody experiences a website slowness, they refer to it as “the internet is slow.” Also, the buzz words like “Big Data” often send a message, that “the databases are outdated,” and that there is something bigger, and cooler, and more fashionable, than just”databases,” which does not help a bit.

As I always like to be practical, and not only state a problem but come up with a way to solve it, I am now thinking about how to bridge this gap. I want to find ways to reach out to college and high school students and to give them some exposure to the Wonderful World Of Data. A couple of years ago when I was attending one of the Big Conferences, I’ve heard a discussion regarding “what our students want to be taught.” That was a time of Big Data just becoming a Big Deal :). Honestly, my opinion is that the student’s interest should not drive a curriculum entirely 🙂 and that that’s the right place to interfere.

Is anybody interested in joining me in this effort?

Leave a comment

Filed under Data management, SQL

Working Under Pressure, Next Chicago PUG And The Wonders of FDW

Anybody likes working under pressure? Anybody likes production crisis? Nobody would admit it, however…

Try to recall something really exciting which has happened to you in your work environment. No, not your recent promotion :). Try to remember last time you did something great, and things worked fabulously. May be you were not running around, as I do in such cases, telling everybody what a cool thing I just came up with, but even without this running around, most likely your the last great thing happened when there was some serious production issue and you had very limited time to come up with an entirely new solution.

You know how I love sharing the story of New York Hight School Application Processing System! And when I tell this story, I always mention, that I had very limited time to fix everything, and worked 16-hours days for 2 weeks.

It was not that kind of crisis recently at my work, but it was still a production issue . And as 99% of all of our production issues, this one have happened during some interaction with our third party service provider, who’s utilizing MySQL. To be honest, knowing what I know now about MySQL as of now, I am very close to making a statement, that it in not a database! Because, anything which wants to be treated as a database, should know how to use indexes :).

To make the story short, we’ve figured it all out, and rewrote a query – in a most absurd way :), I have to admit. But after this one, and a whole bunch of other similar exercises, I think I have a very good understanding of how things work on the MySQL, what is getting lost when we are using FDW, and what and how can be mitigated.

And guess what – I will be taking about all this cool stuff next Wednesday, at the Chicago PUG. Come and join the conversation!

Leave a comment

Filed under Data management, SQL