In the recent Postgres Community Survey by Timescale, nearly 50% of users reported that they started using Postgres within the last 5 years, including almost 20% within just the last 2 years. With the pandemic and subsequent lock-downs wiping out so many local user groups and in-person conferences, being able to get help and network with other Postgres users online has never been more important.

Of course the Postgres website lists several community resources which I would encourage you to check out, but given the recent kerfuffle with the freenode irc community I thought it might be good to highlight some additional options. Luckily there are a bunch of them out there, and they are all free to join. The following list is not exhaustive by any means, but these are the regular postgres gatherings that I visit at least occasionally and I think you’ll be able to get something out of as well.

PostgresTeam.Slack.com - Ok, this one is listed on the community page, but since I use slack regularly for work, the Postgres slack team has become my daily driver. The #general channel serves as the primary spot for general Q&A, but there are also topic specific channels like #pgbackrest and #patroni, not to mention general information channels like #postgres-job-offers and #pgsql-commits. So far we’ve had over 10,000 people sign-up for the Postgres slack, and the community continues to grow at a steady pace. If you’ve not used slack before, one of the nice things about it is it has excellent clients for the desktop, through your favorite web browser, and even on mobile; just in case you need to get your postgres fix on the go. (What? I can’t be the only one!)

Stackoverflow - Well, technically https://dba.stackexchange.com, but in any case, your favorite technical question / answer site has a site dedicated to databases. While I have a lot of concerns about the recent purchase, I don’t see anything that comes close to an alternative given what it offers. One of the nice things about stackexchange is that it works a little better for long form questions that require more detail and less back-and-forth troubleshooting like you might do on Slack. It also better embraces the asynch nature of the web, which is not to say that you’ll have to wait long, the community there is pretty active and answers can often come in minutes. Oh, if you go now, they are running their annual Developer Survey; if you sign up be sure to represent the Postgres community :-)

The Postgres blogging community is also pretty active, and you can certainly get a lot of good information through posts, and get some questions answered via comments on those topics. While I don’t have a dedicated process for reading Postgres blogs, I find that I do end up coming back to certain ones time and again. If you don’t have a favorite, just keep the Planet Postgres site handy and you’ll have the chance to check out many of the most active Postgres bloggers and assemble your own list.

Of course there is always twitter. Best for quick questions and finding out what’s new in the world of Postgres, many folks in the community are active and willing to answer (short!) questions on twitter. While there aren’t any official hashtags, I’d recommend following (or tagging) tweets with #postgres or #postgresfriends (or #pghelp if you’re optimistic) to get started, and through those you’ll be able to uncover active community members that might also be worth following.

Finally I also want to give a nod to IRC. I’ve been visiting the postgres channel on freenode for nearly 20 years and while the recent changes were a tad depressing the community members on IRC have bailed me out plenty of times, and I’m certainly thankful for thier help over the years. You can read the official migration announcement about the irc team moving to the Libera Chat network, and the Libera Chat folks also have some nice docs on access IRC, whether through a dedicated IRC client or through a web based client (I’m currently trialing this). The primary community channel on IRC is #postgresql, but there are a number of other options; check out the community irc page for more info. If you don’t like Slack for some reason and want to do chat, IRC is still a nice option.

As I mentioned before, this list is certainly not exhaustive. If you don’t like these options, you’re only a google search away from other ones, especially if you are looking for regional or language specific options.

In my experience most of these groups are quite welcoming to new users and happy to answer questions on all sorts of Postgres related topics. Of course, given the distributed nature of the project, and being on the internet in general, you are likely to encounter all different sorts of opinions and folks living in their own world; remember to approach new things with an open mind and find the right fit for you.

