Nice Postgres Feature: LATERAL

September 12th, 2023

PostgreSQL.jpg

There are many times when you would like a sub-select query to be constrained on one of the values of the main query, but when you attempt to do that you get an error message about not being able to use the variable in this context. For example, this query:

  SELECT c.id, c.company_name, pb.available, pb.current,
         date_trunc('second', pb.as_of) AS as_of, pbs.*
    FROM companies c, plaid_tokens pt, plaid_balances pb,
         (SELECT SUM(available) AS all_available,
                 SUM(CURRENT) AS all_current
            FROM plaid_balances WHERE company_id=c.id) pbs
   WHERE pt.id = (c.additional_info->>'primaryPlaidAccount')::uuid
     AND pt.account_id = pb.account_id

where the goal is to have a sub-select gather the sum of the individual columns being pulled in the main query. It's a nice thing to have, but the inability to have c.id used in the sub-select really makes it difficult.

Postgres has a nice feature in LATERAL, where is allows the sub-select to reference these fields by changing the order of evaluation of the sub-select and doesn't penalize the performance too much.

  SELECT c.id, c.company_name, pb.available, pb.current,
         date_trunc('second', pb.as_of) AS as_of, pbs.*
    FROM companies c, plaid_tokens pt, plaid_balances pb,
         lateral (SELECT SUM(available) AS all_available,
                         SUM(CURRENT) AS all_current
                    FROM plaid_balances WHERE company_id=c.id) pbs
   WHERE pt.id = (c.additional_info->>'primaryPlaidAccount')::uuid
     AND pt.account_id = pb.account_id

This is still quick, and it saves the machinations of having to calculate the sums in a temp table, or write a function to do this... it's just a nice little trick that they put in the language. Very considerate. 🙂

The Passing of a Legend

August 6th, 2023

vim.jpg

This morning, a friend sent an email with a link mentioning the passing of Bram Moolenaar, the initial creator of Vim. There aren't many folks who have impacted my professional life as much as the creators of Vi, and then Bram and Vim.

I remember first using Vi at Purdue in my final year of Undergrad on the ADM terminals, and then making sure I could find termcap entries for all the terminals I could get my hands on in the days when you had terminals hooked by serial lines to the Dual VAX 11/780 system at the Electrical Engineering Department. After that, it was Auburn, and Vim on my Amiga, and then Mac System 6, and on virtually every system I had from 1985 on.

The only tool that even comples close to that longevity is GCC.

I know nothing lasts forever, and I know people pass on, but I honestly didn't expect to be so surprised by this news. I read it again, a couple of hours later on one of the RSS Feeds I read on my iPad, where, again, I have Vim. Still the same sense of sadness.

Ask not for whom the bell tolls...

Getting the Water Tested

July 10th, 2023

Government and Laws

This morning they dropped off the sample container for the Water Testing that Naperville is doing once a year. It's completely voluntary, and funded by the Town so that those of us that live in very old homes can be assured that the lead and other toxins in the water supply are will within the EPA limits.

I think it's been happening for about three years now, and I sign up for it each year because all I have to do is to take the sample, and then sit it on my doorstep, and I get a letter in a few weeks with the results of the tests. They have always been far below any threat levels of the EPA, so I'm not concerned, but I'll always accept a free environment test to make sure things are safe in my environment.

I do love this Town. 🙂

Visting with My Cousin Murry

July 8th, 2023

Path

Today I traveled to Indy to have a nice get-together with my siblings and our cousin Murry, on our Dad's side of the family. He's my age, and has spent almost all his adult life in Finance - running a Hedge Fund for a while before he rolled it up in 2022. He's now in Miami and doing a lot of traveling, and this stop in Indy was part of his "Touch Base with Family 2023" Tour.

A few months ago, I reached out to Murry and added him to my Sunday Messages list, where I send a little message about the family and how we're doing, and it's nothing special, but it keeps us in touch. I've been doing this with family and friends for a few years, and it's something I really feel is important.

Well... Murry is doing fine, and everyone had a good time. I really think this is good - the reconnections. Someday soon, there will be fewer of us...

