beginner - 4     

The age old question of why or why is my table index not being used is probably the most common question that ever gets asked even by expert database users.

In this brief article we will cover the most common reasons and try to order by statistical significance.

In first part Guide to Writing PLPGSQL functions, we covered the plpgsql function anatomy and basic IF and FOR loops. In this second part of our PLPGSQL Quick Guide series, we shall delve more into control flow. As we mentioned in the previous part, the following control flow constructs exist for PLPGSQL.

• FOR somevariable IN (1 ...someendnumber) LOOP .. END LOOP;
• FOR somevariable IN REVERSE someendnumber .. 1 BY somestep LOOP .. END LOOP;
• FOR somevariable IN EXECUTE(somesqlquery) LOOP ..RETURN NEXT; .. END LOOP;
• LOOP ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END LOOP;
• WHILE ... LOOP ... END LOOP;
• EXCEPTION WHEN .... WHEN ..
• Introduced in 8.3 RETURN QUERY which can be in any LOOP like structure or stand alone. This is covered in New Features of PostgreSQL Functions

In this section we shall demonstrate looping thru sets of records and writing a set returning function. In the next section after, we shall delve a little into recursive functions, doing table updates, and raising notices.

In this series we'll go over writing PLPGSQL stored functions. We shall follow up in a later issue with a one page cheat sheet.

The Anatomy of a PLPGSQL FUNCTION

All PLPGSQL functions follow a structure that looks something like the below.

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
    RETURNS text AS
$$
DECLARE
    strresult text;
BEGIN
    strresult := '';
    IF numtimes > 0 THEN
        FOR i IN 1 .. numtimes LOOP
            strresult := strresult || msg || E'\r\n';
        END LOOP;
    END IF;
    RETURN strresult;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
  COST 10;

--To call the function we do this and it returns ten hello there's with 
carriage returns as a single text field.
SELECT fnsomefunc(10, 'Hello there');

We've been playing around with the snapshot builds of PgAdmin III 1.9 and would like to summarize some of the new nice features added. PgAdmin III 1.9 has not been released yet, but has a couple of neat features brewing.

For those interested in experimenting with the snapshot builds and src tarballs, you can download them from http://www.pgadmin.org/snapshots/

OpenJump is a Java Based, Cross-Platform open source GIS analysis and query tool. We've been using it a lot lately, and I would say out of all the open source tools (and even compared to many commercial tools) for geospatial analysis, it is one of the best out there.

While it is fairly rich in functionality in terms of doing statistical analysis on ESRI shapefile as well as PostGIS and other formats and also has numerous geometry manipulation features and plugins in its tool belt, we like the ad-hoc query ability the most. The ease and simplicity of that one tool makes it stand out from the pack. People not comfortable with SQL may not appreciate that feature as much as we do though.

In this excerpt we will quickly go thru the history of project and the ties between the PostGIS group and OpenJump group, how to install, setup a connection to a PostGIS enabled PostgreSQL database and doing some ad-hoc queries.

Quick History Lesson

1. OpenJump is descended from Java Unified Mapping Platform - JUMP which was incubated by Vivid Solutions.
2. OpenJump and the whole JUMP family tree have Java Topology Suite (JTS) as a core foundation of their functionality.
3. GEOS which is a core foundation of PostGIS functionality and numerous other projects, is a C++ port of JTS. New Enhancements often are created in JTS and ported to GEOS and a large body of GEOS work has been incubated by Refractions Research, the PostGIS incubation company.
4. For more gory details about how all these things are intertwined, check out Martin Davis' recount of the history of GEOS and JTS.

One of the nice things about the PostgreSQL command-line restore tool is the ease with which you can restore select objects from a backup. We tend to use schemas for logical groupings which are partitioned by context, time, geography etc. Often times when we are testing things, we just want to restore one schema or set of tables from our backup because restoring a 100 gigabyte database takes a lot of space, takes more time and is unnecessary for our needs. In order to be able to accomplish such a feat, you need to create tar or compressed (PG custom format) backups. We usually maintain PG custom backups of each of our databases.

Backup and Restore is probably the most important thing to know how to do when you have a database with data you care about.

The utilities in PostgreSQL that accomplish these tasks are pg_restore, pg_dump, pg_dumpall, and for restore of plain text dumps - psql.

A lot of the switches used by pg_dump, pg_restore, pg_dumpall are common to all three and on rare cases, the switches used by each overlap but mean different things. pg_dump and pg_restore are complementary. You use pg_dump to do hot backups of a database and pg_restore to restore it either to another database or to recover portions of a database.