Say you want to run a query a specific number of times and then stop… trivial right? Not as much as I first thought. Below is a recreation of how I thought this was supposed to work (I’ve subbed in select now(); for my actual query, as it’s a bit more illustrative.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

pagila=# select now(); \g 10
              now
-------------------------------
 2020-11-14 17:37:50.694618-05
(1 row)

pagila=# select now();
              now
-------------------------------
 2020-11-14 17:37:56.045518-05
(1 row)

pagila=# \g
              now
-------------------------------
 2020-11-14 17:37:58.133626-05
(1 row)

pagila=# \g 10
pagila=#

My first attempt, without any thought really, was that the right syntax for this was \g 10. When it didn’t work, I stepped through the idea, first verifying my select was good, then verifying \g worked as expected, and then trying the \g 10 on it’s own. When that didn’t work, I double checked the docs and then hit irc to ask if anyone remember that syntax working… of course the answer was no.

If you are wondering, I think I was conflating \g (which among other things causes psql to either execute the query on the line or re-run the previous query) with \w which also re-runs the previous query, but takes an argument equal to the number of seconds to delay between each run; so \w 10 will “watch” your query every 10 seconds for eternity.

So, that didn’t work, so how to do this easily? Well, the best suggestion from irc was to wrap the query in a quick shell loop, which I admit is a simpel enough way to solve this, but to be honest I wanted an sql level way to handle this. The most obvious solution there was to wrap the query into a DO script and just loop through 10 times, but even that felt more cumbersome than it should have been, not to mention that would have put all 10 queries in the same transaction context, which probably didn’t matter, but wasn’t something I wanted to think about.

And that’s when \gexec popped into my head. Ok, it doesn’t hurt I had just read the docs; but postgres has such a large feature set that even us old timers forget all the things it can do. For the record, the docs describe \gexec as so:

Sends the current query buffer to the server, then treats each column of each row of the query’s output (if any) as a SQL statement to be executed.

Ok, there’s actually more, so go check out the docs, but the main part here was if I could just generate the query enough times, then I could use \gexec to run it for me. Of course anytime you’re dealing with loops at the SQL level, generate_series() should come to mind, and so marrying the two, you get:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

pagila=# select 'select now();' from generate_series(1,10) \gexec
              now
-------------------------------
 2020-11-14 17:58:06.963936-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.964205-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.964411-05
(1 row)
             now
------------------------------
 2020-11-14 17:58:06.96467-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.964953-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.965214-05
(1 row)
             now
------------------------------
 2020-11-14 17:58:06.96544-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.965713-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.965962-05
(1 row)
              now
-------------------------------
 2020-11-14 17:58:06.966218-05
(1 row)

By using generate_series() I can generate exactly how many copies of the statement I want (and dynamically substituate in info as needed) and each query will run as it’s own statement, all without leaving psql. It’s the little things eh?

Note: If you like this post and think \gexec is going to be a usefel addition for your tool box, you may be equally excited to know that just this week Postgres released a round of security fixes which includes a fix for a nasty exploit involving \gexec. Yeah, that sucks, but maybe now you’ll get some value out of the thing you have to patch. Take what you can get, it’s 2020.

Note redux: Thats what I get for not double checking. The security fix was for \gset, not \gexec. Apologies for any confusion and/or if you accidentally upgraded your database because of my post.

I reckon releasing new software trumps just biden yer time, and so I’m pleased to announce the latest release of phpPgAdmin, version 7.13.0.

This release incorporates the following changes:

• Add support for Postgres 13
• Add provisional support for Postgres 14
• Upgrade Jquery library to 3.4.1 (Nirgal)
• Allow users to see group owned databases when using “owned only”
• Fix bug where sorting on selects dumped you to the table screen (MichaMEG)

Note this release drops support for PHP 7.1, and will be the last release to support PHP 7.2.

For more information on phpPgAdmin, check out our project page at https://github.com/phppgadmin/phppgadmin/

You can download the release at: https://github.com/phppgadmin/phppgadmin/releases/tag/REL_7-13-0

For complete details of changes, please see the HISTORY file and/or commit logs. We hope you find this new release helpful!

Package verification codes:

• MD5 (phpPgAdmin-7.13.0.tar.bz2) = f4e5e567fb8fae7193fb265b9c7f5b7a
• MD5 (phpPgAdmin-7.13.0.tar.gz) = 8a38dca6bd0bcdc9481c88f1ac0c0f80
• MD5 (phpPgAdmin-7.13.0.zip) = 46074830709655d1852c3886017ee58c

shasum 6.01

• 165caaf0725563b5b98dce6191e55bfdcb1a8c9f phpPgAdmin-7.13.0.tar.bz2
• 47620834a8bb169c043f47a3eef6029b4f7169af phpPgAdmin-7.13.0.tar.gz
• 12f4dfbaa0f866c6e48e1231f44210a0e6f2907f phpPgAdmin-7.13.0.zip

Like most American families, for the better part of twenty years, we have been a two car family. When we first moved to Florida, in our beat-up old Dodge Omni, my wife and I simply didn’t have the money to buy a second car, so we juggled quite a bit. We even “upgraded” that car (to a Nissan Sentra!) before getting our second car, but eventually, between both our work schedules and kid activities, we opted for a second car (a 1991 MX-5… woot!). And it’s been like that since then; upgraded a car to a SUV to a minivan and several other iterations, but always at least two cars, if not three.

But last year, as OmniTI begat credativ U.S., I made the swap to a full-time remote gig, and no longer need to commute. I still do occasional work trips, or visit meetups, or whatever, but it seems like we don’t generally need to have two cars, so because the lease s now ended on our small SUV (we own our other car), we turned it in, and are going to see how this one car thing works out. We did some rough math, and figure we’ll save at around $300-$400/month, between car cost, insurance, tags/title, and other related maintenance. Amber still has a commute, but we think that in the cases where needed, I can give her a ride to work (or her give me a ride to the airport), and if we do have a conflict beyond that, we can use Uber/Lfyt to off-set that, and likely at a rate of less than $300/month. Or maybe we’ll get it all wrong and go get another car, but for now, we’re curious to see how it goes.

P.S. For those who have been around a while, yes, we are technically still a two car family. Ole Maggie Miata still sits in the garage, now retired and off the road. If we do need to get a second car, there is a likely chance we’ll fix her up and get her back on the road. Even if we fail, it’s a win!

I’m not a big fan of T.V./Movie award shows, but I happened to catch the news that this past Sunday the T.V. show Chernobyl won the award for “Best Television Limited Series”. According to it’s IMDB page, the show has apparently won over 30 awards. In this case, I couldn’t agree more.

I am admittedly late to the Chernobyl party. When the series initially aired in May, I ignored it, thinking I’d binge-watch it at some point once all the episodes had aired (it is a 5 part mini-series). I did try to watch it late one night during the summer, but if I am being honest, I passed out on the couch before the opening credits finished in the first episode. Maybe Chernobyl wasn’t for me. And then last fall I happened to end up on a trans-Atlantic flight with 5+ hours to kill so I thought I’d give it another shot. After all, it is much hard to fall asleep on a plane than on my couch.

And then I was hooked. Since that flight, I have recommended the show to many people, especially those folks I know in the WebOps space who are students of Design Thinking, Human Factors, Resilience Engineering, or Safety Science. It isn’t that the show is not without some flaws; the more you read and learn, you see that there are parts of it that are misrepresented or made up; it is dramatic storytelling after all. But if you have read the literature on complex systems failures or seen failure pathologies documented from the medical, aviation, and nuclear engineering industries, you will immediately recognize the behaviors that surface during the recreation of the accident in the show. Not to mention, the Soviet government does a fine job as a stand-in for internet companies today who are kind-of-sort-of forced to admit when problems go wrong and yet often try to do so without providing any details or disclosing the true nature of the problems.

One of the things so fascinating about Chernobyl, for me at least, is I remember this being in the news as a kid, and that it seemed like it wasn’t that big of a deal (or at least, not as big of a deal as I thought it should be). Of course, watching this now, it all makes sense since the magnitude of potential destruction was almost incomprehensible (i.e., wiping out most of eastern Europe) compared to what the Soviets were telling everyone at the time. The lasting effects, which were certainly not a worst-case scenario, were still harmful enough that it made me wonder how much of this accident helped lead to the break up of the U.S.S.R. just a few years later.

Since watching the series, I’ve now gone on to watch a few other shows on Chernobyl, and if you like the show, I’d recommend these as well. One was a 1-hour documentary called ”Chernobyl: As We Watched” which I caught on something called the “Americas Hero Network”. The other was a show titled ”Building Chernobyls MegaTomb”, which highlights a more recent engineering effort to build a new shield over the reactor before the previous one failed. Yes, this is a disaster that will continue to need management for hundreds of years.

I’m pleased to introduce the latest release of phpPgAdmin, version 7.12.1.

This release incorporates the following changes:

• Fix a number of issues related to changes in Postgres 12.
• Fix an issue with truncation of long multibyte strings
• Removal of broken tree branches from table/view browse option
• Properly escape identifiers when browsing tables/views/schemas
• Add support for granting USAGE on sequences

Note this new version now requires support for the mbstring module in PHP.

For more information on phpPgAdmin, check out our project page at https://github.com/phppgadmin/phppgadmin/

You can download the release at: https://github.com/phppgadmin/phppgadmin/releases/tag/REL_7-12-1

Special thanks to Jean-Michel Vourgère, who supplied a number of significant patches and updates towards this release. For complete details of changes, please see the HISTORY file and/or commit logs. We hope you find this new release helpful!

The folks at TimescaleDB have published their ”State of Postgres” survey results in a new micro-site where you can find a summary of responses, some more detailed analysis, and the source data from the survey. This survey was conducted for about 2 months during the late summer/early fall of 2019 and while I haven’t gone through all the raw data as of yet, after reading the results… well, I have some opinions. If you haven’t read it yet, go check it out, it has all the context for the rest of this post :-)

1. Join, or Die

   Because the Postgres project has no single owner, the Postgres community has always been a little bit fractured and doesn’t always speak with one voice. As users, this means the community can look rather different depending on which vendors you work with, the country you live in, the tooling you use, or the online communities you interact with. Since these different groups aren’t always as coordinated as one would hope, initiatives like this can sometimes be harder to push forward, and I think this survey did suffer from that; it only made it out to about 500 people which is a pretty small subset, and you have to keep this in mind before making too large of conclusions about what you see in the data.

2. Slow and steady growth

   39% of respondents have been using Postgres for less than 5 years, with 10% having started within the last 2 years. I’ve seen surveys from communities where they suddenly catch fire and 50% have used it in less than a year, and 90% less than two years (rhymes with shmocker?) and it becomes really hard for those communities to manage that, so this seems like a positive, and helps confirm that Postgres is growing at a solid pace, but not in a way that is likely to be damaging for the community.

3. You do what now?

   Technical titles are hard, but with more than half of the survey respondents reporting some kind of developer-oriented job title, and 50% saying they work in software companies, it is again a good reminder that Postgres isn’t just for DBA’s, and that most peoples interactions with the software are coming from non-traditional outlets. I’ve spent some time coordinating between the Postgres Funds Group and The U.S. PostgreSQL Association this year to ensure a presence at shows like Pycon, Railsconf, and All Things Open, among others, and I hope to see this trend continue into next year.

4. About those clouds

   The answers related to running Postgres on-prem vs the cloud were a bit hard to decipher. We can safely assume about 1/3 of folks are running on fully managed Postgres, but we don’t know how many of those people are also manually managing instances as well. (We do both and I expect others do the same depending on the size/scope of their deployment needs). I feel like I could make a hand-wavey argument that at least 15% of overall respondents are AWS customers, which seems like a pretty big number and will for some will probably exacerbate the rumblings that, relative to their code contributions, Amazon is not contributing their fair share. Granted that isn’t as surprising as the data on the other cloud providers; Azure/Citus didn’t even rank in the poll, which I just have to attribute to a skew based on Timescale’s reach, especially since GCP got a hefty 18%, which seems amazing considering how they have managed their Postgres offerings. (I have friends at GCP and I like the platform in general, but Postgres seems like a second class citizen the way they are currently running things)

5. Those quotes

   Oy vey. I’m not sure if Timescale was picking quotes just to stir up some controversy (there are certainly more friendly ones in the raw data), but the quotes about NoSQL are a bit off-putting. This is an area where the community needs to continue improving because we have a reputation for sometimes being “stand-offish”. Not in all cases of course, but if you want to find people with strong opinions who are not afraid to speak out, the Postgres community has lots of them. (Perhaps this blog post is a case in point) Anyway, given at least 50% of respondents are using at least one NoSQL system in conjunction with Postgres; and based on modern infrastructure patterns that isn’t going to change; we need to learn to focus on helping people where they are, rather than where we think they should be, and being less abrasive about it in general.

