Have not trolled the source enough to know who specifically to thank, so a general thank you to whoever is responsible for \e(f) in psql and the command DO. I have been working on a fairly complex plpythonu function in a 9.0 database and the combination of the afore mentioned tools made the development  much easier.  The ability to test Python code snippets by writing DO functions inside an editor inside psql is priceless.

Joshua Berkus recently gave a heads up on the Advocacy list of the forthcoming release of the first beta for PostgreSQL 9.1, tentatively scheduled for May 3.  I have been following the development fairly closely, though given the number of changes, not in depth.  Good time to point out the Waiting for.. blog series from Hubert ‘depesz’ Lubaczewski found here. It covers many of the changes and uses examples to illustrate, a very handy resource. So what is 9.1 offering?

First some of the headliners.

Synchronous Replication:
Extends the built in replication to eliminate any lag between the master and standby   databases. For those who absolutely need a 1:1 correspondence with their standbys.

Per column collation:
A different language for each column, the Tower of Babel realized:)

Unlogged tables:
Permission is granted to run with scissors. You can now create tables that do not log to the Write Ahead Log(WAL).  This makes data operations on the table faster, at the cost of data security. Data will not be recovered in the event of a crash, nor are unlogged tables replicated.

SE-Postgres.
Speaking of security Postgres will now have access to the SE-Linux framework.

The above are great and I will be exploring them at some point, but what follows are the new features I am really excited about.

PL/Python changes:
Thanks to Jan Urbanski and others there have been a host of changes to the procedural language support for Python.  To mention a few; table function support, better error handling, explicit subtransactions.

pg_basebackup:
Thanks Magnus. A way of doing a basebackup for replication using a Postgres connection. Very handy.  Along with this,  the replication permission for non-superusers.

SQL/MED:
Basically pulling external data sources into Postgres. The implementation for  9.1 is basic, but the potential is too great to ignore.

Extensions:
A easier way to work with database plugins. Combined with the PGXN site a definite plus.

I have just scratched the surface on what is new and improved in PostgreSQL 9.1, for a more in depth look I suggest the latest alpha Release Notes. I mentioned specific people above because I have associated their names with particular features.  Lest I forget, a big thank you to all those who have participated.  There is much to be proud of in this release as in the previous ones.

I use Jedit as my primary text editing tool. I recently ran across a plugin (SQL) to the editor that allows for running SQL from within an editor  buffer. What follows is an explanation of how to set it up and run it.  Some background, Jedit is a Java application so if you plan to use it you will need a JVM installed. I have found that to have it run well you need to use the Sun(Oracle) Java code.

Set up:
Java runtime 1.6+
Jedit 4.3+
Installing the SQL plugin from the Plugin Manager will install dependencies  most notable Project Viewer.
The set up for SQL has global and per project parts.
The global portion is set up through the Plugin Manager plugin options menu.  By default SQL only returns 10 rows and that can be changed here. I have yet to find a way to get it to return with no limits. I would also suggest checking the Show Toolbar and  Popup messages options. The most important global setting is the JDBC page, where you set up the path to the JDBC drivers you wish to use.  SQL supports quite a few databases (Oracle, MySQL, PostgreSQL, DB2, Sybase, Microsoft SQL Server see help file), though notably not SQLite.

Project Viewer (PV) is installed as a dependency because it is used to create per project settings for SQL. It is possible to use  a PV project for just the SQL settings and not use the rest of the package, though I would suggest checking PV out. One note, PV seems to like to throw non fatal errors on a regular basis. They seem to be related to redraw operations and so far have not caused me any problems.  Once you have created a project you can go to the project SQL setting to enter the necessary information.  See screenshot:

Once you have the server set up the fun starts. As an example I wrote a script to CREATE a table and another to populate it with data.  If you look at the screenshot you will see the SQL toolbar I alluded to earlier, it starts with Database:. The second button to the right of the drop down box is the Execute buffer command. Clicking on that causes the entire sql script in the selected buffer to execute. The result is shown in the message box, in this case for the CREATE TABLE statement. I did this again on the buffer holding the INSERT commands .