Rather than trying to keep track of which switch works with which, we decided to combine all into a single cheat sheet with a column denoting which utility the switch is supported in. Pretty much all the text is compiled from the --help switch of each.

Below is a Thumbnail view of the PostgreSQL 8.3 Dump Restore cheat sheet that covers PostgreSQL 8.3 pg_dump, pg_dumpall, pg_restore utilities.

PDF Portrait version 8.5 x 11" of this cheatsheet is available at PostgreSQL 8.3 Dump Restore 8.5 x 11 and also available in PDF A4 format and HTML.

Programming Design Patterns define recommended approaches of solving common application problems. Within design patterns is a subset of design patterns called Idioms. Idioms you can think of as a strategy for expressing recurring constructs or if you will sub-problems and often take advantage of the special features of a language. They tend to be specific to a programming language and can not be reused in other languages they were not specifically designed for. To demonstrate the differences lets compare two design patterns we commonly use.

In our April Issue An Almost Idiot's Guide to PostgreSQL YUM we covered using the new PostgreSQL Yum repository to install the PostgreSQL 8.3.1 release on Fedora, RedHat Enterprise, and CentOS. We also received numerous useful feedback from others on issues they ran into and how they overcame them. The blog comments are definitely worth a read.

Now that 8.3.3 has come out, many of you should be considering upgrading if you haven't already since there are a couple of bug fixes as outlined in http://www.postgresql.org/docs/8.3/static/release-8-3-2.html, http://www.postgresql.org/docs/8.3/static/release-8-3-3.html, and for those running 8.3.0 you will need to reindex your tables after as noted in http://www.postgresql.org/docs/8.3/static/release-8-3-1.html. If you are running version 8.3.1 and above then this is a fairly painless upgrade that just requires you to backup your data as a precautionary measure, but doesn't require a dump reload.

