Naming in Postgres

In both the SQL spec and various databases, many words are reserved and should not be used in identifiers (your names you assign to columns, tables, etc.). The Postgres 9 docs show a list of reserved words, a long list indeed.

In my own work, I don't want to be checking my possible new identifier names against that list all the time. Instead, I end all my SQL identifiers with an underscore. Examples:

  • customer_
  • first_name_
  • last_name_
  • city_
  • state_
  • zip_

This convention maintains readability without adding much to length. I've heard of people appending "_tbl " and "_fld" and so on to their identifiers to accomplish the same goal of avoiding conflicts with reserved words. But that is much harder to read, and gets old fast. The trailing underscore solves the problem, and is guaranteed to work. Section 4.1.1 of the Postgres 9 docs mention that the SQL spec will never define a keyword that starts or ends with an underscore.

Identifiers in Postgres 9 can be up to 63 characters long.

Identifiers can contain textual characters, even beyond the usual ASCII English letters, including accented characters. But personally I'd stick to ASCII letters to prevent discovery of bugs. An identifier can contain a digit, but must start with an alphabetic letter or underscore. The only allowed punctuation is underscore: No spaces, periods, quotes, etc. A dollar sign ($) is allowed by Postgres but not the SQL spec, so avoid it.

Identifiers in Postgres are case-insensitive. But for compatibility with other databases, use only lower-case letters.

You can create identifiers that contain spaces, ampersands, and other oddball characters by enclosing the identifier in quotes. But do not do this. Using quoted identifiers will cause nothing but pain over the long haul. For instance, give up the idea of having the column name be presentable as-is to a user. An app should always be responsible for taking the column name and then looking up a presentable name for display to users.


Deploying Web Apps in Apache Tomcat via the "jFastCGI" Java Servlet

    When deploying web apps built in Web Edition of REAL Studio, you can either build a basic web server into your app, or you can place you app "behind" a separate web server using the FastCGI protocol as a conduit between them. FastCGI is just a protocol, some rules of the road, with many implementations in various web servers.

[A] Apache Tomcat
These brief steps here for Tomcat installation are described more fully in my blog post:

(1) Download version 7.  
That first "Core" zip will do.

(2) Unzip.

(3) On Mac OS X (and maybe Linux) fix file permissions.
I'm not sure about v7, but on previous Tomcats I always ran into permissions problems.
On a Mac I drag and drop the unzipped folder to free "BatChmod" app.
I turn on all "Options" checkboxes except "Clear xattrs". Click Apply button.

(4) Move the unzipped folder to the top of your home folder. 
I'm paranoid about problems with very long pathnames due to too many nested folders.

(5) Launch Tomcat.
(5a) In the Apache Tomcat folder, find the "bin" folder. 
(5b) Launch the Terminal program. (Command Prompt window in Windows)
(5c) Drag the "startup.sh" file to the Terminal, and press Return. (Use the .bat for Windows)
See several lines confirm startup.

Launch a browser, point it to:
You should see a Tomcat-generated page.

[B] Port-forwarding

For Windows, skip to step 4.

For Mac & Linux, forward port 80 calls to Tomcat's default of 8080.
For more info, read my blog post: 

(1) Open another Terminal window.

(2) Paste this:
sudo ipfw add 100 fwd,8080 tcp from any to any 80 in
and press Return.

(3) Enter your password in Terminal as prompted.

(4) Test by pointing a web browser to:
You should see the Tomcat-generated page.

Tip: Always hit your browser's Refresh/Reload button/command to force a fresh loading of the web page. Some web browsers, especially Safari, display a cached copy of web pages rather than asking for a fresh one. That caching behavior can really confuse your testing!

(5) Shutdown Tomcat.
(5a) Drag the "shutdown.sh" file to the same Terminal window where you started Tomcat.
(5b) Press Return.

[C] jFastCGI Servlet

(1) Download 2.0 of jFastCGI.

(2) Unzip.
You'll get a folder with 2 .jar files and a .pdf manual.

(3) In the Tomcat folder, navigate to "webapps" folder. 
(3a) Delete or move everything inside "webapps".
(3b) Create a folder named "ROOT" inside the "webapps" folder.
(3c) Create a new Welcome page, for testing. For example, save the following HTML5 source code to a file named "index.html" stored in that "ROOT" folder:

<!doctype html>
   <meta charset="UTF-8">
   <p>Per Basil's example.</p>

(4) Install servlet.
(4a) Create a "WEB-INF" folder inside the "ROOT" folder.
(4b) Create a folder named "classes" inside the "WEB-INF" folder, next to that index.html file you just created.
(4c) Move in both .jar files from jFastCGI download: 
• commons-logging-1.1.1.jar
• jFastCGI-2.0.jar
FYI: .jar files are simply zip files plus an optional manifest file, used for storing Java executable and related files.

When done, your files should look like this screenshot.

Ignore the "Joda Time" and the commons-logging.

(5) Deploy Servlet Create an XML file to be used as a "Deployment Descriptor" instructing Tomcat when and how to invoke the jFastCGI servlet.
(5a) Create a new text file. Use UTF-8 character encoding.
(5b) Name the file "web.xml" and store it inside the "WEB-INF" folder used above.
(5c) Paste the following:

<?xml version="1.0" encoding="UTF-8"?> <web-app 




[D] Run.

(1) Launch Tomcat. 
Tip: In Apple's Terminal, you can press the Up arrow key to repeat previous command line. Press again for previous before that. Press Return to execute.

(2) Build a simple web app in REAL Studio.
(2a) Build as "Static FastCGI" using port 9000. This assumes you have no other apps listening on port 9000.
(2b) My app: Create a TextField named "nowField" with an adjacent button whose Action event handler is:
 dim now as new Date
 nowField.Text = now.SQLDateTime

(3) Launch web app
(3a) In a new Terminal window, type out the entire path to your built app, and press Return. For example:
Tip: I moved my built app "real_now" to my Desktop folder for convenience. 
(3b) You can verify the web app is running by finding its name in the "Activity Monitor" program on your Mac, or equivalent in other platforms. You may need to select "All Processes" from the popup menu that filters the list.

Later, to stop this process, press Control+C in the same Terminal window. There must be a more graceful way to shutdown a web app, but I'll look into that later.

(4) Test Tomcat by pointing your web browser to:
Hopefully you will see your web app!

Next challenge is to wrap my head around the URL mapping. Currently I am using "/*" in the tag above. The asterisk is a wild card. So that pattern means any and every url goes to my REAL Studio web app. That is not practical. Only some URLs should go to the web app, while others will go to static web files, Java Servlets, etc. I tried using other URL patterns, but could not get it to work correctly.

Lastly, for most real-world deployment, we would want to add SSL/TLS encryption to the web browser interaction. Tomcat certainly supports that. But that chore will wait for another day.

Web Edition of REAL Studio launched

REAL Studio 2010 Release 5 arrived this week, and now includes "Web Edition".

This tool is a huge breakthrough, allowing you to create desktop-style apps deployed in a web browser. You build web apps in the same manner as desktop apps -- drag and drop widgets to forms, add programming to the widgets, and write classes as well. All in the same language: REALbasic. Same code editor and debugger, same commands and libraries. Except of course the web app widgets are a little different and more limited given the limitations of browser technologies and given that Web Edition is a new 1.0 feature set though built on top of an established toolset.

