We all make mistakes, but if we're lucky, we can learn something for next time.
A few weeks ago I started working on a new project, involving PostgreSQL and DBeaver, two new tools for me.
If you haven't used them before, PostgreSQL is an open-source relational database system.
DBeaver is a free multi-platform database tool for developers, and anyone working with databases. You can use it to connect to multiple types of databases, without the need to install or change software when switching from one to the other.
Anyway, I altered some views and stored procedures in the development environment, and after getting approval from the testers, tried to make the same changes in the production environment.
And then something strange occurred, I run an alter view command, and DBeaver told me the command run successfully. But when I ran a select from that view, it still had the old logic before the change. Right clicking the view, and getting its code, showed that it really hasn’t change.
I reran the same alter view command, and this time it did not finish, but kept running.
I used a query to check for running commands:
SELECT *
FROM pg_stat_activity
WHERE state not like 'idle%'And it showed 2 running commands with the same alter view code.
I tried to run the alter command from another new window, and now, checking running queries, I had 3 versions of the same command all in running mode.
I tried to kill the running queries and start again (pid is the process id from the query above):
select pg_terminate_backend(pid) but the kill command did not work either.
At this point I felt very frustrated. I was running commands, but they didn’t seem to be doing anything!
It took some time and research , but eventually I found out the cause.
In DBeaver, there is a difference if you set your connection as type dev or test, or if you set the connection type to prod. In the dev\test type, auto commit is enabled, and if prod, auto commit is disabled.
Auto commit means that when you run a query, DBeaver automatically commits it to the DB. In a Production environment, it lets you by default change your mind and rollback without committing.
So, my first Alter view command was completed but not committed, and it kept locking the view, and the next runs of the same query where locked, waiting for it to release the lock and never got it.
What I should have done is clicking the pretty obvious commit button. Or change the setting on the connection or on that window to auto commit.
That's my funny story for today. I hope you can learn from my mistake, or at least have a good laugh :-) .