beginner - 4   

Sometimes when you are testing or setting up a server or just porting things to another server, you just want to install the same set of users as you had before without restoring any databases. In PostgreSQL, the users (Login Roles) and group roles are stored at the server level and only the permissions to objects are stored at the database level.

How do you restore just the users and roles without having to do a full pg_dumpall of your server?

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.