The wonderful thing about Web Edition is what is not in it:

Well, actually, Web Edition has all those, but you the programmer don't see them. As a programmer you need know nothing about web technologies. Keep your brain in desktop-app mode, but deploy to a browser. Amazing.


Logging Facility Built Into REAL Studio

REAL Studio 2010 has its own logging facility. Call the "System.Log" command, passing 2 arguments:

  • Integer, the level of logging.
  • String, the message to be logged.
You can find 9 constants for that number in System.LogLevelXXX:
  • LogLevelEmergency = 1000
  • LogLevelAlert = 1001
  • LogLevelCritical = 1002
  • LogLevelError = 1003
  • LogLevelWarning = 1004
  • LogLevelNotice = 1005
  • LogLevelInformation = 1006
  • LogLevelDebug = 1007
  • LogLevelSuccess = 1008
Note that this is different than the 6 log levels used by the Apache Commons Logging project. You might think that after a few decades the computing industry would standardize on this stuff, but no.

Example usage:
  System.Log( System.LogLevelInformation, "Your Message Goes Here" )

Messages you pass appear in the host OS' logging tools. On Mac OS X Snow Leopard 10.6:
  1. Run the "Console" app.
  2. Click the "Show Log List" toolbar button.
  3. Select the first item beneath "Files": "system.log".
    Your messages should appear there.
One BIG catch with this facility: Not all the log level constants work on Mac OS X. As documented, and as tested by me in REAL Studio 2010 Release 5, the 3 most benign log levels fail on a Mac:
  • LogLevelInformation = 1006
  • LogLevelDebug = 1007
  • LogLevelSuccess = 1008
So, only use the following on a Mac.
  • LogLevelEmergency = 1000
  • LogLevelAlert = 1001
  • LogLevelCritical = 1002
  • LogLevelError = 1003
  • LogLevelWarning = 1004
  • LogLevelNotice = 1005
This is an annoying issue. I recommend writing your own wrapper based on the Apache Commons Logging project by using an Interface to make logging calls throughout your app. Use the "LogLevelNotice" to report all the benign levels.


Logging, It's a Good Thing

If Martha Stewart did computer programming, she'd say "Logging, it's a good thing.". Unfortunately, many programmers including me do not make a habit of it.

There are different reasons to do logging:

  • Help figure out what is going on during programming or debugging.
  • Track normal operations of the app.
  • Report trouble or errors.
Yohan Liyanage wrote a nice blog post about how to log. In particular he includes a table with clear descriptions of each of the six levels of logging as specified by the Apache Commons Logging project:
  • INFO
  • WARN
Basically, the first two are for programming & debugging. The middle one, INFO, is for tracking normal operations. The last three are for recording problems.

I ported that "Log" interface to REALbasic, and offer it to everybody. By using an interface, you can switch logging implementations, at design time or run time, without changing the countless logging calls littered throughout your app's code. Also, if everyone in your team, or even the entire REALbasic community, used the very same interface it would be easier to share code containing logging calls.

Robert Elliot wrote a another interesting logging blog post

Aaron Ballman in his "Ramblings" book explains how useful logging is for debugging a GUI app. When running your app from the REAL Studio app, using the debugger can affect the state of your app's GUI. This is one instance of the Observer Effect. This happens to be why he invented the Remote Debugger Stub feature of REAL Studio 2010. The common misconception is that the purpose of Remote Debugger Stub is to test across platforms. But actually it came from his own frustration with this observer effect. By using the Stub, even on the some platform, you can debug without affecting the state of your app.


"Shared Memory" settings for Postgres on Mac OS X

Shared Memory Properties
Property Description Reasonable Value Snow Leopard Default
xxx yyy zzz nnn
xxx yyy zzz nnn
xxx yyy zzz nnn

The best I can figure out: Apparently "shared memory" is a way for separate processes to share information. Since Postgres launches several processes for itself, and then one more process for each connected user, the processes must have access to "shared memory" in common. Setting this amount is critical to successful use of Postgres. The bulk of the database operations take place in that memory. So the bigger the database, the more memory you need allocated to "shared memory".

This is not normal among Mac apps. Most Mac apps ask for more memory than they need. Mac OS X responds to the app by lying, saying "Yes, you can have that much memory". But in fact the app gets a mere fraction of that. When the app starts using that memory, and runs low, the virtual memory system allocates more memory as actually needed. A few apps, such as 4D or Photoshop, grab big chunks of real memory to be managed internally by that app rather than be managed by the Mac OS X virtual memory system.

Rather than go either of these routes, Postgres and other SQL database engines (DB2, Sybase, etc.) use "shared memory". The catch is that by default Mac OS X may have set low limits to the amount of shared memory allowed.

So how do we view the current shared memory settings and set them in Mac OS X? The answer: In the command line, using a tool called sysctl.

Check your settings by typing into your Mac's Terminal program:
sysctl -a
We can use a shorter list, as we don't care about most of those settings. Typing a partial name, up to any of the periods, acts as a filter. Type:
sysctl kern.sysv.
By the way, "kern" is usually short for "kernel" meaning the heart of the operating system.

Postgres also cares about:
  • kern.maxprocperuid
  • kern.maxproc