One of the great selling points of PostgreSQL is its pluggable PL language architecture. MySQL is known for its pluggable storage and PostgreSQL is known for its pluggable PL language architecture. From Monty's notes on slide 12 looks like MySQL may be working on a pluggable PL language architecture of their own. The most common of these languages are the all-purpose languages SQL and C (these are built-in and not really PLs like the others, but we'll throw them in there), PLPgSQL which is also built-in but not always enabled, PL/Perl, PL/Python, and the domain specific languages PL/R, PL/SH and gaining popularity Skype released PL/Proxy. There are others in the family such as PL/Tcl, PL/PHP, PL/Ruby, PL/Scheme (a dialect of Lisp), PL/Java, PL/Lua and PL/LOLCode (for kicks and as a reference implementation. Think of LOLCode as PostgreSQL Pluggable PL equivalent of MySQL's BLACK HOLE storage engine.) .

The other interesting thing about the PostgreSQL PL language architecture is that it is a fairly thin wrapper around these languages. This means the kind of code you write in those languages is pretty much what you would write if you were doing general programming in those languages minus some spi calls. Since the handler is a just a thin wrapper around the environment, the language environment must be installed on the database server before you can use the PL language handler. This means you can have these functions utilized in your SQL statements and you can write in a language you feel comfortable with if you can get the darn PL compiled for your environment or someone has already kindly compiled it for your environment or that it is even compilable for your environment. The pluggable PL architecture means you can write a PL Handler for your favorite language or invent your own language that you can run in the database. In the end the barrier between code,data, and semantic constructs is more of a constraint imposed by compilers. If you have any doubts about the above statement, you need only look at some javascript injection attacks to bring the statement home. One of my fantasies is developing a language that morphs itself, that utilizes the database as its morphing engine and its OS and that breaks the illusion of data being data, code being code, and lacks rigid semantics. Of the languages we have worked with, SmallTalk comes closest to a language that satisfies these ideals and Lisp to a much lesser extent. Lisp lacked the semantic elegance of SmallTalk among other things.

Most people are used to having their procedural language push their data around. PL code living in PostgreSQL allows your data to push your procedural code around in a set-based way. This is a simple but pretty powerful feature since data is in general more fluid than code. For interpretated/just-in time compiled languages it can live in the database, for compiled it has to call compiled functions.

First we'd like to thank Devrim of Command Prompt for working hard on making this new YUM repository available. In this article we will go over using the new PostgreSQL YUM repository for Redhat Fedora, Enterprise Linux and CentOS distros that is available at http://yum.pgsqlrpms.org/.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use this one instead since it seems more up to date than the other. http://yum.pgrpms.org/.

We are gearing the content of this article to the described user profile

• Person has SSH root access to their PostgreSQL box
• Person is new to using Red Hat Linux
• Person may be new to using Linux in general

NOTE: We have a newer article on Installing PostgreSQL 8.4 with Yum and an even newer one for PostgreSQL 9.0 please refer to Installing PostgreSQL 9.0 via Yum. This old article is for PostgreSQL 8.3.

People have asked us on several occasions if there is such a construct SELECT * EXCEPT(...list) FROM sometable. Sadly we do not think such a thing exists in the ANSI SQL Specs nor in PostgreSQL.

The above feature would come in handy when you have certain fields in your tables that are common across tables, but you need to leave them out in your query. A common case of this is when you have PostGIS tables loaded using shp2pgsql with a fields called gid and the_geom which are not terribly useful for simple data queries.

There are 2 common ways we use to achieve this result.

• Using PgAdmin's CREATE SELECT script feature. This exists in other GUI tools as well.
• Using an Information Schema script hack to construct the SELECT statement

You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. How do you rename this database?

If you are using PgAdmin III, you will not see this option. Just one of the ways PgAdmin III lets us down. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error.

1. Connect to some other database other than the one you are trying to rename such as say the postgres db.
2. Kick everyone out of the database you are trying to rename - to figure out users, you can run
   

   SELECT * 
   	FROM pg_stat_activity 
   	WHERE datname = 'myolddbname_goes_here'

3. Now just run this command -
   ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here

What is PuTTY

PuTTY was developed by Simon Tatham and is a very common light-weight MIT-Licensed free and open source Secure Shell (SSH) client for connecting to Linux/Unix systems via a Teletype (TTY) terminal emulation mode console. Currently there are ports for Microsoft Windows, other unix like systems, and ports in progress for Mac OSX and Symbian mobile phone OS.

PuTTY fits into that class of tools we affectionately call Swiss Army Knives because it is Light, Multi-Purpose, and Good Enough. As an added benefit it is free and open source with a generous license so it is commonly embedded in commercial apps.

PuTTY comes in handy both as an SSH terminal console and as a SSH Tunneling tool which allows you for example to use PgAdmin III from a local windows workstation against a remote PostgreSQL server even in cases where the linux/unix PostgreSQL pg_hba.conf and postgresql.conf file only allow local connections or non-SSH traffic is blocked by firewall. For more about the nuances of configuring the pg_hba.conf PostgreSQL server file that controls user access check out Hubert Lubaczewski's “FATAL: Ident authentication failed”, or how cool ideas get bad usage schemas http://www.depesz.com/index.php/2007/10/04/ident/

In this article we shall cover how to use PuTTY's SSH Tunneling feature to access a remote PostgreSQL server that doesn't allow remote connections. To make it a little more interesting we shall demonstrate how to do this for PgAdmin III.

What is TSearch?

TSearch is a Full-Text Search engine that is packaged with PostgreSQL. The key developers of TSearch are Oleg Bartunov and Teodor Sigaev who have also done extensive work with GiST and GIN indexes used by PostGIS, PgSphere and other projects. For more about how TSearch and OpenFTS got started check out A Brief History of FTS in PostgreSQL. Check out the TSearch Official Site if you are interested in related TSearch tips or interested in donating to this very worthy project.

Tsearch is different from regular string searching in PostgreSQL in a couple of key ways.

1. It is well-suited for searching large blobs of text since each word is indexed using a Generalized Inverted Index (GIN) or Generalized Search Tree (GiST) and searched using text search vectors. GIN is generally used for indexing. Search vectors are at word and phrase boundaries.
2. TSearch has a concept of Linguistic significance using various language dictionaries, ISpell, thesaurus, stop words, etc. therefore it can ignore common words and equate like meaning terms and phrases.
3. TSearch is for the most part case insensitive.
4. While various dictionaries and configs are available out of the box with TSearch, one can create new ones and customize existing further to cater to specific niches within industries - e.g. medicine, pharmaceuticals, physics, chemistry, biology, legal matters.

Prior to PostgreSQL 8.3, it was a contrib module located in the shared/contribs folder. As of PostgreSQL 8.3 it is now fully integrated into the PostgreSQL core. The official documents for using TSearch in 8.3 are located in Chapter 12. Full Text Search of the official PostgreSQL documentation.

In this article we shall provide a quick primer to using TSearch in 8.3. In the next month's issue of the Postgres OnLine Journal we shall provide a TSearch cheat sheet similar to our PostgreSQL 8.3 cheat sheet.