2011-10-26

Vaadin Changes I Want To See

• Shorten "?restartApplication" URL trick to "?restart"

'restartApplication' is a lot of typing.

• Clear the "?restartApplication" from the URL automatically.

As warned in the Nicolas Fränkel book, after using that URL trick to restart your app you should immediately clear the trick. Otherwise, the app will continually be restarting on every refresh, rendering unrealistic behavior.

• The "getTheme" method on "Application" class should return the default theme rather than null.



2011-10-12

Extensions in Postgres

Upon installing the new Postgres 9.1, I was surprised and stumped when I went to activate the UUID features, as I’ve done for 8.4 and 9.0.

Using the Mac installers provided by EnterpriseDB, I knew the UUID generation and handling features to be bundled but not activated. In the past I’d learned to open this file:
/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sql
and copy-paste to execute the pre-written SQL commands defining Postgres functions. For example, this fragment defines a function to generate a Version 1 UUID:

CREATE OR REPLACE FUNCTION uuid_generate_v1()
RETURNS uuid
AS '$libdir/uuid-ossp', 'uuid_generate_v1'
VOLATILE STRICT LANGUAGE C;


In 9.1, I could not find that "uuid-ossp.sql" file. In fact all but one of the files previously in that "contrib" folder are gone in 9.1. I found some similar files in another folder  named "extension":
/Library/PostgreSQL/9.1/share/postgresql/extension

After some googling, I discovered that “extensions” are a major new feature in 9.1. The idea seems to be making it easier to install and track a bunch of functions or other pieces as one collection rather than separate pieces. Makes sense, as we rarely want to install just one single piece.

This Extension feature is new as of 9.1 as seen on the Feature Matrix, and is discussed in the What's New wiki page. The 9.1 press kit provides a definition:
While PostgreSQL has always been extensible, now users can easily create, load, upgrade, and manage any of dozens of database extensions using the EXTENSION database object. The new site PGXN.org offers a repository for contributing and downloading extensions.
To see what extensions are currently installed, try this SQL command:
select * from pg_extension;
On my newly-installed Postgres 9.1, I see 'plpgsl' and 'adminpack' as the only two installed extensions. See the doc.

To see available extensions bundled with your Postgres and ready to be installed, try either of these SQL commands:
  • select * from pg_available_extensions;
  • select pg_available_extensions();
For example, I see “uuid-ossp” as the 46th item.


To install ("load") an extension, use the SQL command CREATE EXTENSION. While that command may seem like a misnomer, I suppose it makes sense if you think of it as creating an object within Postgres' world that represents the content of, or access to, the items in the extension. Anyways, to install the UUID extension, execute this SQL:

CREATE EXTENSION "uuid-ossp";

I found the quote marks to be required, despite the example in the doc.

To avoid re-installing, add "in not exists".

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Chapter 35 of the Postgres manual explains how extensions are packaged, if you are curious.

An independent repository of extensions can be found at the PostgreSQL Extension Network.

By the way, to be clear about Postgres’ UUID features, let me say:
  • UUID support is built-into Postgres by default, in that Postgres stores UUID values efficiently in their 128-bit form. While your database driver may choose to show UUIDs as hex strings, the UUID values are actually stored in Postgres as their native bits not inefficiently as a string. So UUIDs are not a hack or an add-on in Postgres, and may be searched and indexed like any other supported data type. No need to add a function or extension in order to store and retrieve UUID values in Postgres – you get that for free.
  • If you wish to generate any of the various versions of UUID values server-side within the Postgres, then you may choose to load the OSSP-UUID extension.

2011-10-09

Using UUID Library in Postgres 9.0 and Earlier

UPDATE: This info applies only to Postgres versions earlier than 9.1, such as 8.4 and 9.0. For 9.1, see my later post on "Extensions in Postgres".

If you are using a version of Postgres installed by installers provided by EnterpriseDB, then your installation includes a library for generating UUID values. The library is known as uuid-osspc and is described in this doc. Commands such as "uuid_generate_v1()" return the hex string representation of a UUID’s 128-bit value.

While this library may be included in your Postgres, it is not enabled by default. To enable, you need to execute some SQL commands to create some function definitions within your Postgres database. Fortunately, these SQL commands have already been written for you. You just need to execute a provided SQL script. Find that script in folder such as this one on my Mac:

/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sql

Either execute that script directly, or copy-paste its contents to pgAdmin’s "SQL" (tab) window. If run successfully, pgAdmin displays a message saying "Query returned successfully". And you should see a bunch of functions listed under your database’s definition, such as YourDatabase > Schemas > public > Functions. You may need to refresh pgAdmin's display by context-clicking on your database in the "Server Groups" listing, to choose the menu item "Refresh".

Actually, in my install of Postgres 9.0.4 I encountered an apparent glitch where the list of functions would not refresh until I added a table. This may be a bug in pgAdmin – I’m not sure.

To try the library, run this SQL:

SELECT uuid_generate_v1();