The defaults for the settings we care about on a fresh Snow Leopard Mac OS X 10.6.5 installation are as follows. Each of these are described in the Postgres doc, with some of that info pasted here.

  • kern.sysv.shmmax
    The most important shared memory parameter. The maximum size, in bytes, of a shared memory segment. Desirable settings are in the hundreds of megabytes to a few gigabytes. Must be a multiple of 4096 or else your setting will be ignored.
    Some possible values:
    ‣ 67,108,864 = 67 megs
    ‣ 134,217,728 = 134 megs
    ‣ 536,870,912= Half a gig
    ‣ 805,306,368 = Upwards of a gig
    ‣ 1,073,741,824 = 1 gig
    ‣ 1,610,612,736 = 1 1/2 gigs
    ‣ 2,147,483,648 = 2 gigs
    ‣ 4,294,967,296 = 4 gigs
    ‣ 6,442,450,944 = 6 gigs
    Reasonable value: Depends on your database. From several megs to several gigs.
    Snow Leopard default: 4194304
  • kern.sysv.shmmin:
    Minimum size of shared memory segment (bytes).
    Reasonable value: 1
    Snow Leopard default: 1
  • kern.sysv.shmseg
    Maximum number of shared memory segments per process.
    Reasonable value: Need only 1 segment. But setting may be higher.
    Snow Leopard default: 8
  • kern.sysv.shmmni
    Maximum number of shared memory segments system-wide.
    Reasonable value: Like SHMSEG plus room for other applications
    Snow Leopard default: 32`
  • kern.sysv.shmall
    Limit on the total amount of shared memory in the system. Measured in 4 kB pages.
    Reasonable value: ceil(shmmax/PAGE_SIZE).
    Snow Leopard default: 1024
  • kern.sysv.semmni: 87381
    Maximum number of semaphore identifiers (i.e., sets).
    Reasonable value: At least ceil((max_connections + autovacuum_max_workers) / 16)
    Snow Leopard default: xxx
  • kern.sysv.semmns:
    Snow Leopard default: 87381
  • kern.sysv.semmnu:
    Snow Leopard default: 87381
  • kern.sysv.semmsl: 87381
    Snow Leopard default: 87381
  • kern.sysv.semume: 10
    Snow Leopard default: 10
  • kern.maxprocperuid: 266 //
  • kern.maxproc: 532 //
When setting these by way of a sysctl.conf file, you must include all 5 of the following settings, or else your settings will be ignored.

  • kern.sysv.shmmax
  • kern.sysv.shmmin
  • kern.sysv.shmmni
  • kern.sysv.shmseg
  • kern.sysv.shmall
The Postgres doc suggests the following for Mac OS X. Notice that these suggestions happen to be precisely the default values in Snow Leopard (as seen above), so you need not bother at all. While these defaults let you install and run Postgres, you must raise shmmax and shmall when your databases grow.
  • kern.sysv.shmmax=4194304
  • kern.sysv.shmmin=1
  • kern.sysv.shmmni=32
  • kern.sysv.shmseg=8
  • kern.sysv.shmall=1024
The ReadMe that accompanies the Postgres 9 one-click installer suggests the following for a 2-gig MacBook. These settings seem way too large to me for a computer with only 2 gigs of memory.
  • kern.sysv.shmmax=1610612736
  • kern.sysv.shmall=393216
  • kern.sysv.shmmin=1
  • kern.sysv.shmmni=32
  • kern.sysv.shmseg=8
  • kern.maxprocperuid=512
  • kern.maxproc=2048
And the ReadMe notes:
  • kern.sysv.shmmax must also be a multiple of 4096.
  • (kern.sysv.shmall * 4096) should be greater than or equal to kern.sysv.shmmax. 
Bruce Momjian, one of the core contributors, has a wealth of information and advice on such matters, especially his Administration class slides.

The upshot seems to be that you should check for these things:
  •  kern.sysv.shmmax >= several million [several megabytes]
  • (kern.sysv.shmall * 4096) >= kern.sysv.shmmax
  • (kern.sysv.shmmax / 4096) = 0   [even multiple of 4096]
If you do decide to edit your syscntl.conf file, be sure to make a backup copy first. You'll find that file in the hidden folder /etc. Finding and editing such hidden files is easily done using the excellent Path Finder tool, a Swiss-army knife for Mac programmers and geeks.

My Mac's settings seemed to meet their requirements, so I made no changes. Perhaps earlier versions of Mac OS X required such changes. This was the only speed-bump I encountered, and it turned out to be a non-issue in my case.


Postgres on My Mac

Some quick facts & tips for new Postgres users, especially on the Mac…

Postgres installs a folder in your /Applications folder. Those are gui front-ends to the underlying command line tools. You regularly use two of those: 
  • "pgAdmin" app
    You create and manage databases here, including defining tables, columns, users & roles. You can also type in SQL statements interactively.
  • "PostgreSQL documentation" app.
    Actually this is just leads to the docs stored locally in HTML format, and opens them in a web browser.
GUI Apps

The underlying Postgres is installed in /Library/PostgreSQL/ folder. Note that this is not the "Library" folder in your own user's home folder. This is the root "Library" folder.
Underlying Postgres Installation
The funky thing about Postgres is that it creates a new Unix user on your computer, named 'postgres'. The installer prompts you for your system password to gain admin rights to create this new user and a password. It also creates a default database by the same name. Apparently there is an old tradition in databases to assume that every system user will want to automatically login to their own private database. Seems silly to me, but remember that in those old days IBM considered SQL to be an end-user self-service tool. 

Anyways, note that the 'data' folder has a red stop sign on its icon. This means you cannot open that folder. The idea is extra security; if the regular user account is compromised, the bad guys will not have direct access to the database and its files. So choose a good long strange password for that 'postgres' user account, and write it down somewhere safe. The tools such as 'pgAdmin' app require that password to gain access as the 'postgres' user to perform actions such as creating a new database. 

You can begin learning Postgres using that 'postgres' user. But soon you should create at least one new Postgres user & password, to access each database without full admin rights, for regular use such as when developing end-user apps that connect but should only be allowed to add and drop records as opposed to deleting all databases. The 'postgres' user can wipe out all databases and do other dangerous acts not allowed 

How do your backup your database if you cannot reach its files in the Finder? Use the "pgDump" tool to pour out the data and scheme definitions as plain SQL text. Postgres and its MVCC implementation are built to do a pgDump run without shutting down the server. (Amazing to me!)

If you want to run the command line tools directly in a Terminal window, switch to that folder. Type the 'cd' (Change Directory) command, followed by a space, and then drag and drop the 'bin' folder from the Postgres folder. Press Return. To see verify, run this in the Terminal run the command to list all files: 
ls -a

To verify the version of Postgres currently running, run this in the Terminal:
./pg_ctl --version
Or in the SQL pane of pgAdmin, execute this SQL:
SELECT version();

My first Postgres Function (PL/pgSQL)

After reading this O'Reilly article and the Postgres doc, I made my first function to run inside the Postgres server. This code is serves no real purpose, but is simple and harmless, so it makes a good example and first experiment.


    quantity integer := 30;
    quantity := 42;
    RETURN quantity;
$$ LANGUAGE plpgsql;

You can try it. Copy that text, paste it into the ‘Query Tool’ of pgAdmin, and execute it. If it works, you'll get a response like this:
Query returned successfully with no result in 230 ms.

To call the function, execute this line:
SELECT some_number();

You should get a result set of one row and one column, with the number "42".


Notice that the example above is a single line of SQL. From the SQL interpreter's perspective there is a single line, with 4 key words ("CREATE…", "RETURNS", "AS", "LANGUAGE") and a semicolon at the end. The DECLARE-BEGIN-END, our function's source code, is the content of a single value delimited by the double dollar signs "$$".

You can call "CREATE FUNCTION" or "CREATE OR REPLACE FUNCTION". The first is safer, so you don't unintentionally replace a pre-existing funtion. After re-writing your code, use the second command to replace existing code on you Postgres server.

Be careful in naming your function, to avoid using reserved labels.

The beginning and end of the function is defined by the "$$". The beginning before the dollar signs declares the signature of the function (name, arguments, and return type). The ending after the question declares the language used to write the function. Together, that beginning and ending make up a single statement as perceived by the SQL interpreter, with a semicolon at the end. The middle part, the source code of the function, is delimited by the "$$" to prevent execution by the SQL interpreter. Instead of execution, the middle block is handed over to the PL/pgSQL module.

There's a lengthy tutorial to read.

This function is written in Postgres' own scripting language, PL/pgSQL. You can write server-side code in several other languages such as Java, Ruby, Python, Perl, and more.


Roll your own "datetime" command line tool in Mac OS X

Sometimes you need the current date and time as a string. Perhaps to copy paste, or label something.

Mac OS X does have a command line tool to generate such a string, called: date

Unfortunately, the date string returned by default is the old-fashioned style:

Tue Oct 26 17:25:19 PDT 2010

All the cool kids nowadays are using ISO 8601, in a style such as YYYY-MM-DD HH:MM:SS
You can temporarily get your Terminal window to do this by pasting: alias datetime='date "+%Y-%m-%d %H:%M:%S"' and then typing: datetime

When you close that Terminal window, your Mac will forget all about this alias. To make it remember, create a text file named exactly, saved to your home folder: .bash_profile

'bash' is the name of the Unix shell used by default in later versions of Mac OS X. The period in front makes this file disappear, that is, the Finder will ignore it. There are other places to save this "alias" command, other files and folders, both traditional Unix/bash ways and Mac-specific ways. This way works for me.

The Terminal is built to look for and open a file with that exact name every time you open a Terminal window. Windows already open will not be updated, so try your new "datetime" command in a new window. You should see something like this:
2010-10-26 17:34:01

You can vary that alias, or create others, if you like. 
  • If you want to use this datetime as part of the name for a file in the Mac's HFS+ file system, then you must avoid the colons.
    alias datetime_mac='date "+%Y-%m-%d %H-%M-%S"'

  • A strict ISO 8601 datetime should have a "T" rather than a space in the middle, and represents the time zone as an offset from UTC time.
    alias datetime_iso='date "+%Y-%m-%dT%H:%M:%S%z"'

  • You may want to use UTC time (formerly known as GMT or Greenwich Mean Time), where 'Z' means Zulu time.
    alias datetime_utc='date -u "+%Y-%m-%d %H-%M-%SZ"'

  • Perhaps UTC time in ISO format:
    alias datetime_utc_iso='date -u "+%Y-%m-%dT%H:%M:%SZ"'
For your convenience, here is all 5 ready for cut-and-paste:

alias datetime='date "+%Y-%m-%d %H:%M:%S"'
alias datetime_mac='date "+%Y-%m-%d %H-%M-%S"'
alias datetime_iso='date "+%Y-%m-%dT%H:%M:%S%z"'
alias datetime_utc='date -u "+%Y-%m-%d %H:%M:%SZ"'
alias datetime_utc_iso='date -u "+%Y-%m-%dT%H:%M:%SZ"'


$ datetime
2010-10-26 18:11:59

$ datetime_mac
2010-10-26 18-12-03

$ datetime_iso


$ datetime_utc
2010-10-27 01:12:13Z

$ datetime_utc_iso


Get DateTime in one line, in REALbasic

In REALbasic syntax, it is not legal to call a method on an instance created with "New". So you have to capture the new object on one line of code, and then on successive lines use that object. This is particularly inconvenient when briefly needing the current datetime instance or string. Grabbing the datetime is common when logging, debugging, or recording the moment in database records.

For example, using getting a datetime as a string requires two lines of code:

dim now as new Date
MsgBox( "At the tone the time will be: " + now.SQLDateTime )

For convenience, to collapse those multiple lines of code, add these two little methods to your App class, or some other utility class.

Function now() As Date
  dim thisMoment as new Date
  return thisMoment
End Function


Function nowSQLDateTime() As String
  dim thisMoment as new Date
  return thisMoment.SQLDateTime
End Function

Now you can make get the datetime mid-line:

MsgBox( "At the tone the time will be: " + app.nowSQLDateTime )


Postgres Companies and Resources

Just a list of links related to Postgres. I'm not necessarily recommending any.

Where it all starts

Support, Training, Consulting



SQL Prepared Statement comes to REALbasic

In REAL Studio 2010 Release 4, the REALbasic language gains new interfaces and classes to support SQL Prepared Statement. This feature is called Database Parameter Binding.

A prepared statement is a SQL thing, where the query is pre-processed on the database server before it is actually used. The parsing of the SQL statement's text and verifying the table/column names is done ahead of time. The query planner in the database server figures out how to solve the query without actually executing one. Then you can execute the same query many times, passing arguments. Each successive use of that query saves you the overhead of parsing, processing, and planning by the database server. Not much difference for a low use database, but this is an easy way to greatly improve performance on a busy server. 

Another advantage is simplification of your programming code. It is easier to read calls to a prepared statement where you are passing the arguments for various column values rather than reading the entire text of a full SQL statement.

Besides performance on the server, and convenience in your code, there is another big advantage of the prepared statement: Security. This approach fights against “SQL Injection” attacks, where the bad guys put partial SQL statements in the fields of your form. With a regular SQL call, you’ll be unwittingly altering your SQL statement on-the-fly at runtime when those field values are interpreted as SQL commands. With a prepared statement, the SQL statement is already built and the query planner has already done its work before the form's data is incorporated. So any partial SQL in the form's fields will be treated as plain text rather than interpreted as SQL.

For more info on SQL Injection:

Here's an example of the prepared statement in action in REAL Studio.

dim db as new PostgreSQLDatabase
// Set db.xxx properties. IP address, db name, user name, user password, etc.

dim stmt as PostgreSQLPreparedStatement = PostgreSQLPreparedStatement(db.Prepare("SELECT * FROM cats WHERE name = $1"))

// 1 query, used 3 times.
// "Sebastian" is fed into the "$1" in line above.
Dim rsGoodKitty As RecordSet = stmt.SQLSelect("Sebastian")

Dim rsBadKitty As RecordSet = stmt.SQLSelect("George")
Dim rsCartoonKitty As RecordSet = stmt.SQLSelect("Sylvester")

The "$1" syntax above is specific to Postgres. Other databases may use a different symbol, typically "?" question mark. Furthermore, other database may require additional syntax to specify the data types of passed arguments.

The prepared statement is new to REAL Studio, but not to SQL or other programming languages such as Java. So don't be afraid to start using it if you upgrade to this new release. In at least Postgres, a prepared statement can be used on the SELECT, INSERT, UPDATE, DELETE, or VALUES commands.

Preliminary doc is here. The online wiki-based docs are not yet updated.

And some Postgres docs about prepared statements and its PREPARE command.


pgWest 2010 conference topics for beginners

I'm new to Postgres by way of programming in REAL Studio and its bundled database driver. Postgres keeps looking better and better. So my interest was piqued when I heard about the annual conferences where both the creators and users of Postgres congregate, alternating east and west coasts of the US.

Nov 2-4, 2010 is the west coast conference, called ‘pgWest’. This time around it’s located in downtown San Francisco. Even as a newbie, I found a useful topic in all the time slots. Here's a list of my picks among the busy agenda:

There is also a mention but no information about a full day training session on Nov 5 by Bruce Momjian.


Standard delimiters

After decades of programming experience, I only now learn that the ASCII spec defined delimiters. As an extension of ASCII, Unicode adopted these too.

  • Character 31 is for separating fields of data.
    – ASCII "unit separator" (US for short)
  • Character 30 is for separating records of data.
    – ASCII "record separator" (RS)
If you need higher levels of hierarchy or grouping:
  • Character 29 is for separating groups of records.
    – ASCII "group separator" (GS)
  • Character 28 is for separating groups of groups, that is, a document or file.
    – ASCII "file separator" (FS)
This blog and this Wikipedia page explain. Correctly using these delimiters is properly called ASCII Delimited Text.

These delimiters were defined way back in the 1960s! So why on earth has common practice been the perversion of Tab, Linefeed, Carriage Return, Comma, etc. for delimiting data? Perhaps because text editors are built as stripped-down word-processors for visualizing text on screen, yet are often used for viewing and editing data streams. Tab, Linefeed, Carriage Return, Comma, etc. characters where meant to control presentation of text, but delimiters are meant to identify chunks of data in a stream. Since Tab, Linefeed, Carriage Return, Comma, etc. can very well be present inside a valid text stream, it is silly to use these as delimiters.

There are two distinct purposes being served by these code points:
  • Text white-space
    Presentation of text characters on screen or on paper for display to humans, controlling placement of text in column position and lines, spreadsheet cells, and so on.
  • Information separators
    Identify meaningful chunks of information inside a data stream, and show a hierarchical relationship of those chunks.

Mixing up the two (text white-space vs. information separators) is like serving the plastic green frilly stuff found between butchers' trays inside your hamburger.

As for white-space, carriage return, linefeed, tab, form feed, and such were originally intended to control presentation of data in a printer. Unfortunately, their purpose has been bent into indicating end-of-line and end-of-paragraph. Unicode had the bright idea of defining two new characters just for those purposes, to eliminate the ambiguities. Unfortunately, I've never seen them used.

• U+2028  (decimal 8,232) = SEPARATOR, LINE
• U+2029  (decimal 8,233) = SEPARATOR, PARAGRAPH


Postgres User Groups

I'm liking Postgres more and more. If you are interested in learning more about Postgres, you may find a user group meeting in your area.

In the Seattle and Bellevue area, there is SeaPug. If I could  draw, I'd make a logo: a Pug dog drinking Starbucks.

To find others groups, check this page.


Colo and hosting

If you want to pay someone to host your server on the internet, there are 3 ways to go.

  • "Colo"
    Short for Co-located, where you own a computer, send it to a service company, and they install it in their racks. You own the computer box, they plug it into fast internet connections, uninterruptible power supplies with generator backup, and possibly behind firewall hardware. Some will sell you a computer from their inventory as well, new and/or used.
  • Rent-a-server
    Same as above but you rent their computer rather than provide your own. With today's low costs for computing hardware, this may not make financial sense.
  • Virtual Server
    You pay rent for a certain speed and number of processor cores, certain amount of disk space, and bandwidth and/or traffic. But it's all imaginary. You don't actually control a specific computer box, processor, or hard drive. Your virtual server is one of many others operating across a server farm, large arrays of computers. Some services let you shut down your virtual server but store your disk space while reducing the bulk of your cost. This is handy for test machines, or trial runs before launching your full-time servers.
In the first two categories, my professional contacts, previous research, and word-of-mouth leads me suggest the following companies to someone looking for a vendor:
Do your own research and shopping, but those vendors may be a good place to start.


Zipping beyond the Mac

I distribute my cross-platfrom app to the world as simply a zipped file. That seems to be working well, except for two problems:

  • Older versions of Windows, even Windows XP, may not ship with a Zip tool. 
  • When creating the zip on my Mac using the Finder command File > Compress*, extra files or folders are included for Mac-savvy features. 
The first problem, I have no answer for. Except to say I've gotten no complaints from the public yet. So perhaps the problem is not widespread today.

The second problem is a problem only for the non-Mac crowd. On a Mac, the extra file/folders are invisible. But on Linux and Windows computers, the users see this extra crud and are likely to get confused. I have found 2 easy fixes.
  • Path Finder
    This excellent app from CocoaTech is a Swiss Knife full of diverse tools, handy for a range of geeks and programmers. It costs about forty bucks, and so worth it. Relevant here is the ability to create zips. By default it creates zips in the same Mac-savvy way. To strip that extra stuff in future zips, in Path Finder preferences, turn ON the two "Ignore" checkboxes.
     Ignore invisible file when compressing
    Ignore resource forks when compressing

  • YemuZip
    This free app from Yellow Mug does one simple thing: Create zips. Drag-and drop easy. With the option of stripping away the Mac-specific stuff. Perfect. Even includes a feature to check for updates.

* Older versions of Mac OS X: File > Make Archive


Running Apache Tomcat on your Mac


Apache Tomcat is Apache's other web server. Also runs Java Servlets and JSPs. I prefer running Tomcat as a web server much better that Apache's more famous (and finicky) "httpd".

Tomcat is pure Java, so it runs very well on your Mac OS X computer. Apple builds Java into every Mac.

  • The current version 6 runs on many older generations of Macs as well as new ones. 
  • The still-beta version 7 will run only on Java 6, which requires an Intel Mac running Snow Leopard.
There is one big catch to running Tomcat on your Mac: By default, the permissions are set incorrectly on the downloaded files. When you try launching Tomcat you will get errors, complaining that you do not have permission to complete certain operations. The fix is so easy: just a drag-and-drop and a few mouse clicks. Here's how.

Download the free app (donations accepted) BatChmod. It has the best icon, by the way. This app is a very convenient gui wrapper around an otherwise very difficult arcane set of command-line instructions. You will be entrusting your system password to them, and do so at your own risk. They've been in business for years with no issues I've heard of. Tip: To detect any software phoning home without your permission, get the Mac app Little Snitch.

Download Tomcat. Version 6.0.29 is current nowadays. Grab the zipped one, as the tarred files may not work with Mac OS X' tar tools. You'll end up with a folder named something like "apache-tomcat-6.0.29". If you’re paranoid, hit the MD5 link, read the number, then drag and drop the downloaded zip file to a Terminal window after typing "md5 ", and compare your number to theirs. If no difference, you downloaded without corruption.

You can place the Tomcat folder anywhere you like. I avoid nesting too many folders because extremely long path names can cause problems. I suggest the top level of your home folder.
  1. Drag and drop your unzipped Tomcat folder to the BadChmod app. 
  2. Check all the checkboxes except "Clear xattrs".
  3. Click Apply.
  4. Enter your system password.
  5. Quit.

CAVEAT: I am not expert at these Unix permissions and related issues. Do all this at your own risk, and after your own research. I'm just sharing what works for me, but taking no responsibility for you.

This problem of Unix file permissions often occurs on downloaded files, but the effects are more obvious in Tomcat. I usually run BatChmod on anything I download, unless packaged as a .DMG file. 

Now you're ready to launch Tomcat.
  1. Open a window in Terminal app, found in Applications > Utilities folder.
  2. In the Finder, locate inside the "bin" folder inside the Tomcat folder.
  3. In "bin" folder, locate the file named "startup.sh".
    (Ignore the .bat files; those are for MS Windows)
  4. Drag and drop that "startup.sh" to your Terminal window.
  5. Press Return to run that shell script.
    (".sh" means "shell")
  6. See several lines of text appear in the Terminal window.
    (If you see about 5 lines of "Using " this-or-that, you are in good shape.)
Now test your web server.
  1. Launch a web browser, such as Safari, Camino, Firefox, Opera, Chrome, etc.
  2. In the URL address bar, type:
    Or if that seems to fail, type:
  3. Press Return to make the web browser hit your web site.
    If successful, you should see a web page displayed with the cute tiger Tomcat logo and the Apache feather logo.
To get rid of the need to add the ":8080" see my other blog post on port-forwarding.

To shutdown the web server, in that same Terminal window, drag and drop the "shutdown.sh" file. Press Return to execute, of course.

Tip: Press the Up-Arrow key to re-enter previously used lines in that Terminal window. Press Return to execute one of them. This is a handy way to easily repeatedly start and stop Tomcat.

If you got this far, lastly you replace Tomcat's default web site with your own. Replace the contents of:
Tomcat folder > webapps > ROOT

Note that we did not do any configuration settings in Tomcat whatsoever. I like that about Tomcat: For a single web site, Tomcat is ready to go out-of-the-box. You can do much more configuration if you wish as Tomcat is relatively flexible and full-featured as web servers go. You can change the port number. You can turn on logging to record who hits your web site and when. You can enable virtual hosting to serve multiple web sites. You can set up security restrictions controlling access to portions of your web site.

In passing, I'll mention another popular Java-based alternative to Tomcat: Jetty. If you want a bunch of additional functionality, beyond just web serving and Java servlets, for a big application server consider Glassfish. But for me and most folks I know, Tomcat or Jetty are the way to go.

Hope that works for you. Happy serving!

Use Google to search REAL Studio docs

I really miss the PDF version of docs for REAL Studio 2010. With PDFs, I can use Apple Preview or Adobe Reader to search through an entire manual to find occurrences not listed in the TOC or Index.

The REAL Studio team has chosen to move from PDFs to online wiki-based docs, for the noble reason of keeping the docs always up to date. Unfortunately, the price we paid is awkward search capability. Unless you are searching for an exact class name, the search utility at docs.realsoftware.com leads to frustration.

Then I remembered the wise man who taught me this nifty trick in using Google: Constrain a Google search to a single domain (web site). Simply add the word "site:" (with a colon) and the domain name. Then add on your usual search criteria.

For example, instead of googling this:
Shared Property
Put this into your Google bar and smoke it:
site:docs.realsoftware.com Shared Property


Add a Version Number to Your Classes

When creating classes to be shared with others, add a property to represent the version.

Programmatic access to the version number is good for at least 2 reasons:

  • Review what version is currently installed in an app.
  • Calling code can work around bugs or changes in behavior if it knows the version number.*
At the very least, add a simple string to store something like "2.4".

Make the property a "static" or "class" field with one value shared among all instances. The number will not vary across instances, so there is no need to make it a regular member. In REALbasic, make it a Shared Property.


Tracking a field's value in REALbasic

Oddly enough, REAL Studio 2010 through Release 3 does not offer an event meaning the user (or code) changed the value in a TextField. On every keystroke, both "KeyUp" and "TextChange" fire. But that is too often. In boring database-backed business apps, we usually don't care about the user's edit as it happens. We only care after the edit, when the change is complete.

Hopefully the REAL Software team will add such an event in the future. In the meantime, I am giving away a single file, a subclass of TextField named "TextField_ValueTracking". I have an example project to demonstrate how it works. See here.

Moving files securely between Macs

How do you move files across a network or Internet to a secure Mac server?


As a faithful Apple customer with a MobileMe account, my first thought was to use my iDisk to transfer files. I put files into my "Public" folder, then on the server in the Finder I choose Go > iDisk > Other User's Public Folder. The Finder mounts the public folder as a drive. Works well. Until it doesn't.


Twice now I've been burned in Mac OS X 10.6.4 and .5 where the Finder gets confused about the mounted iDisk folder. One symptom of this Finder bug is refusing to dismount. The Finder complains that a file is in use, but that's not true. The list of files in the mounted iDisk no longer updates its display to show current contents. "Force ejecting" the mounted iDisk fails. Your only option left is to force-quit the Finder. But that hoses your Mac, requiring a restart of your secure server. That's a drag.

I don't expect a fix soon. The Finder has always been lousy with bugs related to iDisk and its WebDAV protocol. With Snow Leopard, the Finder got a re-write in Cocoa from Carbon, which means new hiding places for bugs.


One dead-simple workaround:
Rather than mount the iDisk in the Finder, use a web browser. 

Apple has been greatly improving the web access to MobileMe features. That includes iDisk file access. A nice multi-columned view of the file system appears in your web browser when you access the iDisk icon.

To log in to your MobileMe account with full access:

To access somebody's iDisk public folder, point your web browser to:

By the way, there are some other handy such URLs for accessing a MobileMe account's picture gallery, calendars, and web site.

You may be able to use Safari or other web browsers such as Camino, FirefoxChrome, or Opera. I've some of those successfully, but don't recall which. You may get a warning message, but I've ignored it with no penalty.


How to type an end-of-line

Recently a fellow programmer asked me a couple questions about how to type a linefeed on the keyboard, and how to handle newline delimiters from one program to another.

1. "What keyboard key can I press to create a linefeed character on a Mac?"

Depends on the app. End-of-line delimiters (also known as Newline) are not really input by the keyboard. The app interprets a press of the Return or Enter key, inserting into the data what it believes to be appropriate.

In most text editors such as TextMate, JEditBBEdit, TextWrangler, etc. you specify in prefs what the end of character should be. Some setting like "Lines end in: " with a popup menu offering LineFeed, Carriage Return, both. An app might even offer some special Unicode characters meant explicitly for such use (that nobody uses unfortunately). 

So, the upshot is: The user presses Return (or whatever) and the app inserts the particular octet(s) for a LineFeed, Carriage Return, both, etc.

2. "I was having some issues with 4D text fields that were getting pasted in HTML code from TextMate which was supposed to be putting in linefeeds."

Yes, sharing data between apps is almost always tricky. The programmer or user must set/manipulate the desired end-of-line delimiters either in the data source (TextMate here) or the sink (4D here).

Remember that computers are illiterate. Ultimately they know nothing about characters and text. They truly only understand numbers. What you think of as a file full of text is actually a file full of numbers. If an app reads that file and encounters a "10" (Linefeed), the app may choose to treat that octet as an end-of-line. Or that app may ignore each occurrence of 10, blissfully cruising along looking for an expected '13' (Carriage Return) on which to break lines. To really see what an app sees when it opens a file, to see octet-by-octet, open the file yourself using a hex editor such as HexFiend.

Believe it or not, the geek in me finds this kind of topic fascinating: human language vs computerized data, character sets, Unicode, etc. I have actually had a friend call me to discuss such topics as a cure for his insomnia. Don't even get me started on the lack of foresight of the early computer information folks in the `60s and `70s.

  • Many text editors do not convert existing documents when you change the end-of-line prefs setting. Your setting might apply only to future new files. 
  • You can verify what is being used with a hex editor such as HexFiend.
  • Some older Mac programs still default to Carriage Return for end-of-line rather than the Unix convention of linefeed.
  • Some cross-platform development tools try to help by defaulting to the convention used on the computer at runtime. But this can cause confusion as it changes the behavior of your app depending on which computer a user uses. Usually you should hard-code a specific consistent end-of-line. Java and REALbasic switch defaults, and as I recall 4D does too at least in some circumstances.
Here's how to hard-code generating a linefeed in REALbasic:


Nil versus Null

The 4D language has two words that may be confused: Nil & Null.

Nil tests whether the pointer points to a valid container of a value, a field, variable or array. Nil has nothing to do with the content of what is being pointed to. Nil tells you nothing about what may or may not be inside that field, variable, or array.

  • To test if a pointer is nil:
    If ( Nil( $myPointer) )
    End If

NULL means the container (a database field) has no contents. No value = NULL. Do not confuse no value with an empty value. For an Alpha field or Text variable, we represent an empty value in our code as a pair of double-quotes: $companyName := ""
  • To test for NULL, call the command Is field value Null
  • To set a NULL, removing the value entirely, call the command SET FIELD VALUE NULL

Here's code to test for nil pointer…

C_pointer( $pointerToText )
if Nil( $pointerToText )
  `ERROR condition.
