Handy Little Items For Postgres

Time Zone Display

Postgres always stores a timestamp with no time zone. The data types TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE are both misnomers. A better name would be WITH REGARD TO TIME ZONE. 

The "without time zone" command ignores any time zone or offset information included with data input. The date-time value is stored without any adjustment and without any recording of the specified time zone or offset. This is rarely useful, as explained by Postgres expert David E Wheeler. 

The "with time zone" means that any time zone or offset information included with data input is used to adjust the stored date-time to UTC. Any such time zone or offset info is discarded after the adjustment, not stored as the name suggests.

When displaying timestamps, Postgres applies the client's default time zone when generating a string value. What if you want to display the date-time values in UTC? 

    SET TIME ZONE 'UTC';

What if you want to display in a specific time zone? Use a proper time zone name, usually a continent/city (or region). Avoid the 3 or 4 letter codes such as EST or IST as they are neither standardized nor unique.

    SET TIME ZONE 'America/Montreal';

This command affects the current session only. For example, type into a "Query" window in pgAdmin.

BYTEA Display

The BYTEA data type in Postgres is akin to a BLOB in other databases. I'm guessing the name is short for "byte array". This data type is a sort of anti-data-type. When specifying BYTEA, you are telling Postgres to not bother parsing or interpreting the data. You are saying "take these bytes as-is and save them to storage without looking at them".

Since Postgres has no idea what those bytes mean, it has no idea how to display them. In the old days, Postgres displayed them in a sequence of escapes. Nowadays you have the choice of displaying as hexadecimal. But how to specify which?

    SET bytea_output = "hex";
    SET bytea_output = "escape";

 This command affects the current session only.

What Is Whitespace?

In some Java work I needed to scrub some NO-BREAK SPACE characters from some import data. In looking for a command in Java to trim the leading and trailing whitespace, I fell down a Rabbit Hole.

Turns out that the Java String class offers a trim command. But that command has a strange definition of whitespace. Read this blog post by Mike Kaufman for details. The upshot: 'trim' only deletes characters numbered 32 (U+0020, SPACE) and lower.

Then I find an interesting spreadsheet, whitespace? what's that?, listing the various definitions of whitespace in Java and Unicode. There is a lot going on in the nothingness of whitespace!

CharMatcher – Google Guava

Eventually I found a modern, flexible, easy-to-use solution: `CharMatcher` in Google Guava. See their brief guide. By making clever use of Predicate syntax, they make it easy to mix and match various groups of whitespace, invisible, and control characters. You can trim from the front and/or back of a string, replace, and more.

Example usage:

someText = CharMatcher.WHITESPACE.trimFrom( someText );

Just use 'TEXT' type in Postgres

Sometimes it is the little things that trip you up, especially when learning a new tool. Like which data type to choose when creating a new database in a SQL system. But not so simple.

Here are two posts that make a strong case for just using TEXT in Postgres while avoiding VARCHAR and CHAR. I agree. Wish I'd seen this long ago.

• PostgreSQL: Don't Use CHAR or VARCHAR by Jon Morton, with important comments by Josh Berkus.
• VARCHAR(n) Considered Harmful by David Fetter.

And for more info, experimentation, and discussion, see the post CHAR(X) VS. VARCHAR(X) VS. VARCHAR VS. TEXT by depesz.

The upshot is:

• Use the TEXT type for all your textual needs.
• If constraining the maximum, minimum, and content is important then define a CONSTRAINT. (No need to depend on the max length feature of varchar.)

As counter argument, see IN DEFENSE OF VARCHAR(X) by Leo Hsu and Regina Obe.

Chores In TestFlightApp

The TestFlightApp.com people provide an amazing service with relatively good documentation. Unfortunately they fail to provide the most simple doc for the regular chores. I only perform these chores only on occasion, and always forget the required steps.

So here’s a blog post to help remind myself. This information is for the old site before Apple acquired this company. Apple has begun pushing this service explicitly through Apple, but I continue to use the old web site; not sure if the new Apple way is different.

Add A New User

Someone wants to join my team of testers. They have no account on TestFlightApp.com.

