Our Blog

Temporary tables in SQL query optimization

By Jeff Boes
February 25, 2015

SQL queries can get complex in a big hurry. If you are querying multiple tables, and in particular if your query involves operations like UNION and INTERSECT, then you can find yourself in a big, messy pile of SQL. Worse, that big pile will often run slowly, sometimes to the point where a web application times out!

I won’t inflict a real-world example on you; that would be cruel. So let’s look at a “toy” problem, keeping in mind that this won’t illustrate any time-savings, just the technique involved.

Here’s the original SQL:

SELECT p.* FROM products p
JOIN (SELECT * FROM inventory WHERE /* complex clause here */) i USING (sku)
UNION ALL
SELECT p.* FROM clearance_products p
JOIN (SELECT * FROM inventory WHERE /* complex clause here */) i USING (sku)

Bonus hint: using “UNION ALL“ instead of just “UNION” will allow the query processor to skip an unnecessary step here. “UNION ALL” says you know the rows on either side of the clause are unique. “UNION” means the results will be post-processed to remove duplicates. This might save you more than a smidgen of time, depending on how large the two sub-queries get.

Now, many times the query optimizer will just do the right thing here. But …

Postgres ON_ERROR_ROLLBACK explained

By Greg Sabino Mullane
February 24, 2015

(picture by Steve Wilson)

Way back in 2005 I added the ON_ERROR_ROLLBACK feature to psql, the Postgres command line client. When enabled, any errors cause an immediate rollback to just before the previous command. What this means is that you can stay inside your transaction, even if you make a typo (the main error-causing problem and the reason I wrote it!). Since I sometimes see people wanting to emulate this feature in their application or driver, I thought I would explain exactly how it works in psql.

First, it must be understood that this is not a Postgres feature, and there is no way you can instruct Postgres itself to ignore errors inside of a transaction. The work must be done by a client (such as psql) that can do some voodoo behind the scenes. The ON_ERROR_ROLLBACK feature is available since psql version 8.1.

Normally, any error you make will throw an exception and cause your current transaction to be marked as aborted. This is sane and expected behavior, but it can be very, very annoying if it happens when you are in the middle of a large transaction and mistype something! At that point, the only thing you can do is rollback the transaction and lose all of your work. For …

Testing your chef repo pull requests with chef-zero, Vagrant and Jenkins

By Wojtek Ziniewicz
February 18, 2015

All Liquid Galaxy setups deployed by End Point are managed by Chef. Typical deployment consists of approx 3 to 9 Linux boxes from which only 1 is managed and the rest is an ISO booted from this machine via network with copy-on-write root filesystem. Because of this, typical deployment involves more steps than just updating your code and restarting application. Deployment + rollback may be even 10 times longer compared with typical web application. Due to this fact, we need to test our infrastructure extensively.

What are we to do in order to make sure that our infrastructure is tested well before it hits production?

Scary? It’s not.

Workflow broken down by pieces

• lg_chef.git repo—​where we keep cookbooks, environments and node definitions
• GitHub pull request—​artifact of infrastructure source code tested by Jenkins
• Vagrant—​virtual environment in which chef is run in order to test the artifact. There’s always 1 master node and few Vagrant boxes that boot an ISO from master via tftp protocol
• chef-zero—​Chef flavor used to converge and test the infrastructure on the basis of GitHub pull request
• chef-server/chef-client—​Chef flavor used to converge and test production and …

Postgres pg_dump implicit cast problem patched!

By Greg Sabino Mullane
February 16, 2015

(image of brain coral by Lara Danielle)

One of the many reasons I love Postgres is the responsiveness of the developers. Last week I posted an article about the dangers of reinstating some implicit data type casts. Foremost among the dangers was the fact that pg_dump will not dump user-created casts in the pg_catalog schema. Tom Lane (eximious Postgres hacker) read this and fixed it up—​the very same day! So in git head (which will become Postgres version 9.5 someday) we no longer need to worry about custom casts disappearing with a pg_dump and reload. These same-day fixes are not an unusual thing for the Postgres project.

For due diligence, let’s make sure that the casts now survive a pg_dump and reload into a new database via psql:

psql -qc 'drop database if exists casting_test'
psql -qc 'create database casting_test'
psql casting_test -xtc 'select 123::text = 123::int'
ERROR:  operator does not exist: text = integer
LINE 1: select 123::text = 123::int
                                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

