Chicago PostgreSQL User Group in 2023

On January 17, Chicago PUG started the new year by welcoming Ryan Booz. Ryan’s presentation Successful Database DevOps with PostgreSQL. Although I already thanked Ryan and want to thank him again – the presentation was phenomenal! We had an extended discussion after his talk, and even the next day, people in my office kept talking about it! I should mention that there was a poker tournament in the room adjacent to the one where we had our meetup. A couple of people migrated to our room to listen to the presentation!

So – thanks again, and a link to the slide deck would be great!

And guess what – the February meetup is now less than a month away! On February 15, our speaker will be Kirk Roybal, who presented at Chicago PUG many times, and I am so glad he can do it in person again!

If you are in Chicago or close enough to be able to attend in person – please register here. It is so good to see people’s faces!

PG Day Chicago Volunteer Sign-Up

Hi, friends and colleagues in Chicago, and those who plan to attend the PG Day Chicago on April 20! We are looking for volunteers, and there are many options available. I know that the volunteer sign-up was not working before, but now it is fixed, and you can sign up here: the link to the volunteer form.

One of many dumb things I did recently…

I know that many people are reluctant to admit they made some stupid mistakes (like accidentally dropping a table in production). Some of these mistakes are very visible to the rest of the world, like the infamous Amazon S3 rm *, but raise the hands those who never ever did anything like this?! That’s why I always try to share my “oops,” in hope that it will make many people feel better.

Now, here is my recent story.

I spent two workdays trying to figure out why my stored procedure failed on commit. It was failing with an error “can’t commit while a subtransaction is running.” I am looking at the code, and there is no subtransaction! And I was trying to figure it out for two days! The use case was a classic one for “why we need to commit inside a stored procedure: we have a loop for a hundred million rows, and we want to commit, say, every 100K or 0.5M.

I read and re-read all related PostgreSQL documentation, all mailing lists, and all blog posts I could find, and I still could not figure out how my example differs from other people’s examples which work.

Out of total desperation, I started to remove parts of the code, hoping that, at some point, I would remove something which prevented it from working.

Guess what? I am down to i:=i+1, and it still returns the same error!

And then, finally, I realized what the problem was! I had exception handling!!! Which implicitly creates a checkpoint!!! Which I theoretically knew but, for some reason, I didn’t associate it with a subtransaction.

Now I knew what was wrong, and I knew that the solution was to create a block inside the loop and put the exception handling there. Great.

Doing this, sitting and waiting for commit messages, and nothing happens. I know that the commit message should appear within 20 min, and I am already waiting for two hours. Since this is happening not in production but in my sandbox, I let it run… until it hits “out of disk space.”

Next thing I realized – when I was putting back into my code all of the meaningful pieces I removed, I accidentally deleted i:=i+1.

😂 😂 😂

PGSQL Phriday #004 Recap

Thank you to everybody who contributed to the January PGSQL Phriday topic! I suggested “Postgres and Software Development,” while being very well aware that this topic is not popular in the PostgreSQL community. To be completely honest, I thought that it was possible that only Ryan Booz would contribute! 

That being said – a big thank you to everyone who participated! Please find a summary of contributions below, and please let me know if I missed your post!

The first person to contribute was Andreas Scherbaum with his blog post PGSQL Phriday #004: PostgreSQL and Software Development. Andreas had a huge advantage as he could summarize the information from over 140 interviews he conducted for his “PostgreSQL person of the week” series. His blog post mainly focuses on what people are saying about the development for PostgeSQL – patch submissions and other details of how PostgreSQL as a project is maintained. While we can learn a lot about PostgreSQL development patterns from these observations, we still want to learn how the World of PostgreSQL interacts with the rest of the world.

Next, I want to mention two great “here is my toolbox” posts; a Sequence Survey from Gabrielle (Gorthx) and Don’t do this: creating useless indexes by Laeticia Avrot. (great read, and lots of helpful links!) Many thanks to both of you!

I love the blog post by Dian Fay Scripting in the Industrial Age. I bookmarked this post for future rereading and citing since it resonates with many of my sentiments about automation, centralized code repo, sharing, and the ease (and what’s not) of finding what you need!