1. I login to TestFlightApp.com.
2. I click the "Dashboard" tab in their web site.
3. I click the third and last big green button, "Invite People".
4. I enter their email address, and send an invitation.
5. Wait for tester to do their part. Email goes out within minutes. If unreceived, remind tester to check their junk filters & folders.

The prospective tester takes their turn.

1. Tester picks up the iOS device on which they want to test.
2. On that iOS device tester receives email from TestFlightApp.com, with subject line: "Basil Bourque has invited you to join…".
3. Tester clicks big green "Accept" button.
   I'm not sure exactly what happens next. I think the button opens a web page to the TestFlightApp.com site which then tries to open a URL for their iOS app called "TestFlight". The user does not have such an app, so the App Store appears with an offer to purchase this app at no cost. 
4. User proceeds with purchase of "TestFlight" app.
5. App opens and prompts user to create a personal account on TestFlight, with the user providing their email and inventing a password.
6. During this process, TestFlight logs this particular device's unique identifier with the TestFlightApp.com web site.

After the user has their device’ identifier logged, TestFlightApp.com sends me an email with Subject line: "So-And-So accepted your invitation". The message includes the device’s identifier. 

Register Device Identifiers

A later goal is building a fresh "Provisioning Profile". To that we must register with Apple a lilt of device identifiers to be authorized to run our app. Our new user's device identifier must be added to our list registered with Apple.

The email mentioned above contains that identifier.

If you lose that email providing the identifier, or you want to look up some other testers’ device identifiers, use the TestFlightApp.com website. Click the main tab for "People". Select the checkboxes of those testers with device(s) that Apple has not yet been made aware. Over in the upper-right of the TestFlightApp.com web page find the grey "Actions" pop-up menu. Choose the menu item "Export iOS devices". Your web browser then downloads a plain text file of the identifiers of the devices owned by those checkmarked testers.

At this point, I need to let Apple know this device(s) is going to be added to my list of authorized testing devices. Remember that Apple limits that list to 100, revised per annum.

1. I log into http://developer.apple.com.
2. Click the "Member Center" area of Apple's Developer site.
3. In the "Developer Program Resources" group box, click the link labeled "Certificates, Identifiers & Profiles – Manage your certificates, App IDs, devices, and provisioning profiles".
   The next page appears with a group box "Certificates, Identifiers & Profiles".
4. Click "Devices" link. In the side-bar, you should have "Devices" > "All" selected.
5. I use the web browser's text search feature ("Edit" > "Find" in Safari) to see if the desired device identifier is in the list or not.
6. Click the "+" button in upper-right to add the identifier. Copy-paste values from the email (or the plain text file as mentioned above). TIP: Include a description of the particular device as part of the tester's name to differentiate when they own multiple devices, such as "Jane Doe – iPad Air".

As a shortcut to manually entering each of several user name + device identifiers, you can import a plain text file such as the one you downloaded early in this page. The catch is that the entire file is rejected if it contains even a single device identifier already registered. I often find it easier to do one at a time.

Provisioning Profile

Now it is time to build that fresh "Provisioning Profile". This encrypted security document lists our registered devices identifiers Apple is granting permission to run my app without that app being delivered through the App Store. This Provisioning Profile will eventually be integrated into a future build of my app intended for distribution to testers.

To create a fresh Provisioning Profile, use that same "Certificates, Identifiers & Profiles" group box page at the Apple Developer site. 

1. In the side bar, choose "iOS Apps" > "Provisioning Profiles" > "Distribution". 
2. Select the "Distribution" > "Ad Hoc" radio button.
3. Follow the Wizard-like steps.
4. On the step for "Select devices.", click the checkbox labeled "Select All" and continue.
5. On the step for "Name this profile and generate.", enter a name similar to "Ad Hoc test distrib 2014-11-01".
6. On that same step, click the "Generate" button.
7. On a following step, click the "Download" button to download the newly generated Provisioning Profile to your local Mac.
   That file will be named something like "Ad_Hoc_test_distrib_20141101.mobileprovision".
8. Locate that local file on your Mac.
9. Open your app’s project in Xcode.
10. Double-click on the .mobileprovision file causing it to be opened by Xcode.

