2010-12-19

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.

2010-12-15

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 127.0.0.1,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>
<html>
 <head>
   <meta charset="UTF-8">
   <title>Welcome</title>
 </head>
 <body>
   <p>Per Basil's example.</p>
 </body>
</html>


(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 
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                        http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
       version="3.0"
       metadata-complete="true">

<servlet>
    <servlet-name>FastCGI</servlet-name>
    <servlet-class>net.jr.fastcgi.FastCGIServlet</servlet-class>
    <init-param>
        <param-name>server-address</param-name>
        <param-value>localhost:9000</param-value>
    </init-param> 
</servlet>

<servlet-mapping>
    <servlet-name>FastCGI</servlet-name>
    <url-pattern>/*</url-pattern>
</servlet-mapping>

</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:
/Users/basilbourque/Desktop/real_now/real_now.fcgi
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.

2010-12-02

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.


2010-11-30

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:
  • TRACE
  • DEBUG
  • INFO
  • WARN
  • ERROR
  • FATAL
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.

2010-11-22

"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.



SHARED MEMORY
  • 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
SEMAPHORES
  • 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
PROCESSES
  • 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.

2010-10-31

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();