Our Blog

Pretty Printing JSONs in PostgreSQL

By Szymon Lipiński
July 25, 2013

PostgreSQL has huge support for JSON type, like I wrote recently. It also has some operators for converting data to and from JSON, and the JSON type itself is great for ensuring that the JSON stored in database is always valid.

Pretty Printing JSON

The Problem

JSONs can be quite complicated and can have multiple levels. Look at them as normal strings: printing the values can increase their readability. Let’s use a sample JSON like:

{"a":42, "d":{"a":10, "b":[1,2,3], "c":"x2"}, "x":"test", "p":[1,2,3,4,5]}

I think it would be much readable in the form:

 {
     "a": 42,
     "d": {
         "a": 10,
         "b": [
             1,
             2,
             3
         ],
         "c": "x2"
     },
     "p": [
         1,
         2,
         3,
         4,
         5
     ],
     "x": "test"
 }

The Solution

To generate this kind of format, I created a very simple Python function:

CREATE FUNCTION pp_json(j JSON, sort_keys BOOLEAN = TRUE, indent TEXT = '    ')
RETURNS TEXT AS $$
  import simplejson as json
  return …

GNU Screen logtstamp string

By Jon Jensen
July 24, 2013

A short note on GNU Screen configuration:

You can add configuration to ~/.screenrc or another configuration file named by -c $filename upon invocation, and among the many options are some to enable logging what happens in the screen windows. This is useful when using screen as a reattachable daemonizer.

Consider this configuration:

logfile path/to/screen-output.%Y%m%d.log
logfile flush 1
logtstamp on
logtstamp after 5
log on

That works nicely. With logfile we specify the name of the log file, using some % escapes as per “STRING ESCAPES” in the manpage to put the date in the logfile name.

With logfile flush 1 we request that every 1 second the output be flushed to the log, making it easier to follow with tail -f.

The logtstamp on option writes a timestamp to the log after a default 2 minutes of inactivity. We shorten that to 5 seconds with logtstamp after 5.

Finally, log on turns on the logging.

Now, what if we want to customize the timestamp? The default looks like this:

-- 0:process-name -- time-stamp -- Jul/24/13  9:09:56 --

The manpage says that can be customized with logtstampt string ..., where the default is:

-- %n:%t -- time-stamp -- %M/%d/%y %c:%s --\n

The manpage earlier …

has_many filter in RailsAdmin

By Marina Lohova
July 18, 2013

I enjoyed using the RailsAdmin record filtering abilities until one day I needed to find all the orders with the specific product.

class Order < ActiveRecord::Base
   has_many :products, :through => :orders_products
end

The following valid piece of RailsAdmin configuration did not break anything but did not work either:

RailsAdmin.config do |config|
  config.model Order do
    list do
      field :products do
        searchable :name 
      end
    end
  end
end

The reason is that only the belongs_to association is enabled for the search, as stated in the “Field searching” section of the documentation:

(3) Belongs_to associations : will be searched on their foreign_key (:team_id) 
or on their label if label is not virtual (:name, :title, etc.)

Benoit Bénézech, creator of RailsAdmin, confirmed this as well:

has_many are not added to the include for perf reason. That means that AR won’t find the :programs table

We only had a few has_many fields configured across the project, so I decided to look into the source code and see if the limitation can be bypassed.

MainController class in RailsAdmin invokes the “get_collection” method to fetch the records for the list action. It …

PostgreSQL Autovacuum Issues In EOL Postgres

By David Christensen
July 17, 2013

We recently had a web application shut down and start throwing PostgreSQL errors such as the following:

ERROR:  database is shut down to avoid wraparound data loss in database "postgres"

HINT:  Stop the postmaster and use a standalone backend to vacuum database "postgres"

This is of course the dreaded error message that occurs when you get close to the transaction wraparound point, and PostgreSQL refuses to continue to run in server mode.

This is a situation which occurs when vacuuming is not run regularly (or at all, considering that autovacuum has been enabled by default since PostgreSQL 8.2), so this is rare to see in actual usage.

The particular installation we were looking at was an older one, running PostgreSQL 8.1, which had been included as the base PostgreSQL version with RHEL 5. (To stave off the inevitable comments: yes, upgrading is a good idea, considering 8.1 has been End-Of-Life’d for years now. This isn’t the point of this article.)