else ` else not nil, we have a valid pointer. So use it.
  C_TEXT( $myText )
  $myText := $pointerToText-> `Copy the value of the text.
end if

You should always test a pointer for nil when passed as a parameter, as seen in this code…

if Not(Nil($1))
end if

4D versions 11 & 12 introduce the feature/problem of NULL values in a record's field, as do most other SQL databases. You can have a valid pointer to a record's field, but have no value (NULL) stored in that field. On the other hand, the record's field may have a value (be non-NULL) but that value may be empty such as empty text for Alpha Field, !00/00/00! for date, etc.

So generally you should be testing for 2 or 3 issues:

  • Is pointer valid? (not Nil)
  • Is what's being pointed to a record field with a value? (not NULL)
  • Is the value valid in the record field being pointed to? (not empty, within expected range, etc.)

--- 4D code --------
C_pointer( $pointerToField )
C_TEXT( $myText )
$myText := "ERROR - This text never got set"

if Nil($ pointerToField )
  `ERROR condition.
else ` else not nil, we have a valid pointer. So use it.
  IF ( Type($pointerToField->) = Is Alpha Field )
    If ( Is field value Null( $pointerToField )
        `ERROR condition. No value (NULL).
    Else // Else we have an actual value

      If ( $pointerToField-> = "" )
        $myText := "Got text? Nope." `Flag error condition if having an empty string was unexpected.
        $myText := $pointerToField-> `Copy the value of the field.   <-------- GOAL
      End If

    End IF

    `ERROR - Expected an Alpha field, but got a pointer pointing to something else.
  End if
end if
… Do something with $myText

If you're working with Date, Blob, or other types of variabless or fields rather than Alpha/String, same concepts apply. Just change the data types in code above, but logic is the same.

Caveat: These code examples were written freehand, not written or tested in 4D.

Tip: In general you should avoid dealing with NULL by defining your database fields as "NOT NULL" in SQL, meaning a record with a NULL in that field will be rejected by the database engine. Chris Date explains why.


REALbasic code to connect to Postgres

I have another blog post with example code in REAL Studio for connecting with REAL Server.

Here's the same example adjusted for connecting to a Postgres server.

  dim db as PostgreSQLDatabase = new PostgreSQLDatabase

  // ---------------- Prepare connection.
  db.Host = ""
  db.port = 5432 // 5432 is default port for PostgreSQL.
  //db.Encryption = 128 // Always encrypt your connection when on an untrusted network.
  db.UserName = "postgres" // "postgres" is the default admin user name.
  db.Password = "YourPostgresPasswordGoesHere"
  db.DatabaseName = "YourDatabaseNameGoesHere" // Postgres supports multiple databases. Which one do you want? Default is name the same name as user, such as 'postgres'.

  // ---------------- Try connecting.
  dim status as String
  if( db.Connect ) then
    status = "Success connecting to db server"
  else // Error connecting
    status = "Error occurred when connecting to database server. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
  end if

  // ---------------- Execute a "SELECT *"
  dim sql as String = "SELECT * FROM YourTableNameGoesHere"
  dim rs as RecordSet = db.SQLSelect( sql)
  if(db.Error) then
    status = "Error when running a SELECT. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
  else // Else normal.
    if( rs = nil ) then
      status = "ERROR - RecordSet is nil"
      status = "Record count = " + str( rs.RecordCount ) // Replace with more useful code.
    end if
  end if


  // ---------------- Catch any exception thrown by code above.
  if( db <> nil) then
  end if


Hiding extraneous stuff in pgAdmin3

When starting out in Postgres 9 using the bundled pgAdmin3 app, I was confused by all the various pieces of the installation. Postgres has many internal support files, tables, and such, none of which is useful to a newbie like me.

Thanks to SeaPug, I got a tip to hide all that extra stuff, instead showing just my own tables, etc.

  1. In Preferences, choose the Browser tab, and uncheck "Show System Objects". 
  2. Restart pgAdmin3.


Port Number Problems

Someone complains: "Port 8080 is FUBAR for some reason..."

What do you do? Here's a few things to try.

Sanity Check

Try another computer, another OS, another human, another Ethernet cable, etc., as a sanity check.

Point Your Web Browser

Check that no other app is listening on the port in question. Without running the system you are trying to reach, point your web browser to the port in question, such as:


If the target app is running on your own computer, use the "localhost" IP number that always means "here on this computer locally":

After a pause, the web browser should report an error saying it could not connect. If it does seem to connect, you may have an app already listening on that port.

Port Scan

On a Mac, another way to check for other apps using that port is running the app:
Applications > Utilities > Network Utility > Port Scan

Chere are over 64,000 ports, so you may want to narrow the range to go faster. If a port is in use, it is listed. Not being listed means the port is free at the moment.


Check your firewall(s).

Mac OS X 10.5 & 10.6 have two firewalls in place, acting in parallel. 

• One firewall tracks which applications have permission to receive incoming network connections. This tool is turned off and on in System Preferences in later versions of Mac OS X. 

• The other firewall inspects network packets as they flow in and out of your computer. Any number of rules can be created to allow, deny, or modify those packets. 

In earlier versions of Mac OS X, you controlled this firewall in System Preferences, but in later versions this firewall is invisible. Use the command line tool 'ipfw' or download the GUI wrapper around ipfw, an app called "WaterRoof". 

Read my blog post for more info about 'ipfw'. 


If all else fails, reboot your computer, cold. Seriously.

Sniff Packets

Use the "tcpdump" command line tool in Mac OS X (and other Unix-based systems) to look at your network packets. Apple tells you how. Use WireShark or other such GUI tools to do the same. Not in my skill set, but perhaps in yours or a colleague’s.


OmniGraffle - Export Current Selection

I just discovered a super useful feature in OmniGraffle Pro version 5.2.3:
File > Export > Export area > Current Selection

I routinely use OmniGraffle to decorate screenshots with call-outs, or create graphics to demonstrate visually. But I often used other programs to finalize the image file. Now I have discovered a shortcut within OmniGraffle:

  1. Select the items I want in my image file.
  2. Choose File > Export.
  3. In the dialog, set "Export Area" popup to "Current Selection".
  4. Name the file and save.
By the way, another shortcut I've discovered is saving directly to my iDisk rather than to my local hard drive, to move files to my web server. iDisk can be flaky, so you always run a risk of problems, but lately in Mac OS X 10.6.4 I have found OmniGraffle, Preview, and other apps to work well saving a file directly.


EndOfLine in REALbasic

REAL Studio 2010 offers the command "EndOfLine" to generate a platform-savvy sequence of one or two characters commonly used to mark the end of a line of text or other use as a delimiter.

But we don't always want a platform savvy choice. Using that choice means our software runs inconsistently from one host OS to another.

To generate a specific end of line character(s) in REALbasic type:
  • EndOfLine.Unix 
  • EndOfLine.Windows
  • EndOfLine.Macintosh
The .Unix choice generates a LineFeed, ASCII/Unicode 10.

The .Windows choice generates 2 characters, a Carriage Return and LineFeed, ASCII/Unicode 13 and 10.

The .Macintosh choice generates a Carriage Return, ASCII/Unicode 13.

That .Macintosh choice is old-school, common in the days of the classic Mac OS 8 and 9, before Mac OS X. By contrast, Mac OS X is Unix-based. While many older Mac programs may still expect lines to be delimited by a carriage return, newer programs expect a linefeed. 

My own default on any platform is the .Unix choice, a linefeed, unless I have some specific reason otherwise.


Installing Postgres

I am trying the release candidate for PostgreSQL (more commonly known as Postgres) 9.0, on my Mac. [Update: Now using 9.0.1.] Version 8.4.4 is current, but given its imminent release I thought I'd give 9 a whirl.

Know that 9.0 is not a radical revision. Original plans labelled it 8.5. Some major features are being added, making it a big enough change that they decided to call it 9.0. But apparently the core architecture is the same. The bundled REAL Studio driver ("PostgreSQL Plugin.rbx") has not been updated for v9, but is working well for me so far as of R.S. 2010 Release 4.1.

Caveat: This discussion is focused on Mac OS X. Some details may vary for other platforms.
Caveat 2: I'm no expert on this topic. Take with a grain of salt. I'm just sharing my current understanding.

Where to Download

Postgres is an open-source project with its own web site.  Some commercial companies are closely involved, employing key developers and providing technical support and other services. One of these companies, EnterpriseDB provides some of the binary (compiled and ready to install) builds. So, for the Mac OS X release, you go to the Postgres.org downloads page offering 3 different download options, including a download link that takes you to EnterpriseDB. That link was for the "One click installer" option which I chose rather than the Fink or MacPorts option.


A "cluster" in Postgres can mean either of two totally different things:

  • Multiple computers cooperating in a way as to provide fail-over or other such features.
    - This is the meaning used commonly in most computing topics.
  • A single installation of Postgres on a single computer.
    - This is the usual meaning in a Postgres discussion. 
I have no idea how the term "cluster" came to be used this way in Postgres history. But get used to it. If you simply download the Postgres installer, run it once on a single Mac, you have a "Postgres cluster". The meaning is singular in the sense that you have one copy of the software installed, but plural in the sense that Postgres supports multiple database simultaneously running. Your Postgres cluster may have one database listing all the books and music you own, while also running another database with your sports league statistics. The data is stored in separate files on disk, but are both managed by Postgres simultaneously.

Computer > Cluster > Database > Schema > Table > Columns > Rows

A Mac has a Postgres cluster installed. That cluster will have at least one database (named 'postgres' to match the user account created when Postgres is installed), and you can create additional databases. In each database you have at least one schema named 'public'. Generally you can do all your work in that schema. Then you define one or more tables. Each table has one or more columns. After that you can create the actual records in the database, the "rows".

Installation Guide

Preview the steps taken during installation by reading this thorough Installation Guide. There is one apparent error: A screenshot shows port 5433, but the default is actually 5432. You can skip reading the Non-interactive installation section. However, do pay attention to the Uninstallation section at the end, as you cannot just toss Postgres in the Trash like a common app.

Specify UTF-8

From what I can gather, generally speaking, we be using UTF-8 as the character encoding used to store our data in Postgres. You can specify the character encoding for each database individually. But more convenient is specifying the encoding for the Postgres cluster, which then becomes the default for any databases created later.

You specify the cluster's character encoding when installing Postgres. I'm not sure, but you may not be able to change this later. The default is mysterious as it is platform-specific. I've been advised to override this to choose a specific encoding. This is done by choosing one of the "locales" with "UTF8" in its name. Apparently Postgres is unfortunately combining two ideas that should really be handled separately:

  • Locale - What rules should be followed for sorting, interpreting dates, etc.?
  • Character Encoding - How should character codepoint numbers are allowed, what letters do they represent, and how should they be written to disk?

For work here in the United States, I want the locale rules usually followed for US English. And I want character encoding to be UTF-8.  So I choose "en_US.UTF-8". This "Locale" setting is the only one I alter during installation; For all others I go with the default.

"postgres" User

The installer ran well without incident on my Mac OS X 10.6.4 (Snow Leopoard) MacBook. I went with all the defaults. Installer runs for several minutes. Your system password is required, for an unusual purpose: To create another Unix user on your Mac. A user named "postgres" is created, and you'll be prompted to invent a password for this user. Be sure to write that password down. That is the password for utilities to connect to the database installation, but is also that new user's password.

The main place for the installation is /Library/PostgreSQL/. Nested is a "9.0" folder, inside of which you'll find a "data" folder. Note that you do not have access to that "data" folder; it is owned by the "postgres" user. Postgres is one of the most secure database systems available. The blocked folder is one aspect of that security. If any of the other user accounts is compromised, rogue software will not have direct access to the database files.

So how do we access the database from our usual user account if we can't get to the database directly? We use utility programs, such as "pgAdmin" to connect to the database using password credentials. For example, to backup the data from the database, you can use "pg_dump" to extract the entire database definition and data as SQL script files.


After the installer completes, the "StackBuilder" utility runs. This tool is a convenience for downloading updated or additional pieces to your Postgres installation. Using StackBuilder is optional, and you can skip it for your first time usage.

Notice the "PostgreSQL 9.0"  folder in /Applications. You'll find the admin tool "pgAdmin III.app" and a "Documentation" folder.

Shared Memory(?!)

Screenshot of Mac OS X installer disk image (DMG)
Last came one confusing speed bump in my installation experience: 'shared memory'. While the issue is confusing, don't be scared… It may turn out to be a non-issue for you as it was for me.

Next to the installer on the DMG is a README. The document is short, with a strange and confusing discussion about critical "shared memory" settings in BSD systems, which includes Mac OS X. It talks about memory amounts for settings with names like shmmax, shmmin, shmmni, shmseg, and shmall. Also read the linked web page.

You needn't concern yourself with these settings just yet. The one-click installer for Postgres 9.0.1 automatically creates a "sysctl.conf" file in the folder "etc" folder at the root of your boot drive:
You must restart your Mac for those settings to take effect.

The most important thing those settings do is raise the limit on the amount of memory used by Postgres from 4 megs to 33 megs. That is sufficient as a beginner starting out with Postgres. But is still a skimpy amount of memory for Postgres to operate. As your databases grow, and for deployment to a production server, you must re-visit this topic and tweak that "systctl.conf" file. When that time comes, see my blog entry and the Postgres doc.

That concludes the installation portion of my Postgres experience. Postgres should automatically be running at this point. In the Activity Monitor program, see several processes named 'postgres' and owned by the 'postgres' user. Launch the newly installed "pgAdmin" app installed in your Applications folder. With pgAdmin, you can create a new database, tables, columns, and rows.