So now we have a table with data, now what? See below. In the right buffer I have written a SELECT statement against the previously created table. In this case I could have done Execute buffer but for illustration used another method Execute selection. This is the first button to the right of the drop down list. It only runs whatever text is selected.

So what about variable substitution? In the first screenshot below I have used the drop down list Preprocessors to select variable substitution. In the left buffer is another SELECT query this time using the substitution variable ‘?’. The second screenshot shows the dialog box that pops up for data entry. For more than one variable the plugin will cycle through them and present a dialog for each.

One more interesting feature, SqlVFS . This allows one to browse the database through the Jedit File Browser. When you open the File Browser select the Plugins dropdown and then Show databases. Double clicking on the database allows you start drilling down through the database structure.

In the short time I have been using this tool I have found it quite helpful, so I thought would pass along my experience.  Saves a round trip between the text editor and a terminal window.

Today I got involved in a discussion on the General mailing list about using ROLE in Postgres.  I realized this is a topic that can get confusing in a hurry and I thought I might try to clear things up, in my mind if nothing else.  Part of the confusion arises from a historical carry over. In versions of Postgres prior to 8.1 there where USER and GROUP.  (An aside, I found this out by using this : Feature Matrix, a very handy tool. ) These where distinct and operated somewhat like their OS equivalents, in that users logged in and then could be part of a group. In 8.1+, in other words all current community supported versions, USER and GROUP have been replaced by ROLE.  The confusion arises because while the current underlying mechanism is based solely on ROLE, the USER and GROUP commands have been maintained for backwards compatibility. So when you use the SQL command CREATE USER in 8.1+ you are just creating a ROLE with the clause LOGIN  whereas CREATE GROUP is just CREATE ROLE which by default has the clause NOLOGIN. For my own personal sanity I just use the CREATE ROLE command and pretend USER and GROUP do not exist. Since we already done the rabbit hole I might as well mention the psql \d commands that apply as well. Again for historical reasons these are \du and \dg which at one time listed users and groups respectively. Now they both return the same thing, a list of the roles. The thing to remember is that unless you are working with a pre 8.1 database you are working with some variation of a ROLE.

So what does a ROLE get you? As was implied above a ROLE can have the characteristics of a user or group. For my own use I follow the pattern used by the current CREATE USER/GROUP commands where a ROLE with LOGIN set is a taken to be a user and one with NOLOGIN is equivalent to a group.  Note: This is just a convenient way of viewing things and you will not see a ROLE defined that way when you list them. Assuming that pattern then it possible to create group like ROLES and then assign user like ROLES membership in them. For instance:

‘Groups’:
CREATE ROLE accounting;
CREATE ROLE sales;
CREATE ROLE production;

‘Users’

CREATE ROLE aklaver LOGIN IN ROLE accounting,sales;
CREATE ROLE daffy_duck LOGIN  IN ROLE production;
If you want to change role membership after CREATE ROLE then you will need to use the GRANT or REVOKE commands.

Where this pays off is in object permissions. Give the ‘group’ roles permissions to their respective objects and then add or drop ‘users’ to the ‘groups’ as needed. This way you can maintain access flexibility without continually  having to change permissions on objects.

I have touched on just the bare basics, this is a subject that covers a lot of ground. To really learn it requires multiple passes through the documentation and plenty of hands on testing.

This is more of a test post than anything. A way of checking that I have my feed to Planet PostgreSQL set up right. The point being to have only Postgres related posts come through, not my thoughts on cat juggling(thanks Steve Martin) or other such topics. Should the system work I will follow with a post on Streaming replication/Hot Standby/WAL archiving. As Bruce Momjian is aware I have been experimenting with SR.  That has led me to trying to figure out the relationships between the previously mentioned components.  More thoughts on that later.