In his post Postgres and Software Development, Ryan Lambert addresses how he manages the code through the development process. He groups the code into mission-critical, not trivial, and trivial and explains how he handles all of these code types. I like such a systematic approach! Also, Ruan is one of the few who took my prompt on pgTAP and testing, and I am glad this topic has been discussed. 

And finally, here are two blog posts by authors who share the experience (and pains) of communicating with application developers!

• Pat Wright’s PostgreSQL and Software Development 
• Ryan Booz’s Database DevOps and code management with PostgreSQL. 

When Pat responds to my prompt with, “I rarely have non-frustrating interactions with developers,” I know he feels my pain! And same as Pat, I believe that the problem originates in education or lack of that! Pat mentioned “1-2 semesters of how to write SQL,” but the truth is that a frightening majority of application developers’ degrees are not technical or mathematical and, more often than not, have nothing to do with their jobs. Most likely, you can expect a code boot camp, with not semesters but just hours of SQL. 

And finally, Ryan Booz’s post! I knew he would love my topic because that’s what he will present at Chicago PUG on January 17! And I know that he believes in a “database as a code” mindset. Read his blog post to learn about the principles he carries through in how he approaches database management.

Summary

• Once again, THANK YOU to everyone who participated in #PGSQLPhriday #004! 
• Special thanks to everyone who shared their scripts!
• Unfortunately, our interactions with application developers are more often frustrating than not!
• Unfortunately, although we would love to have a way to organize our toolboxes, we still do not have a straightforward way.
• We all want to share our scripts, but the most common way of sharing is via blog posts.
• And thus, the question remains:

Why an application developer says, “Let me share a library with you,” and a DBA (or a database developer) says, “let me give you a script”?!

P.S. I just found out that my talk “PostgreSQL and Software Engineers” has been accepted to PG Day Paris! Yay!

PG Day Chicago 2023 Call for proposals

Now that we are in 2023, and you have probably submitted your proposals for Nordic PG Day and PG Day Paris, please consider submitting a proposal (or two) for PG Day Chicago.

Think when the last time you attended a conference in Chicago was? I can tell you that it was at least three years ago, or maybe even more! And if it was in 2018 or 2019, it was in December! If you ask me, I love Chicago any time of the year, but I know that most people disagree:). But even I would admit that Chicago looks much better at the end of April!

I can’t wait to welcome everyone to our Windy City, and I am thrilled to share everything Chicago offers! Architecture, museums, music, Broadway shows, and I am not even mentioning the food (we are going to be in the West Loop, so gourmet food will be impossible to miss:))

And most importantly – meeting with other community members, people who are as passionate about PostgreSQL as you are, on this side of the pond for the first time since September 2022!

The only thing you need to do now is to click on the submission link and enter your proposal!

And one more #PGSQLPhriday reminder!!!

This is a very short reminder – it’s already Thursday! (Ph)Friday is one day away!

Did you miss this post???

PGSQL Phriday #004: PostgreSQL and Software Development

Please contribute!

PGSQL Phriday reminder

Hi, Postgres community! For some of us, Monday is going to be the last day off of this end-of-the-year vacation. In a day, you will be back to work and will spend several hours trying to recall where you left off before the Christmas break.

This being said, I wanted to ask you: did you get a chance to think about the topic I proposed here? It could be that Monday will be the last day before Friday you will have a chance to blog about that! Just follow the link and check out the rules of the game. And join the discussion!

PGSQL Phriday #004: PostgreSQL and Software Development

Thanks to Ryan Booz, we now have the #PGSQLPhriday blogging series. When Ryan shared the idea with me in October, I enthusiastically agreed to host the January discussion without even reading the instructions in advance! The most challenging part of these instructions was to write a short blog post! Well… I will try to be as concise as possible, although I can’t promise! 

Without further ado, welcome to the New Year edition of #PGSQLPhriday series! Today’s topic is:

PostgreSQL and Software Development

