pgAdmin 1.10 Visual Tour
The long awaited 1.10 version of pgAdmin is finally available. We will highlight some of the new features which have been added since the last stable release on this page.
Major new features
There are two major new features in the Query Tool. They both came out of GSoC projects.
First, it is now possible to build a query graphically.
The graphical query builder (aka GQB) allows one to build a query mainly with the mouse: dragging tables and views from the navigator to the graphical pane, adding links between them, clicking on checkboxes to add columns, adding criteria and setting the order of the results.
The other new feature is the pgScript scripting framework. This allows one to write scripts to execute queries. Here is an example:
In this example, the user will insert random values into some tables. To execute this script, you need to click on the PGS button. The complete reference to pgscript is available here.
Derivative database support
There is enhanced support for Greenplum database: resource queues, external tables and partitioned tables are supported and the graphical explain tool knows much more about Greenplum query node types. There are also links to Greenplum help and Greenplum utility support.
Unfortunately, pgAdmin 1.8 doesn't have support for FTS Objects. One of the things we had to do for 1.10 is to support these.
Each FTS object is handled by its own properties dialog.
The table contextual menu has a few more items that allow you to enable/disable rules on the selected table or to truncate (cascade if needed).
There's also a new DELETE script item:
And last, the role contextual menu has one more item to add Drop/Reassign Owned support:
8.2 and 8.3 stats columns have been added to the tables list.
Each objects' list now show the owner of the object:
Only the function dialog was resizable. With 1.10, each property dialog is resizable:
We also have allowed the SQL tab to be edited. For example, when adding a new column with a default value and a NOT NULL constraint, you couldn't do it with pgAdmin in one action because one needs to first add the column, change the value for each existing row and then add the NOT NULL constraint. Now, you can use the UI to do this. You'll get this query:
Then, click on the "Read-only" checkbox to allow editing mode and update the query to execute.
We no longer display the SQL tab for the Server properties dialog. It had no real meaning on this dialog. But we add a few things, like the color selection:
This color is the background color for each server:
New features have been added to this dialog for 8.4: support for changing the tablespace of an existing database, support for renaming an opened database, support for per-database collation, character type and connection limits.
Adding/removing inherited tables with 8.2+ servers has been added. The list control and the two buttons have moved to their own tab:
The new way to handle autovacuum settings in 8.4 is also supported. Toast tables are handled too.
Column level priviledges are handled for 8.4 servers:
pgAdmin 1.10 adds support for DESC and NULLs FIRST/LAST options for index columns.
Window functions are handled for PostgreSQL 8.4+ servers.
pgAdmin 1.10 supports default values for function parameters in PostgreSQL 8.4+ and on EnterpriseDB, and variadic functions.
Support for typmod in/out functions on custom types is finally available.
One can now change a language's owner.
Connection limit for roles was unavailable in previous release despite PostgreSQL handles this since 8.1.
There's now an option to ignore server version mismatches with pg_dump/pg_dumpall/pg_restore.
The --clean option is now supported.
pgAdmin 1.10 adds the ability to register external applications on a plugins menu. Menu options can be activated based on the current object selection and a variety of variables - including everything needed to open a database connection - can be passed to the external utility.
The only plugin available by default is a psql console, though any application could conceivably be added by the user:
When clicked, the psql console is launched and connects to the database selected in pgAdmin.
Other than the Graphical Query Builder and the pgScript scripting framework, the Query Tool adds options to shift selected text to upper or lower case in the SQL editor, adds block indenting and outdenting, prevents the auto complete being used while a query is running and adds block commenting and uncommenting to the SQL editor.
The Edit grid can now work with the type bit(x).
It was already possible to sort and filter the grid but one has to go another dialog and choose the columns to sort by and write the filter as in a WHERE clause. A new feature allows a user to right click on a value he wants to filter (include or exclude). He can also right click on a column to sort by this column.
There was a complete UI rework of the "Server Status" window.
It allows one to view all reports at the same time, to close some, to resize some, etc. The complete window layout is preserved between each run. Also, one can copy the contents of each grid. It also features a new toolbar to launch each action.
Other work happened on this window. A 'Blocked By' column appears to show if a transaction is blocked by another process. One can launch pgAdmin to only show the Server Status window, with the -S and -Sc command line options.
Other more minor changes in v1.10 include: