Do This 10 Times and Stop… In Postgres

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.

phpPgAdmin 7.13.0 Released

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

New Life Experiment: One Car Family

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!

The Golden Globes Meets Safety Science

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.

phpPgAdmin 7.12.1 Released

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!

5 Quick Thoughts on the State of Postgres Survey

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.

Introducing phpPgAdmin 7.12.0

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!

Charm City Postgres

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.

The Lost Art of Plpgsql

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.

The Ghost of phpPgAdmin

TLDR; This evening I put the final blotches on to a new release of phpPgAdmin 5.6. This release adds official support for all recent Postgres versions, fixes a number of smaller bugs, and includes several language updates. While I think upstream packagers need not worry about absorbing this release, I’ve made downloads generally available from the Github project page, or you can just pull from git to get the latest code. Note this release is designed to run on PHP 5.6.

Now for the backstory…

After much hoopla a few years back about new admin clients and talk of the pgAdmin rewrite, most of the regular contributors had pretty much moved on from the project, hoping to see a clearly better admin tool surface as a replacement. Instead, I saw multiple projects launch, none of which captured the hearts and minds so to speak, and saw the number of pull requests on an ever more abandonded looking project continue to pile up, not to mention thousands of downloads.

As for me, while not doing much publically, privately I was still maintaining two private copies of the code, one which had support for newer Postgres servers, and one which had support for PHP 7; both in rough shape. While my schedule doesn’t leave much time for random hacking, about a month ago I saw an upcoming block where I would be conferencing three weeks in a row and suspected I could probably find some time during my travels to do some updates. After a little bit of thought, I decided to do two releases. The first would add support up through Postgres 11, the most recently released version of the server software, and the second would add the aforementioned PHP 7 support. Granted, it’s taken longer than I had hoped, probably mostly because that’s how software engineering works, but also because I had to literally relearn how it is we were running this project, but I think I’ve got most of that worked out now.

I suspect the two releases might annoy some people, given that PHP 5.6 is years old and in many peoples minds EOL. But it turns out that a lot of people still run various 5.x versions of PHP, so this is a nod to that user base. If you are one of the people who has been waiting for a PHP 7 release, don’t worry. As mentioned I already have a patch set, so I’m hoping to have that completed in the next couple of weeks. Once that is released, I think it will make for a good base to start adding new features again; there is a bunch of stuff that could be added into phpPgAdmin, it’s just a matter of re-igniting the engine, so to speak.

In any case, there is life again in the old project. Long live open source.