After running postgres in single-user mode and running VACUUM FULL on all of the databases, I started the cluster back up and started to see why we ran into the wraparound issue.

Using psql, I verified that the …

Speedier column additions in PostgreSQL

By Jeff Boes
July 17, 2013

Say you want to add a column to a large table, e.g.,

ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT FALSE;

You really do want new rows to start out with the column “false” (if no value is supplied when the row is created). However, you also want all existing rows to be “true”, so innocently:

UPDATE transactions SET email_sent = TRUE;

This is a great time for a coffee break, or a trip to the post office, or maybe (if you’re a telecommuter like me), a stroll out to weed the garden. Unfortunately for all those side activities, you really didn’t have to take the long way.

BEGIN;
ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT TRUE;
ALTER TABLE transactions ALTER TABLE email_sent SET DEFAULT FALSE;
COMMIT;

This is a lot faster; create all the columns with the appropriate value, then set the default for new rows, and all inside a transaction so you know it gets done atomically.

Kamelopard update—​Panoramic camera simulation, and splines have returned

By Josh Tolley
July 16, 2013

A few days ago I pushed Kamelopard version 0.0.12 to RubyGems. This version includes a couple big items. The first of these is a new implementation of the spline code that was removed a while ago. As I mentioned in a previous blog post, this original implementation was built in anticipation of an API that never materialized. The new version is built on the same API discussed in the previous post I mentioned, modified to support multidimensional functions. More information about these splines is available on the wiki for Liquid Galaxy.

The other big new feature is the ability to simulate multiple-camera panoramic images, which we’re calling “multicam”. It has its own wiki page as well, but I wanted to describe it in greater detail, because there’s a fair bit of 3D geometry involved that seemed blog-worthy. First, though, it’s important to understand the goal. In a Liquid Galaxy, each instance of Google Earth displays the view from one virtual “camera”. One display’s camera points exactly where you tell it to point; the others point to one side or the other, based on a few settings in a Google Earth configuration file. When placed side-by-side in the right order, these displays form …

Vim tabs and splits

By Mike Farmer
July 15, 2013

Vim is my go-to code editor these days. After many years of using different editors, I’ve settled on Vim as my editor of choice. There are some things I’ve done to make using Vim more enjoyable and usable and this blog post is dedicated to some of those things that I use everyday.

Tabs

I love using tabs in Vim. I know there are some who are opposed to tabs, but I find them invaluable. The default shortcuts for manipulating tabs are a little cumbersome, which I believe deters many users. Here are some modifications that I added to my vimrc.

nmap <silent> tt :tabnew<CR>
nmap <silent> [g :tabprevious<CR>
nmap <silent> ]g :tabnext<CR>
nmap <silent> [G :tabrewind<CR>
nmap <silent> ]G :tablast<CR>

First, I’m using nmap here which says to only map these keys in normal mode. Next, I use which keeps my editor clean of any distractions while performing the task. I find that the double tap short-cuts (see more below) tt work really well for normal mode and I love their simplicity. Double-tap t and you have a new tab. Using the bracket navigation is something that I’ve stolen from Tim Pope’s vim-unimpaired plugin. Using g and G work …

SFTP virtual users with ProFTPD and Rails: Part 2

By Brian Gadoury
July 10, 2013

In Part 1 of “SFTP virtual users with ProFTPD and Rails”, I introduced ProFTPD’s virtual users and presented my annotated proftpd.conf that I used to integrate virtual users with a Rails application. Here in Part 2, I’ll show how we generate virtual user credentials, how we display them to the user, as well as our SftpUser ActiveRecord model that does the heavy lifting.

Let’s start at the top with the SFTP credentials UI. Our app’s main workflow actually has users doing most of their uploads through a sweet Plupload widget. So, by default, the SFTP functionality is hidden behind a simple button sitting to the right of the Plupload widget:

The user can click that button to open the SFTP UI, or the Plupload widget will open it automatically if the user tries to upload a file through the widget that is too big. Either way, it uses a jQuery UI function to slide the SFTP UI open. Before it makes the jQuery call, an Ajax request is made to request a new SFTP virtual user username and password. When that request returns, we populate those two textboxes. At that point, that virtual user exists as a new row in the sftp_users database table. At this point in the workflow, the user will be …