psql casting_test -c 'create function pg_catalog.text(int) …

Postgres custom casts and pg_dump

By Greg Sabino Mullane
February 10, 2015

We recently upgraded a client from Postgres version 8.3 to version 9.4. Yes, that is quite the jump! In the process, I was reminded about the old implicit cast issue. A major change of Postgres 8.3 was the removal of some of the built-in casts, meaning that many applications that worked fine on Postgres 8.2 and earlier started throwing errors. The correct response to fixing such things is to adjust the underlying application and its SQL. Sometimes this meant a big code difference. This is not always possible because of the size and/or complexity of the code, or simply the sheer inability to change it for various other reasons. Thus, another solution was to add some of the casts back in. However, this has its own drawback, as seen below.

Image by Tarek Mohamed

While this may seem a little academic, given how old 8.3 is, we still see that version in the field. Indeed, we have more than a few clients running versions even older than that! While pg_upgrade is the preferred method for upgrading between major versions (even upgrading from 8.3), its use is not always possible. For the client in this story, in addition to some system catalog corruption, we wanted to move to using data …

Updated End Point Blog Stats and Our Services

By Steph Skardal
February 10, 2015

Today, I sat down to read through a few recent End Point blog articles and was impressed at the depth of topics in recent posts (PostgreSQL, Interchange, SysAdmin, Text Editors (Vim), Dancer, AngularJS) from my coworkers. The list continues if I look further back covering technologies in both front and back end web development. And, this list doesn’t even cover the topics I typically write about such as Ruby on Rails & JavaScript.

While 5 years ago, we may have said we predominately worked with ecommerce clients, our portfolio has evolved to include Liquid Galaxy clients and many non-ecommerce sites as well. With the inspiration from reading through these recent posts, I decided to share some updated stats.

Do you remember my post on Wordle from early 2011? Wordle is a free online word cloud generator. I grabbed updated text from 2013 and on from our blog, using the code included in my original post, and generated a new word cloud from End Point blog content:

End Point blog Word cloud from 2013 to present

I removed common words from the word cloud not removed from the original post, including “one”, “like”, etc. Compared to the original post, it looks like database related …

Interchange Loop Optimization

By Mark Johnson
February 9, 2015

It’s important to understand both how loops work in Interchange and the (very) fundamental differences between interpolating Interchange tag language (ITL) and the special loop tags (typically referred to as [PREFIX-*] in the literature). Absent this sometimes arcane knowledge, it is very easy to get stuck with inefficient loops even with relatively small loop sets. I’ll discuss both the function of loops and interpolation differences between the tag types while working through a [query] example. While all loop tags–[item-list], [loop], [search-list], and [query]–process similarly, it is to [query] where most complex loops will gravitate over time (to optimize the initiation phase of entering the loop) and where we have the most flexibility for coming up with alternative strategies to mitigate sluggish loop-processing.

Loop Processing

All loop tags are container tags in Interchange, meaning they have an open and close tag, and in between is the body. Only inside this body is it valid to define [PREFIX-] tags (notable exception of [PREFIX-quote] for the sql arg of [query]). This is because the [PREFIX-] tags are not true ITL. They are tightly coupled with the structure …

Cron Wrapper: Keep your cron jobs environment sane

By Richard Templet
February 6, 2015

It is becoming more common for developers to not use the operating system packages for programming languages. Perl, Python, Ruby, and PHP are all making releases of new versions faster than the operating systems can keep up (at least without causing compatibility problems). There are now plenty of tools to help with this problem. For Perl we have Perlbrew and plenv. For Ruby there is rbenv and RVM. For Python there is Virtualenv. For PHP there is PHP version. These tools are all great for many different reasons but they all have issues when being used with cron jobs. The cron environment is very minimal on purpose. It has a very restrictive path, very few environment variables and other issues. As far as I know, all of these tools would prefer using the env command to get the right version of the language you are using. This works great while you are logged in but tends to fail bad as a cron job. The cron wrapper script is a super simple script that you put before whatever you want to run in your crontab which will ensure you have the right environment variables set.

#!/bin/bash -l

exec "$@"

The crontab entry would look something like this:

34 12 * * * bin/cron-wrapper …