All in all, I hope this information will be useful for the community, and I want to thank the Timescale folks for publishing the results (and the raw data), and I hope they will continue to do this and/or work within the community to expand the reach of this survey next year.

After an overly long development cycle, I’m pleased to introduce the latest release of phpPgAdmin, version 7.12.0.

As with many software releases, the code changes are plenty, and the release bullets are few, but they are quite important. In this release we have:

• PHP 7 is now the default version for development, and the minimum version required for phpPgAdmin going forward. Most users are currently running PHP 7, so we’re happy to support this going forward, and encourage users of PHP 5.x to upgrade for continued support.

• We’ve added support for all current versions of PostgreSQL, including the pending PostgreSQL 12 release. Our aim going forward will be to ensure that we are properly supporting all current release of Postgres, with degraded support for EOL versions.

• We’ve updated some internal libraries, fixed additional bugs, and merged many patches that had accumulated over the years. We want to thank everyone who provided a patch, whether merged or not, and hope you will consider contributing to phpPgAdmin in the future.

This version also comes with a change to our development and release cycle process. When the project originally started, we developed and released new versions like traditional desktop software; annual-ish releases for new versions with all the new features, while providing a few periodic bugfix releases in between. While this was ok from a developers point of view, that meant users had to wait for months (and in unfortunate cases, years) between releases to get new code. As developers, we never felt that pain, because developers would just run code directly from git master. As it turns out, that is a much better experience, and as much of the software world has changed to embrace that idea, our process is going to change as well.

The first part of this is changing how we number our releases. Going forward, our versions numbers will represent:

- the primary PHP version supported (7), 
- the most recent version of PostgreSQL supported (12), 
- and the particular release number in that series (0).   

Our plan is to continue developing on this branch (7_12) and releasing new features and bug fixes as often as needed. At some point about a year from now, after PostgreSQL has branched for Postgres 13/14, we’ll incorporate that into an official release, and bump our release number to 7.13.0. Presumably, in a few years, there will eventually be a release of PHP 8, and we’ll start planning that change at that time. We hope this will make it easier for both users and contributors going forward.

For more information on phpPgAdmin, check out our project page at https://github.com/phppgadmin/phppgadmin/

You can download the release at: https://github.com/phppgadmin/phppgadmin/releases/tag/REL_7-12-0

Once again, I want to thank everyone who has helped contribute to phpPgAdmin over the years. The project has gone through some ups and downs, but despite that is still used by a very large number of users and it enjoys a healthy developer ecosystem. We hope you find this new release helpful!

This post marks the official launch of the Charm City Postgres Meetup group. Our goal is to help bring together the growing community of Developers, DBAs, DBREs, and other technologist in the greater Baltimore area who are working with Postgres. We’re currently working to organize folks and would encourage interested people to sign-up on the meetup site, and have set an initial meetup at Smart Office @ BWI. If you have questions or want to speak, sponsor, or host a meeting in the future, you can contact me either through the meetup page or find me on the Postgres Team Slack.

One of the big features talked about when PostgreSQL 11 was released was that of the new stored procedure implementation. This gave Postgres a more standard procedure interface compared to the previous use of functions. This is perticularly useful for folks who are doing database migrations where they may have been using the standards CALL syntax vs Postgres traditional use of SELECT function(); syntax. So it struck me as odd earlier this year when I noticed that, despite the hoopla, that a year later that there was almost zero in the way of presentations and blog posts on either the new stored procedure functionality or the use of plpgsql in general.

And so I got the idea that maybe I would write such a talk and present it at PGCon; a nod to the past and the many years I’ve spent working with plpgsql in a variety of roles. The commitee liked the idea (disclosure that I am on the pgcon committee, but didn’t advocate for myself) and so this talk was born. For a first time talk I think it turned out well, though it could definitly use some polish; but I’m happy that it did help spark some conversation and actually has given me a few items worth following up on, hopefully in future blog posts.

Video should be available in a few weeks, but for now, I’ve gone ahead and uploaded the slides on slideshare.