Nice Postgresql Trick

June 29th, 2023

PostgreSQL.jpg

This morning I really wanted to be able to set the psql prompt in my sessions because the system we have for creating databases doesn't really create nicely human-readable name, and even so, I'd like to have the name in the prompt match the branch of the code I'm working on... it just works out better.

So I started digging, and the -c parameter is OK, but the psql session terminates after that - so that's not going to work. And piping in the \set commands seemed to be problematic, and then I found this:

  $ psql --set=PROMPT1="${br}%R%#%x "

where br is the name of the branch I'm working on. This could then be obtained from git easily, and then put into a function, and it works great!

  #
  # "platter psql (postgres command line tool)" - start a psql session on
  # the provided branch in the PLATTER_INSTANCE in the .env file in the
  # current directory (a Node project repo). If no branch is provided, then
  # the current git branch will be used.
  #
  function ppsql() {
    if [ -f .env ]; then
      set -o allexport; source .env; set +o allexport
      local br=$1
      if [ ${#br} -eq 0 ]; then
        br=`cat .git/HEAD | sed -e 's:^.*/::'`
      fi
      local url="`npx platter postgres branch url ${br} \
           --instance $PLATTER_INSTANCE | tr -d '\n'`?sslmode=require"
      psql --set=PROMPT1="${br}%R%#%x " --set=PROMPT2="${br}%R%#%x " $url
    else
      echo "Not in a Node project directory!"
    fi
  }

With this, it's so easy now to be able to keep track of the database (branch) I'm on with Platter, and that makes a really big different to my peace of mind. 🙂

Ivory for Mastodon for macOS

May 23rd, 2023

Ivory

I just downloaded the Ivory for macOS app from the App Store, and upgraded my subscription to the Universal Subscription, so that it would cover all my devices. It's just an amazing app. This iOS/iPadOS version has been working really well for me since it was released, and now that I have the macOS version, I'll not have to worry about Ice Cubes and reading two streams.

I don't know that Mastodon will ever be popular enough to have some of the feeds from Twitter on it, but that's OK... I'm at about 95% complete, and I don't really need the last 5%. Plus, you just can't beat that it's not under the thumb of a capricious individual.

All in all... a nice bit of news for today. 🙂

Interesting Issues with Clearbit

April 24th, 2023

Alarm icon

This weekend we had an interesting issue with Clearbit's Logo Search interface - a free service they provide on their Business Information Service system. You can basically hit their endpoint with a query param of the name of a Company, and they will respond with something that looks like:

  {
    name: 'Flexbase',
    domain: 'flexbase.app',
    logo: 'https://logo.clearbit.com/flexbase.app'
  }

which is a nice little thumbnail logos of the Company. It's a very nice tool, and for the most part works flawlessly.

Until it doesn't.

The real issue was the Open Source Node client that was hitting the company's endpoint. It started with:

  topSuggestion(name){
    return new Promise((resolve, reject) => {
      resolve(getTopSuggestion(name));
    });
  }

which called:

  let getTopSuggestion = (query) => {
    return new Promise((resolve, reject) => {
      request(config.api.autocomplete + '?query=' + query, (err, response, body) => {
        resolve(JSON.parse(body)[0]);
      });
    });
  }

Now when everything is working as it should, this code is just fine. But on the weekend, the response from the endpoint in getTopSuggestion() was returning:

  Welcome to the Company API. For docs see https://clearbit.com/docs#company-api

which, of course, isn't JSON, and so the JSON.parse() was throwing an exception. But the function getTopSuggestion() was using the resolve() for the Promise, so it was creating an exception that could not be caught. This was bad news.

Now as it turned out, a coworker found that Clearbit was doing some maintenance, and that might have been the genesis of the issue, but it was made much worse because when we debugged this on our machines - several of us, the issue didn't present itself. Only in production.

Still, it was clear this wasn't the right code to use, and the library was 6 years old without an update, and the code was small. So a coworker suggested we just make the one call ourselves:

    let res = {}
    try {
      // Get the top URL Suggestion for a store name
      const url = new URL(config.api.autocomplete)
      url.searchParams.append('query', name)
      // ...now make the call, and parse the JSON payload
      const payload = await fetch(url).then(response => response.json())
      if (Array.isArray(payload) && payload.length > 0) {
        // ...pick off the top suggestion
        res = payload[0]
      }
    } catch (err) {
      log.error(`[logoFinder] ...message... Name: '${name}', Details: "${err.message}"`)
      return {
        success: false,
        error: errorMessages.badClearBitRequest,
        exception: err,
      }
    }
    return {
      success: true,
      ...res,
    }
  }

where the error message is really up to you, but the point was that this was something that would handle the simple text being returned by the endpoint and throw the exception on the JSON parsing without causing all the trouble of the library we were using.

There were a few things I liked about the new implementation we came up with:

  • Explicitly setting the query param on the URL - while it's possible that 90% of all name values would not lead to an issue, it's always nice to be safe and make sure that the proper encodings are done with the query params. It's two lines of code, but it makes sure that it's all handled properly.
  • The chaining of fetch() and then() - both fetch() and response.json() are async functions, so you might expect to see two await prependers on the functions, but there's only one. This is a nice feature of the then(), in that it unrolls the async nature of the fetch() so that the async nature of the .json() comes through - returning the value to the caller.

Sure, we still need to get the first element in the Array, but we also test that to make sure it's actually an array, and that there's something to get. It's just a lot more defensive coding than the original client had, and when we did this, we still got the good results on the dev machines, and at the same time, we got proper exception catching on the production instances.

Thankfully, the issues resided about the time we got the fix into the code, tested, and into production, so it wasn't long-lived, but it was a problem for a while, and we were able to recover the errors due to queues and retries, which is another saving grace that I was very thankful for.

Nothing like a little production outage to make the day exciting. 🙂

It’s been a Wild Month

March 28th, 2023

WallSt.jpg

Over the course of the last few weeks, we've seen the collapse of Silicon Valley Bank, and Signature Bank, and the Feds stepping in to reassure the depositors that everything will be OK, every deposit will be made whole, and the only losses will be to the stock holders of SVB. It's something that they didn't have to do, by regulation, but they did to return confidence to the smaller banks, and keep the deposits from being moved to the top-10 banks, and cause a real problem in all the smaller banks.

Still, it's faded from the headlines, and while The Journal is still covering it, it's not USA Today anymore, and that means that the bulk of the crisis is over - that of the lack of confidence in the system. People are no longer concerned, and that's really very good news.

The job market is still a mess... and it's going to take the financial markets to stabilize and start to head up, for the job market to return. It's confidence that drives good hires, not desperation. Still, this is just another cycle, and we'll weather it.

There are better days ahead. 🙂

Trip to the Dentist

February 28th, 2023

Path

Today is another trip to the dentist, and while I'm getting to the point that I can pretty much deal with these things, I won't be sorry when I don't have to. I know they mean well, and I know it's childhood trauma, but it's also the experience you have with the dentist, and therein lies a tale.

Today is two weeks after a procedure that was performed with such brutality, it set me back at least a decade in my comfort level with the dentist. It was a new guy, and he was brutal... just brutal. But it was done, and I counted my blessings that it was done. Now I have to go back for a post-op check, and get the stitches removed. I'm not looking forward to it.

I know it'll be over, and that's what I have to look forward towards, because thinking about what happened two weeks ago is mighty painful. But I'll survive.

Ivory for Mastodon by Tapbots

January 30th, 2023

Ivory

With the Early Release of Ivory by Tapbots, I think I've found the clear winner for a Mastodon client. This is very good - and it's got a lot more work that they want to do on it... so it's only going to get better.

Right now, it's got everything I used on Twitter, plus other nice features that are part of Mastodon natively. All in all, it's actually a nice upgrade.

I would still like to see the local town alerts (weather, service interruptions, etc.) come to Mastodon, but even if they don't, it's far better than whatever Twitter is turning into.