Now I rebuild the project to incorporate that fresh Provisioning Profile. I am using Xcode 5 (not 6).

1. Hold down the OPTION key while choosing the menu item "Product" > "Clean Build Folder" and confirm.
2. Set the "Active Scheme" pop-up in far upper-left corner to "iOS Device".
3. Choose menu item "Product" > "Archive".
   The Organizer window appears.
4. In the Organizer, click "Distribute" button.
5. In Wizard-like window, choose radio button for "Save for Enterprise or Ad Hoc Distribution" (Ad Hoc is what I'm doing).
6. IMPORTANT – In next step of the Wizard, "Choose a profile to sign with", change the "Provisioning Profile" pop-up menu to your fresh one rather the default old one you used previously. This pop-up is why we named the .mobileprovision file with a date and the words "Ad Hoc", to make selection easier here.
7. Click "Export" button.
   Wait a couple minutes for a Save As dialog to appear suggesting a ".ipa" file name extension.
8. In that Export save dialog, I create a new folder named with the date and purpose. Save into that staging folder.

Done with the build process. Now it is time to upload to TestFlight for distribution to our users.

Upload To TestFlight

1. Login to TestFlightApp.com.
2. Click the main tab for "Dashboard".
3. Click the first large green button, "Upload a Build".
4. In the page that appears, write your Release Notes, indicating a focus for your testers’ work.
5. Drag-and-drop the .ipa file from your staging folder to the dashed-line box on the same web page, and click green "Upload" button.
6. Wait patiently for your app to upload.

UUID Converter For Vaadin

Here is a class to convert a java.util.UUID object to String for use in Vaadin. Originally written by a team member at Vaadin, I modified their source code to output lowercase hexadecimal characters as required by the UUID spec.

I filed a feature request ticket with Vaadin to bundle such a class with Vaadin.

Source code…

import com.vaadin.data.util.converter.Converter;
import java.util.Locale;
import java.util.UUID;

/**
 * Modified by Basil Bourque to ensure output of hex string is in lowercase as required by the UUID spec. 2014-08.
 *
 * @author petter@vaadin.com
 */
public class UUIDToStringConverter implements Converter<String , UUID>
{
    private static final String NULL_STRING = "(none)";

    @Override
    public UUID convertToModel ( String value , Class<? extends UUID> targetType , Locale locale ) throws ConversionException
    {
        try {
            return value == null || value.isEmpty() || value.equals( NULL_STRING ) ? null : UUID.fromString( value );
        } catch ( IllegalArgumentException ex ) {
            throw new ConversionException( ex );
        }
    }

    @Override
    public String convertToPresentation ( UUID value , Class<? extends String> targetType , Locale locale ) throws ConversionException
    {
        return value == null ? NULL_STRING : value.toString().toLowerCase(); // The UUID spec *requires* hex-string output to be lowercase. Must tolerate uppercase for input.
    }

    @Override
    public Class<UUID> getModelType ()
    {
        return UUID.class;
    }

    @Override
    public Class<String> getPresentationType ()
    {
        return String.class;
    }
}

xx

Installing Postgres 9.4

Here's a reminder checklist of the steps I take when installing successive beta versions  (1, 2, and 3, so far) of Postgres 9.4 on my Macs.

Installer

The Postgres support company EnterpriseDB graciously supplies installers for Mac OS X as a courtesy to the community. You may reach their site via the Download page of the usual Postgres site. For beta versions look for the paragraph labeled "Beta/RC Releases and development snapshots (unstable)", and find the link to click. On the next page, look for the link near the text "offsite link". Currently takes you to the Early Experience page of EnterpriseDB. Click a link to download a DMG file named something like "postgresql-9.4.0-beta3-osx.dmg".

Users

The Postgres superuser "postgres" is already created by the installer.

Next we need to create a not-quite-so-super user. This is done in "Login Roles" in pgAdmin, not the "Group Roles" list. Choose a name and password for this user, and write it down. The create this user in pgAdmin using a dialog or run the following SQL. After creation, refresh pgAdmin, and context-click the user to choose "Properties" where you can define a password  on the "Definition" tab.

CREATE ROLE your_admin_user_name_goes_here LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;

Next, create a group role to be used by your application. Let's imagine your app is named "Example" in general and "example_" in Postgres. We move attention from "Login Roles" to the "Group Roles" list in pgAdmin. Again, do this in the pgAdmin wizard-like dialog or use this SQL.

CREATE ROLE example_app_role_
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

As done above for the admin user, context-click the user to choose "Properties" where you can define a password  on the "Definition" tab. And, of course, you are writing down these passwords.

Now create a user to be assigned to that role. Again, use either dialog or this SQL:

CREATE ROLE example_app_ LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT example_app_role_ TO example_app_;
COMMENT ON ROLE example_app_ IS 'For connections from our Example app.';


Restore

As I am installing a succession of beta versions, I already have a backup of my desired database. I used pgAdmin's "Backup" feature to create a .backup file. That chore is described in my previous blog entry.

That backup contains just about every aspect of my database, except one: Users & Passwords. That is why we defined our admin user, app role, and app user in those steps above. Those users and roles must be in place for the restore feature to work. The restore references ownership of various objects by those users/roles.

So now I want to restore that database to my new Postgres. No go. The restore process cannot create the database. You must create the database manually, such as in pgAdmin. Use the same name, but need take no further steps.

Context-click on the new database to choose the Restore feature. In the dialog, choose the character encoding to match the original. Not sure of this is required, but probably. Use the button to choose the .backup file to be imported. Finally click the main button to execute the restore. The restoration may take a while. Eventually look to the "Messages" tab of the window to show the progress and completion. Look at the bottom of that report to see if any errors or issues arose.

Permissions

Now we have users defined and we have a database restored. Now we have combine them. The app role we defined must be given permission to work with that database. Execute SQL such as the following, after you have done your homework to study such permissions.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO example_app_role_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO example_app_role_;

Done

That's it. Your database should be back in order. Tables and data should be there. Type "table my_table_name_;" in the SQL window to see all rows, as a quick test. Any functions, domains, etc. you defined should be intact.

Alternative: pg_restore

Instead of this backup-destroy-restore process, you can go another route. Postgres has a nifty pg_restore feature for doing major upgrades in place. I've no experience with this yet.

Uninstalling Postgres 9.4

My reminder checklist for uninstalling Postgres 9.4 beta versions. These instructions assume the use of the Postgres installers provided to the community as a courtesy by EnterpriseDB. For more info, read this answer in StackOverflow.com.

Backup

First backup. Use pgAdmin app to select the desired database(s). On each database context-click to choose the Backup command. Go with default options, including "Custom" format which is a strange name for the native binary format. Choosing "Plain Text" creates SQL statements which is interesting but verbose and slow. The only setting you set is to click the button to choose a folder and specify a desired name for the backup file. Include the ".backup" extension yourself. While most Mac apps are built to add an extension, pgAdmin does not.

Run Uninstaller App

In the root folder (not your user home folder), look in Library folder to find the PosgreSQL folder. So that would be: /Library/PostgreSQL/ path. In there find one or more versions of Postgres. Within a version find the app named uninstall-postgresql. Run that app, supply your system password.

Delete Config File

Delete this file:/etc/postgres-reg.ini.

Delete User

The installer created a Unix user to your Mac named 'postgres'. If eradicating Postgres, you may want to delete that user account. For re-installing a new Beta versions of Postgres, I don't bother. The installer seems to tolerate that extant account.

Delete Apps

Check your usual Applications folder. If the PostgreSQL folder remains there, delete it.

Delete pgAdmin Preferences

If you used the pgAdmin app for administering your databases, its preferences file remains. No big deal. I don't know where it lives. Perhaps the Google would tell you.

No Longer Used

In the old days you would delete the file /etc/sysctl.conf. I do not find that file with Postgres 9.4. I suspect the reason is that 9.4 changed dramatically. Previously a Unix setting was needed on your Mac to enlarge shared buffers. Memory for the database cache is now done differently, so that setting is no longer needed. And therefore that configuration file is no longer needed. I have not confirmed this theory, just a guess on my part.