As much as we all enjoy writing SQL and “doing everything in Postgres,” we are very well aware that databases do not live in a vacuum and were not invented for our pleasure and enjoyment. We may come up with new extensions and innovative indexes; we may improve the query planner and speed up backups, but if application developers don’t use these features, nobody will appreciate our efforts.

Those of you who, like I, often interact with application developers know how often these developers are upset with us, with PostgreSQL, and with databases in general. In addition to the usual complaints that “databases are slow” and “database developers are slow,” a frequent complaint is that database developers are horrible engineers!  

And you know what – I agree! 

There are shining examples like pgbackrest, where the source code is stored in GitHub; the tests are there, and installation instructions are clear and specific. But for most of the “ordinary us,” it’s that script.

Why is it that when application developer wants to share their work, they would say: let me share this library with you. And in similar circumstances, a database developer, a DBA, and even a consultant would say: let me give you a script!  

A script is copied from an email to the command line, from the command line to Data Grip, and from there to the local file, and when nobody can remember where this file is, we go back to the original email. A script is something you can’t “install” in the database, and nobody guarantees that it will work in the next version of Postgres. So how can application developers trust us?

— How can I find the size of the bloat? – There is a script; let me find it…

— How can I find out all object permissions for a user?- There is a script somewhere on the internet; let me Google it…

— Is there anything in Postgres that can be used in place of Oracle $ views? – No, but there are scripts…

And the list goes on!

Now that I have expressed my frustration, here is what I would love to hear from other people in the World of PostgreSQL:

• Do you have any of your own scripts which make your life as a DBA/Database developer/consultant easier? What do they do? Do you share them with others, or are they hidden in your secret toolbox?
• Do you store your SQL code in GitHub (except when your company requires it)?
• Do you use pgTAP? Do you think it’s a good practice to have pgTAP tests in your repo, or does it not add value? 
• There are many “scripts” to do all sorts of things in PostgreSQL that are stored everywhere and nowhere specifically (like the abovementioned bloat scripts, lists of unused indexes, you name it). Do you think they should be a part of the PostgreSQL official documentation and re-verified for each new major version?
• Did you ever have (frustrating) interactions with application developers? Did they end with some truce?
• Anything else related to that topic you would like to share?

How does #PGSQLPhriday work?

• Post your blog post about “PostgreSQL, you, and application developers” by Friday, January 6th.
• Include “PGSQL Phriday #004” in the title or first paragraph of the blog posting.
• Link back to this blog posting. If you don’t link back to the invite, you won’t be included in the summary list of blog posts.
• Announce your blog post in one or any of these places:
  • Twitter, using the #PGSQLPhriday hashtag
  • LinkedIn
  • PostgreSQL Slack in the #pgsqlphriday channel
  • Responding to the thread on the pgsql-general email list that announced this month’s blog topic
• Interact with other blog posts about this topic! We hope to see conversations happening on LinkedIn, Twitter, or in the blog comments.

Looking forward to your blog posts!

PG Day Chicago 2023: call for volunteers

Dear Chicago PUG members, Chicago-based co-workers (present and former), and anybody planning to attend PG Day Chicago on April 20, 2023!

As promised, the volunteer sign-up is now live: please fill in the form here if interested!

PG Day Chicago 2023: CfP open, call for volunteers

Dear Chicago PostgreSQL Community, friends and colleagues, and the PostgreSQL community at large! As you may already know, PG Day Chicago will happen for the first time on April 20, 2023, and I can’t tell enough how excited I am about that! 

Here are several important things I wanted to highlight.

First – the call for proposals. We understand that the pre-holiday rush is not necessarily the best time to work on proposals, so we extended the deadline to January 17 (adding MLK day for those lucky to have it off!).

Second – sponsorship on all levels is available; the sponsorship prospectus can be found here.

And last but not least – we need volunteers! Lots of them! Just a reminder – if you volunteer for the event, you get free admission :). Volunteering opportunities include:

• stuffing the bags the day before the event
• site set up (333 Green Street) the day before the event and the morning of the day of the event
• registration desk (the day of the event)
• sessions hosts
• site taking down (after the event)

Watch for the volunteer application form coming up shortly!